SQL WHERE Operators
BETWEEN operator
The BETWEEN operator in WHERE clause selects values that are within the given range. The minimum and maximum values set the range and can include dates, numbers, or text data. Note that the BETWEEN operator is inclusive.
Let's select products with a price greater than or equal to 8 and less than or equal to 13 from the products table below.
![](https://cdn.prod.website-files.com/65255f01672fcf3029794f94/652ea7d42ccdbcfc039782d3_%D0%A1%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA%20%D1%8D%D0%BA%D1%80%D0%B0%D0%BD%D0%B0%202023-10-17%20%D0%B2%2018.26.59.png)
The query with WHERE BETWEEN will look as follows:
The result set of the query above will have three rows:
![](https://cdn.prod.website-files.com/65255f01672fcf3029794f94/652eaa0d83ceb6ff56d26588_%D0%A1%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA%20%D1%8D%D0%BA%D1%80%D0%B0%D0%BD%D0%B0%202023-10-17%20%D0%B2%2018.30.07.png)
The same result can be acquired using the query without BETWEEN:
Basically, WHERE BETWEEN can be considered as a shorthand for >= AND <= in WHERE clause.
To select all the products outside the range, use the NOT BETWEEN operator:
The result set will have three rows excluded now:
![](https://cdn.prod.website-files.com/65255f01672fcf3029794f94/652ea95f78c2b6c2755b7cfc_%D0%A1%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA%20%D1%8D%D0%BA%D1%80%D0%B0%D0%BD%D0%B0%202023-10-17%20%D0%B2%2018.33.32.png)
IN operator
The IN operator in WHERE clause is a shorthand for multiple OR conditions and allows specifying various values.
Let's select products with prices equal to 10,12, or 16 from the table products from the previous example:
The result of this query looks like this:
![](https://cdn.prod.website-files.com/65255f01672fcf3029794f94/652eaac1770684675db05a74_%D0%A1%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA%20%D1%8D%D0%BA%D1%80%D0%B0%D0%BD%D0%B0%202023-10-17%20%D0%B2%2018.39.31.png)
To select products with prices other than 10, 12, or 16 negate IN operator using NOT:
With this query, the result table will get all the products except milk and pasta:
![](https://cdn.prod.website-files.com/65255f01672fcf3029794f94/652eab4d2f6a04c747cd66fb_%D0%A1%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA%20%D1%8D%D0%BA%D1%80%D0%B0%D0%BD%D0%B0%202023-10-17%20%D0%B2%2018.41.39.png)
LIKE operator
The LIKE operator can be used in the WHERE clause to check if a string matches a pattern. To create a pattern use two wildcard match options: % and _.
The % represents any number of characters: zero, one, or more. In the case of writing a pattern %s%, the strings s, toaster, string, and cats will all match this pattern.
The _ represents exactly one character. Thus, writing a pattern s_, the string so will match this pattern, but the strings s and soap will not.
Let's select all the products with the letter 'a' in any position from our products table:
The query will return this table:
![](https://cdn.prod.website-files.com/65255f01672fcf3029794f94/652eac7b5b4214ed07bed34b_%D0%A1%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA%20%D1%8D%D0%BA%D1%80%D0%B0%D0%BD%D0%B0%202023-10-17%20%D0%B2%2018.46.46.png)
Same as with BETWEEN and IN operator, negate LIKE operator with NOT to get all the products without the a in the product name:
EXISTS operator
The EXISTS operator checks if the subquery returns any records or not. If the subquery returns any records, the EXISTS operator will return TRUE; otherwise, it will return FALSE.
Let's consider the following table suppliers:
![](https://cdn.prod.website-files.com/65255f01672fcf3029794f94/652eafe668cba2e76fd05b71_%D0%A1%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA%20%D1%8D%D0%BA%D1%80%D0%B0%D0%BD%D0%B0%202023-10-17%20%D0%B2%2019.01.21.png)
Use the EXISTS operator to select all the suppliers who supply both milk and pasta:
The query result looks as follows:
![](https://cdn.prod.website-files.com/65255f01672fcf3029794f94/652eb0c8b542da65d0f4e849_%D0%A1%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA%20%D1%8D%D0%BA%D1%80%D0%B0%D0%BD%D0%B0%202023-10-17%20%D0%B2%2019.05.16.png)
The negated EXISTS operator returns FALSE if the subquery returns any records; and TRUE if otherwise. Modify the previous query to get the suppliers who supply milk but not pasta:
This query will return this table:
![](https://cdn.prod.website-files.com/65255f01672fcf3029794f94/652eb15118f4964735d98731_%D0%A1%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA%20%D1%8D%D0%BA%D1%80%D0%B0%D0%BD%D0%B0%202023-10-17%20%D0%B2%2019.07.27.png)
ANY operator
The ANY operator returns TRUE if any of the subquery values meet the condition.
Let's use the products and suppliers tables and use the ANY operator to find the supplier who supplies a product that is not listed in the products table:
The result of this query:
![](https://cdn.prod.website-files.com/65255f01672fcf3029794f94/652eb23adce7baedb15acc3d_%D0%A1%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA%20%D1%8D%D0%BA%D1%80%D0%B0%D0%BD%D0%B0%202023-10-17%20%D0%B2%2019.11.26.png)
The ANY operator can be used only after standard comparison operators, such as =, !=, <=, etc.
IS NULL operator
The IS NULL operator returns TRUE if the value in the column is equal to NULL.
Consider the persons table below:
![](https://cdn.prod.website-files.com/65255f01672fcf3029794f94/652eb284ce69267a9a475938_%D0%A1%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA%20%D1%8D%D0%BA%D1%80%D0%B0%D0%BD%D0%B0%202023-10-17%20%D0%B2%2019.12.37.png)
Use the IS NULL operator to select all rows without the information about the city from the persons table:
The query result will look as follows:
![](https://cdn.prod.website-files.com/65255f01672fcf3029794f94/652eb303ce69267a9a48238f_%D0%A1%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA%20%D1%8D%D0%BA%D1%80%D0%B0%D0%BD%D0%B0%202023-10-17%20%D0%B2%2019.14.45.png)
Is it possible to use IS NOT NULL operator to select all rows where the city is non-null:
IS DISTINCT FROM operator
Common comparison operators return NULL, not TRUE or FALSE. When this behavior is not desired, IS DISTINCT FROM operator can be used.
The IS DISTINCT FROM operator is very similar to not equality check (!= or <>). This operator returns TRUE only if two values are different; otherwise it returns FALSE. If both values are equal to NULL, the IS DISTINCT FROM operator will return FALSE, which distinguishes the IS DISTINCT FROM operator from the standard, not equality check.
In other words, these constructs essentially work with NULL as if it were a normal value, not suspense", and return TRUE or FALSE instead of UNKNOWN:
Let's select all the rows where the city is not equal to New-York from the persons table:
The query above will select two rows:
![](https://cdn.prod.website-files.com/65255f01672fcf3029794f94/652eb4b4b2c14020a365b38e_%D0%A1%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA%20%D1%8D%D0%BA%D1%80%D0%B0%D0%BD%D0%B0%202023-10-17%20%D0%B2%2019.21.55.png)
The operator with negation IS NOT DISTINCT FROM works as an equality check for non-null values and returns TRUE if both values are NULL.
Let's select all the rows where the city is equal to NULL from the persons table:
This query will return this row:
![](https://cdn.prod.website-files.com/65255f01672fcf3029794f94/652eb535dce7baedb15f022e_%D0%A1%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA%20%D1%8D%D0%BA%D1%80%D0%B0%D0%BD%D0%B0%202023-10-17%20%D0%B2%2019.24.06.png)