PostgreSQL Tutorial: WHERE

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

Table of Contents

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

PostgreSQL WHERE clause overview

The syntax of the PostgreSQL WHERE clause is as follows:

SELECT select_list
FROM table_name
WHERE condition
ORDER BY sort_expression

The WHERE clause appears 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 must evaluate to true, false, or unknown. It can be a boolean expression or a combination of boolean expressions using the AND and OR operators.

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

PostgreSQL evaluates the WHERE clause after the FROM clause and 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 be updated or deleted.

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 whose first names are Jamie:

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

2) Using WHERE clause with the AND operator example

The following example finds customers whose first name and last name are Jamie and rice by using the AND logical operator to combine two Boolean expressions:

SELECT
	last_name,
	first_name
FROM
	customer
WHERE
	first_name = 'Jamie' AND 
        last_name = 'Rice';

3) Using the WHERE clause with the OR operator example

This example finds the customers whose last name is Rodriguez or first name is Adam by using the OR operator:

SELECT
	first_name,
	last_name
FROM
	customer
WHERE
	last_name = 'Rodriguez' OR 
	first_name = 'Adam';

4) Using WHERE clause with the IN operator example

If you want to match a string with any string in a list, you can use the IN operator.

For example, the following statement returns customers whose first name is Ann, or Anne, or Annie:

SELECT
	first_name,
	last_name
FROM
	customer
WHERE 
	first_name IN ('Ann','Anne','Annie');

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 returns all customers whose first names start with the string Ann:

SELECT
	first_name,
	last_name
FROM
	customer
WHERE 
	first_name LIKE 'Ann%'

The % is called a wildcard that matches any string. The 'Ann%' pattern matches any string that starts 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;

In this example, we used 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';

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

In this tutorial, you have learned how to use PostgreSQL WHERE clause in the SELECT statement to filter rows based on a specified condition.