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.
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.
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.
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.
The NOT condition allows you to specify a condition that must not be met for the row to be returned.
This can be useful for finding all data other than a certain subset. This will return all customers who are not from Germany.
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.
Note that the BETWEEN operator would work similarly to using a greater than or equal condition and a less than or equal 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.
|'%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.
The LIKE operator can be used to find all values that match a criteria, such as all countries with 'land' in that name
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.
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.
This would be similar to doing something like this.
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.
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.
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.
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.
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.