Summary: in this tutorial, you’ll learn how to use PostgreSQL WHERE clause to filter rows returned from the SELECT
statement.
In the previous tutorial, you’ve learned how to use the SELECT statement to query data from a table. What if you want to query just particular rows from a table? In this case, you need to use the WHERE
clause in the SELECT statement.
Let’s take a look at the syntax of the PostgreSQL WHERE
clause.
PostgreSQL WHERE Clause
The syntax of the PostgreSQL WHERE
clause is as follows:
1 2 3 | SELECT column_1, column_2 … column_n FROM table_name WHERE conditions; |
The WHERE
clause appears right after the FROM
clause of the SELECT
statement. The conditions are used to filter the rows returned from the SELECT
statement. PostgreSQL provides you with various standard operators to construct the conditions.
The following table illustrates the standard comparison operators.
Operator | Description |
---|---|
= | Equal |
> | Greater than |
< | Less than |
>= | Greater than or equal |
<= | Less than or equal |
<> or != | Not equal |
AND | Logical operator AND |
OR | Logical operator OR |
Let’s practice with some examples of using the WHERE
clause with conditions.
PostgreSQL WHERE examples
If you want to get all customers whose first names are Jamie
, you can use the WHERE
clause with the equal (=) operator as follows:
1 2 3 | SELECT last_name, first_name FROM customer WHERE first_name = 'Jamie'; |
If you want to select the customer whose first name is Jamie
and last names is rice
, you can use the AND
logical operator that combines two conditions as the following query:
1 2 3 4 | SELECT last_name, first_name FROM customer WHERE first_name = 'Jamie' AND last_name = 'Rice'; |
If you want to know who paid the rental with amount is either less than 1USD or greater than 8USD, you can use the following query with OR
operator:
1 2 3 4 5 | SELECT customer_id, amount, payment_date FROM payment WHERE amount <= 1 OR amount >= 8; |
There are many ways to construct conditions with standard operators. In the next tutorials, you will also learn how to filter the rows based on pattern matching or using special operators such as BETWEEN, IN
and IS
. So take few minutes to practice with the sample database using the WHERE
clause with comparison operators.
In this tutorial, you’ve learned how to use WHERE
clause together with the SELECT
statement to filter rows based on conditions constructed using standard operators.