SQL Where Clauses
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:
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:
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:
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.
DELETE FROM table WHERE id in (100,101,150,174)
Another common use of the WHERE clause is is to search through records and return the ones during a particular time frame. Timestamp columns can use the < and > operators to compare to each other, and certain string formats can be compared to timestamps like this query:
SELECT * FROM table WHERE t_stamp > '1984-01-25 16:35:55'
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.
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.
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.
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.
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.
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.
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.
SELECT column1, column2, column3 FROM table WHERE column1 BETWEEN value1 AND value2
Examples​
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.
SELECT * FROM customers WHERE age >= 20 AND age <= 40
Timestamps can also use the BETWEEN operator to check for a given start time and end time.
SELECT * FROM customers WHERE start_time BETWEEN '1984-01-25 00:00:00' AND '1984-01-25 16:35:55'
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​
| Pattern | Meaning | Possible 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.
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
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.
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.
SELECT * FROM customers WHERE country IN ('Germany', 'France', 'USA')
This would be similar to doing something like this.
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.
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.
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.
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.
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.
SELECT * FROM customers WHERE country LIKE '%land%' AND (age > 50 OR country IN ('Germany', 'France'))