PostgreSQL – WHERE clause

The PostgreSQL WHERE clause is used to filter results returned by the SELECT statement.

Syntax: SELECT select_list FROM table_name WHERE condition;

Let’s analyze the above syntax:

• The WHERE clause appears right after the FROM clause of the SELECT statement
• The condition evaluates to true, false, or unknown. It can either be a Boolean expression or a combination of Boolean expressions where AND and OR operators are used.
• The WHERE clause can also be used with the UPDATE and DELETE statement to specify rows to be updated or deleted.

Below table provides us with the list of comparison operators valid in PostgreSQL:

Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<> or =!	Not equal
AND	Logical AND operator
OR	Logical OR operator

For the sake of this article we will be using the sample DVD rental database, which is explained here and can be downloaded by clicking on this link.
Now, let’s look into some examples.

Example 1:
Using WHERE clause with the equal (=) operator. Here we will be using the equal operator in the “customer” table of our sample database.

SELECT
    last_name,
    first_name
FROM
    customer
WHERE
    first_name = 'Kelly';

Output:

Example 2:
Using the WHERE clause with the AND operator. Here we will be using the AND operator in the “customer” table of our sample database.

SELECT
    last_name,
    first_name
FROM
    customer
WHERE
    first_name = 'Kelly'
AND last_name = 'Knott';

Output:

Example 3:
Using the WHERE clause with the OR operator. Here we will be using the OR operator in the “customer” table of our sample database.

SELECT
    first_name,
    last_name
FROM
    customer
WHERE
    last_name = 'Cooper' OR 
    first_name = 'Jo';

Output:

Example 4:
Using the WHERE clause with the IN operator. The IN operator is used for string matching. Here we will be using the IN operator in the “customer” table of our sample database.

SELECT
    first_name,
    last_name
FROM
    customer
WHERE 
    first_name IN ('Kelly', 'Jo', ' Alexander');

Output:

Example 5:
Using the WHERE clause with the LIKE operator. The LIKE operator is used to find string matching a particular pattern. Here we will be using the LIKE operator in the “customer” table of our sample database.

SELECT
    first_name,
    last_name
FROM
    customer
WHERE 
    first_name LIKE 'Kath%';

Output:

Example 6:
Using the WHERE clause with the BETWEEN operator. The BETWEEN operator return if a value is in the mentioned range. Here we will be using the BETWEEN operator in the “customer” table of our sample database.

SELECT
    first_name,
    LENGTH(first_name) name_length
FROM
    customer
WHERE 
    first_name LIKE 'K%' AND
    LENGTH(first_name) BETWEEN 3 AND 7
ORDER BY
    name_length;

Output:

Example 7:
Using the WHERE clause with the not equal operator (<>). Here we will be using the <> operator in the “customer” table of our sample database.

SELECT 
    first_name, 
    last_name
FROM 
    customer 
WHERE 
    first_name LIKE 'Bra%' AND 
    last_name <> 'Motley';

Output: