Introduction to PostgreSQL WHERE clause

TheSELECT statement returns all rows from one or more columns in a table. To retrieve rows that satisfy a specified condition, you use aWHERE clause.

The syntax of the PostgreSQLWHERE clause is as follows:

SELECT  select_listFROM  table_nameWHERE  conditionORDER BY  sort_expression;

In this syntax, you place theWHERE clause right after theFROM clause of theSELECT statement.

TheWHERE clause uses thecondition to filter the rows returned from theSELECT clause.

Thecondition is a boolean expression that evaluates to true, false, or unknown.

The query returns only rows that satisfy thecondition in theWHERE clause. In other words, the query will include only rows that cause thecondition to evaluate to true in the result set.

PostgreSQL evaluates theWHERE clause after theFROM clause but before theSELECT andORDER BY clause:

If you usecolumn aliases in theSELECT clause, you cannot use them in theWHERE clause.

Besides theSELECT statement, you can use theWHERE clause in theUPDATE andDELETE statement to specify rows to update and delete.

To form the condition in theWHERE clause, you use comparison and logical operators:

PostgreSQL WHERE clause examples

Let’s practice with some examples of using theWHERE clause.

We will use thecustomer table from thesample database for demonstration.

1)  Using WHERE clause with the equal (=) operator example

The following statement uses theWHERE clause to find customers whose first name isJamie:

SELECT  last_name,  first_nameFROM  customerWHERE  first_name= 'Jamie';

Output:

last_name | first_name-----------+------------ Rice      | Jamie Waugh     | Jamie(2 rows)

2) Using the WHERE clause with the AND operator example

The following example uses aWHERE clause with theAND logical operator to find customers whose first name and last names areJamie andrice:

SELECT  last_name,  first_nameFROM  customerWHERE  first_name = 'Jamie'  AND last_name = 'Rice';

Output:

last_name | first_name-----------+------------ Rice      | Jamie(1 row)

3) Using the WHERE clause with the OR operator example

The following example uses a WHERE clause with an OR operator to find the customers whose last name isRodriguez or first name isAdam:

SELECT  first_name,  last_nameFROM  customerWHERE  last_name = 'Rodriguez'  OR first_name = 'Adam';

Output:

first_name | last_name------------+----------- Laura      | Rodriguez Adam       | Gooch(2 rows)

4) Using the WHERE clause with the IN operator example

If you want to find a value in a list of values, you can use theIN operator.

The following example uses the WHERE clause with the IN operator to find the customers with first names in the list Ann, Anne, and Annie:

SELECT  first_name,  last_nameFROM  customerWHERE  first_name IN ('Ann','Anne', 'Annie');

Output:

first_name | last_name------------+----------- Ann        | Evans Anne       | Powell Annie      | Russell(3 rows)

5) Using the WHERE clause with the LIKE operator example

To find a string that matches a specified pattern, you use theLIKE operator.

The following example uses the LIKE operator in the WHERE clause to find customers whose first names start with the wordAnn:

SELECT  first_name,  last_nameFROM  customerWHERE  first_name LIKE 'Ann%';

Output:

first_name | last_name------------+----------- Anna       | Hill Ann        | Evans Anne       | Powell Annie      | Russell Annette    | Olson(5 rows)

The% is called a wildcard that matches any string. The'Ann%' pattern matches any strings that start with'Ann'.

6) Using the WHERE clause with the BETWEEN operator example

The following example finds customers whose first names start with the letterA and contains 3 to 5 characters by using theBETWEEN operator.

TheBETWEEN operator returns true if a value is in a range of values.

SELECT  first_name,  LENGTH(first_name) name_lengthFROM  customerWHERE  first_name LIKE 'A%'  AND LENGTH(first_name)BETWEEN 3  AND 5ORDER BY  name_length;

Output:

first_name | name_length------------+------------- Amy        |3 Ann        |3 Ana        |3 Andy       |4 Anna       |4 Anne       |4 Alma       |4 Adam       |4 Alan       |4 Alex       |4 Angel      |5...

In this example, we use theLENGTH() function to get the number of characters of an input string.

7) Using the WHERE clause with the not equal operator (<>) example

This example finds customers whose first names start withBra and last names are notMotley:

SELECT  first_name,  last_nameFROM  customerWHERE  first_name LIKE 'Bra%'  AND last_name <> 'Motley';

Output:

first_name | last_name------------+----------- Brandy     | Graves Brandon    | Huey Brad       | Mccurdy(3 rows)

Note that you can use the!= operator and<> operator interchangeably because they are equivalent.

Summary

• Use aWHERE clause in theSELECT statement to filter rows of a query based on one or more conditions.

Last updated onJanuary 16, 2024

On this page

• Introduction to PostgreSQL WHERE clause
• PostgreSQL WHERE clause examples
• Summary