Skip to end of metadata
Go to start of metadata


Overview

The SQL WHERE clause is utilized to restrict the number of rows impacted by a query. A WHERE clause is commonly utilized in two scenarios:

  • In conjunction with a SELECT statement to filter the number of rows returned
  • As part of an UPDATE or DELETE statements to restrict which rows are manipulated by the query. 

In either scenario, the syntax of a WHERE clause is used the same, and can have multiple predicates:

Pseudocode - Where Clause Syntax
SELECT * FROM table WHERE column = value

Where-Clauses and Data Manipulation

In most cases, statements that modify the content of a database table via UPDATE or DELETE should include a WHERE clause: otherwise the manipulation will be applied to every row. To demonstrate, you typically want to avoid queries that look like the following

Pseudocode - Never do this!
UPDATE table SET column = 'This was a horrible mistake'


When manually modifying a database table, a good habit to develop involves first writing a SELECT statement. If you can successfully write a WHERE clause that only returns the results you need to modify, then you can simply change the rest of your query to manipulate the table. Thus, we could write a query like the following:

Pseudocode - Select-Statement with a Where Clause
SELECT * FROM table WHERE id in (100,101,150,174)


If we receive only the results we need to modify in the query above, we can make a simple modification to our query to now delete just the rows we want. 

Pseudocode - Delete-Statement with a Where Clause
DELETE FROM table WHERE id in (100,101,150,174)

On this page ...


And Operator

The AND operator allows you to specify two or more conditions in a WHERE clause, where each condition must be true for the row to be returned.

Pseudocode - Using And to Specify Multiple Conditions
SELECT column1, column2, column3 FROM table WHERE column1 > value AND column2 < value AND column3 = value

Example

This helps to narrow down the result set even further by adding in additional conditions that must be met. This will only return rows for customers from Germany who are also over 20 years old.

SQL - Customers from Germany Over 20
SELECT * FROM customers WHERE country = 'Germany' AND age > 20


Or Operator

The OR operator allows you to specify two or more conditions in a WHERE clause, but only one of the conditions need to be true for the row to be returned.

Pseudocode - Using Or to Specify Multiple Conditions
SELECT column1, column2, column3 FROM table WHERE column1 > value OR column2 < value OR column3 = value

Example

The OR operator can help pull in data from two different subsets in the table. This will only return rows for customers from Germany or customers who are over 20 years old.

SQL - Customers from Germany or Customers Over 20
SELECT * FROM customers WHERE country = 'Germany' OR age > 20


Not Condition

The NOT condition allows you to specify a condition that must not be met for the row to be returned.

Pseudocode - Using Not to Specify a Condition that Shouldn't be met
SELECT column1, column2, column3 FROM table WHERE NOT column1 = value

Example

This can be useful for finding all data other than a certain subset. This will return all customers who are not from Germany.

SQL - Customers that are not from Germany
SELECT * FROM customers WHERE NOT country = 'Germany'


Between Operator

The BETWEEN condition allows you to specify a range of values separated by an AND that the value must be in for a condition to be true. The value can be numbers, text or dates and is inclusive of the first and last values in the range.

Pseudocode - Using Between to Specify a Range of Values
SELECT column1, column2, column3 FROM table WHERE column1 BETWEEN value1 AND value2

Examples

SQL - Customers that are Between the Ages of 20 and 40
SELECT * FROM customers WHERE age BETWEEN 20 AND 40

Note that the BETWEEN operator would work similarly to using a greater than or equal condition and a less than or equal condition.

SQL - Customers that are Between the Ages of 20 and 40 with no Between Operator
SELECT * FROM customers WHERE age >= 20 AND age <= 40


Like Condition

The LIKE condition allows you to specify a condition that must meet a certain pattern. Typically used to compare to string values, the pattern can be built using a combination of characters and the two wildcard values.

  • % - Used to specify any number of any characters including zero characters.
  • _ - Used to specify exactly one character.

Pattern Examples

PatternMeaningPossible Matches
'%a%'Values that have an 'a' in them.

'a', 'Inductive Automation', 'almost', 'create'

'_a_'Values that have an 'a' with exactly one character before and after the 'a'.'bat', 'cat', 'can'
'_a%'Values that have an 'a' as the second character.'da', 'saw', 'catcher'
'a%t'Values that start with 'a' and end with 't'.'about', 'at'
'%a%_%_%_'Values that contain an 'a' with at least 3 other characters after it.'trains', 'airplane', 'canteen'
'%a%a%'Values that contain at least two 'a' characters in them.'Inductive Automation', 'separate', 'apart'

Once the pattern has been constructed, it can be used with the LIKE operator to find values that match the specified pattern.

Pseudocode - Using Like to Specify a Pattern of Values
SELECT column1, column2, column3 FROM table WHERE column1 LIKE '%a%'

Example

The LIKE operator can be used to find all values that match a criteria, such as all countries with 'land' in that name

SQL - Customers that are not from Countries with 'land' in the Name
SELECT * FROM customers WHERE country LIKE '%land%'


In Condition

The IN operator allows you to specify a subset of values, with the condition that the return match at least one of them. Using an IN operator is similar to using multiple OR operators for the same column.

Pseudocode - Using IN to Specify Multiple Values
SELECT column1, column2, column3 FROM table WHERE column1 IN (value1, value2, value3)

Examples

The IN can be used as a shorthand way of writing out multiple conditions for the same column separated by OR operators. This would select all values where the country is either Germany, France, or USA.

SQL - Customers from Germany or France or USA
SELECT * FROM customers WHERE country IN ('Germany', 'France', 'USA')

This would be similar to doing something like this.

SQL - Customers from Germany or France or USA
SELECT * FROM customers WHERE country = 'Germany' OR country = 'France' OR country = 'USA'

The real power of the IN operator is that instead of specifying static values, an entirely new query can be run to compare values against.

SQL - Customers from Countries that users are also in
SELECT * FROM customers WHERE country IN (SELECT country FROM users)


Combining Multiple Operators

Multiple AND and OR operators can be combined to specify multiple different conditions that need to be met in order for a particular row to be returned. Additionally, each condition can be simple using the mathematical operators or complex using the conditions listed above. When using AND and OR operators in a WHERE clause, the AND will take precedence, evaluating first before the OR. In the pseudocode below, the row will be returned if either both the first and second conditions are met, or the third condition is met.

Pseudocode - Using Multiple Where Clause Operators
SELECT column1, column2, column3 FROM table WHERE column1 > value AND column2 < value OR column3 = value

However, the order at which the operators get evaluated can change by placing parentheses around the conditions which should be evaluated first. In the pseudocode below, the row will be returned if both the first condition is met, and either the second or third condition is met.

Pseudocode - Using Multiple Where Clause Operators with Parentheses
SELECT column1, column2, column3 FROM table WHERE column1 > value AND (column2 < value OR column3 = value)

Examples

We can use complex conditions with different operators to find all customers who are over the age of 50 in a country that has 'land' in the name, or any customers in Germany or France.

SQL - Multiple Complex Conditions
SELECT * FROM customers WHERE country LIKE '%land%' AND age > 50 OR country IN ('Germany', 'France')

Using parentheses in the same query can drastically change what valid return conditions are. Here, the customer must both be from a country with 'land' in the name, as well as either over 50 or from Germany or France.

SQL - Multiple Complex Conditions
SELECT * FROM customers WHERE country LIKE '%land%' AND (age > 50 OR country IN ('Germany', 'France'))



 

  • No labels