PostgreSQL WHERE: Filtering Rows

Summary: in this tutorial, you will learn how to use the PostgreSQL WHERE clause to filter rows in a table.

PostgreSQL WHERE clause #

The SELECT FROM statement retrieves data from one or more columns of all rows of a table. In practice, you often need to select rows that meet a condition.

To filter rows from a table based on a condition, you use the WHERE clause in the SELECT statement:

SELECT column1, column2, ...
FROM table_name
WHERE condition;Code language: SQL (Structured Query Language) (sql)

When executing the statement, PostgreSQL evaluates the FROM clause first, then the WHERE clause, and finally the SELECT clause:

• First, retrieve each row in the table_name by running the FROM clause.
• Second, evaluate the condition in the WHERE clause and include the row in the result set if the condition is true.
• Third, select the columns specified in the SELECT clause from the selected rows.

If no rows from the table_name meet the condition, the SELECT statement returns an empty set, which contains no rows.

PostgreSQL WHERE clause example #

Suppose we have the inventories table as follows:

CREATE TABLE inventories (
  name VARCHAR(255),
  brand VARCHAR(50),
  quantity INT,
  price DECIMAL(19, 2)
);


INSERT INTO inventories (name, brand, quantity, price)
VALUES ('iPhone 14 Pro', 'Apple', 10, 999.99),
       ('Galaxy S23 Ultra', 'Samsung', 15, 1199.99),
       ('Pixel 7 Pro', 'Google', 8, 899.99),
       ('Xperia 1 IV', 'Sony', 7, 1299.99);Code language: SQL (Structured Query Language) (sql)

The following statement uses the WHERE clause to retrieve the products with a quantity greater than 9:

SELECT name, quantity
FROM inventories
WHERE quantity > 9;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

       name       | quantity
------------------+----------
 iPhone 14 Pro    |       10
 Galaxy S23 Ultra |       15Code language: SQL (Structured Query Language) (sql)

How it works.

First, the FROM clause selects all rows from the inventories table:

       name       |  brand  | quantity |  price
------------------+---------+----------+---------
 iPhone 14 Pro    | Apple   |       10 |  999.99
 Galaxy S23 Ultra | Samsung |       15 | 1199.99
 Pixel 7 Pro      | Google  |        8 |  899.99
 Xperia 1 IV      | Sony    |        7 | 1299.99Code language: SQL (Structured Query Language) (sql)

Second, the WHERE clause evaluates each row in the inventories table, checks if the quantity is greater than 10, and includes the rows in the intermediate result set:

       name       |  brand  | quantity |  price
------------------+---------+----------+---------
 iPhone 14 Pro    | Apple   |       10 |  999.99
 Galaxy S23 Ultra | Samsung |       15 | 1199.99Code language: SQL (Structured Query Language) (sql)

It returns two rows with quantity 10 and 15.

Third, the SELECT clause retrieves data from the name and quantity columns:

       name       | quantity
------------------+----------
 iPhone 14 Pro    |       10
 Galaxy S23 Ultra |       15Code language: SQL (Structured Query Language) (sql)

Comparison operators #

Besides the greater than operator >, you can utilize other comparison operators inside the WHERE clause:

Equal operator (=) #

The equal operator returns true if two values are equal.

For example, the following statement uses the equal to operator (=) in the WHERE clause to query the products from the inventories with a quantity equal to 10:

SELECT name, quantity
FROM inventories
WHERE quantity = 10;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

     name      | quantity
---------------+----------
 iPhone 14 Pro |       10Code language: SQL (Structured Query Language) (sql)

The query returns the iPhone 14 Pro because only this product has a quantity of 10.

Not equal operator ( !=) #

The following statement uses the not equal operator (!=) to retrieve products from the inventories table with the quantity not equal to 10:

SELECT name, quantity
FROM inventories
WHERE quantity != 10;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

       name       | quantity
------------------+----------
 Galaxy S23 Ultra |       15
 Pixel 7 Pro      |        8
 Xperia 1 IV      |        7Code language: SQL (Structured Query Language) (sql)

Note that PostgreSQL also uses <> as the not equal to operator, so you can use the != and <> operators interchangeably.

The WHERE clause and column alias #

Since PostgreSQL evaluates the WHERE clause before the SELECT clause, the column aliases are not available at the time of evaluating the WHERE clause.

The following statement attempts to use the amount column alias in the WHERE and results in an error:

SELECT
  name,
  quantity * price AS amount
FROM
  inventories
WHERE
  amount > 10000;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Error:

column "amount" does not existCode language: plaintext (plaintext)

The output indicates that the amount column does not exist. To fix this error, you need to use the expression in the WHERE clause as follows:

SELECT
  name,
  quantity * price AS amount
FROM
  inventories
WHERE
  quantity * price > 10000;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name       |  amount
------------------+----------
 Galaxy S23 Ultra | 17999.85Code language: plaintext (plaintext)

Summary #

• Use the WHERE clause to filter rows from a table based on a condition.
• PostgreSQL evaluates the WHERE clause after the FROM clause and before the SELECT clause.

Quiz #