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.