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.

The query with WHERE BETWEEN will look as follows:

SELECT
    product 
FROM
    products 
WHERE 
    price BETWEEN 8 AND 13;

The result set of the query above will have three rows:

The same result can be acquired using the query without BETWEEN:

SELECT
    product 
FROM
    products 
WHERE 
    (price >= 8 
     AND price <= 13);

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:

SELECT
    product 
FROM
    products 
WHERE 
    price NOT BETWEEN 8 AND 13;

The result set will have three rows excluded now:

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:

SELECT
    product 
FROM
    products 
WHERE
    price IN (10, 12, 16);

The result of this query looks like this:

To select products with prices other than 10, 12, or 16 negate IN operator using NOT:

SELECT
    product 
FROM
    products 
WHERE
    price NOT IN (10, 12, 16);

With this query, the result table will get all the products except milk and pasta:

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:

SELECT
    product 
FROM
    products 
WHERE
    product LIKE '%a%';

The query will return this table:

Same as with BETWEEN and IN operator, negate LIKE operator with NOT to get all the products without the a in the product name:

SELECT
    product 
FROM
    products
WHERE
    product NOT LIKE '%a%';

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:

Use the EXISTS operator to select all the suppliers who supply both milk and pasta:

SELECT DISTINCT
    supplier 
FROM
    suppliers AS milk_suppliers
WHERE
    product = 'milk'
    AND EXISTS
(SELECT supplier
FROM
    suppliers 
WHERE
    product = 'pasta'
    AND supplier = milk_suppliers.supplier);

The query result looks as follows:

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:

SELECT DISTINCT 
    supplier 
FROM
    suppliers AS milk_suppliers
WHERE
    product = 'milk'
    AND NOT EXISTS 
(SELECT
    supplier
FROM
    suppliers 
WHERE 
    product = 'pasta'
    AND supplier = milk_suppliers.supplier);

This query will return this table:

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:

SELECT DISTINCT
    supplier
FROM
    suppliers
WHERE
    NOT product = ANY (SELECT product FROM products);

The result of this query:

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:

Use the IS NULL operator to select all rows without the information about the city from the persons table:

SELECT
    name 
FROM
    persons 
WHERE
    city IS NULL;

The query result will look as follows:

Is it possible to use IS NOT NULL operator to select all rows where the city is non-null:

SELECT
    name
FROM
    persons
WHERE
    city IS NOT 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:

NULL = NULL => UNKNOWN 
NULL IS NOT DISTINCT FROM NULL => TRUE

Let's select all the rows where the city is not equal to New-York from the persons table:

SELECT
    * 
FROM
    persons 
WHERE
    city IS DISTINCT FROM 'New-York';

The query above will select two rows:

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:

SELECT
    *
FROM
    persons
WHERE
    city IS NOT DISTINCT FROM NULL;

This query will return this row: