PostgreSQL WHERE

Summary: in this tutorial, you will learn how to use PostgreSQL WHERE clause to filter rows returned by a SELECT statement.

Introduction to PostgreSQL WHERE clause

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

The syntax of the PostgreSQL WHERE clause is as follows:

SELECT 
  select_list 
FROM 
  table_name 
WHERE 
  condition 
ORDER BY 
  sort_expression;Code language: SQL (Structured Query Language) (sql)

In this syntax, you place the WHERE clause right after the FROM clause of the SELECT statement. 

The WHERE clause uses the condition to filter the rows returned from the SELECT clause.

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

The query returns only rows that satisfy the condition in the WHERE clause. In other words, the query will include only rows that cause the condition evaluates to true in the result set.

PostgreSQL evaluates the WHERE clause after the FROM clause but before the SELECT and ORDER BY clause:

If you use column aliases in the SELECT clause, you cannot use them in the WHERE clause.

Besides the SELECT statement, you can use the WHERE clause in the UPDATE and DELETE statement to specify rows to update and delete.

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

PostgreSQL WHERE clause examples

Let’s practice with some examples of using the WHERE clause.

We will use the customer table from the sample database for demonstration.

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

The following statement uses the WHERE clause to find customers with the first name is Jamie:

SELECT 
  last_name, 
  first_name 
FROM 
  customer 
WHERE 
  first_name = 'Jamie';Code language: SQL (Structured Query Language) (sql)

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 a WHERE clause with the AND logical operator to find customers whose first name and last names are Jamie and rice:

SELECT 
  last_name, 
  first_name 
FROM 
  customer 
WHERE 
  first_name = 'Jamie' 
  AND last_name = 'Rice';Code language: SQL (Structured Query Language) (sql)

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 is Rodriguez or first name is Adam:

SELECT 
  first_name, 
  last_name 
FROM 
  customer 
WHERE 
  last_name = 'Rodriguez' 
  OR first_name = 'Adam';Code language: SQL (Structured Query Language) (sql)

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 the IN 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_name 
FROM 
  customer 
WHERE 
  first_name IN ('Ann', 'Anne', 'Annie');Code language: SQL (Structured Query Language) (sql)

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 the LIKE operator.

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

SELECT 
  first_name, 
  last_name 
FROM 
  customer 
WHERE 
  first_name LIKE 'Ann%';Code language: SQL (Structured Query Language) (sql)

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 letter A and contains 3 to 5 characters by using the BETWEEN operator.

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

SELECT 
  first_name, 
  LENGTH(first_name) name_length 
FROM 
  customer 
WHERE 
  first_name LIKE 'A%' 
  AND LENGTH(first_name) BETWEEN 3 
  AND 5 
ORDER BY 
  name_length;Code language: SQL (Structured Query Language) (sql)

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 the LENGTH() function gets 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 with Bra and last names are not Motley:

SELECT 
  first_name, 
  last_name 
FROM 
  customer 
WHERE 
  first_name LIKE 'Bra%' 
  AND last_name <> 'Motley';Code language: SQL (Structured Query Language) (sql)

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 a WHERE clause in the SELECT statement to filter rows of a query based on one or more conditions.