PostgreSQL WHERE Clause: Introduction, Syntax & Examples

In this tutorial, we will learn about the WHERE clause in PostgreSQL. Though it is a very simple topic, it is recommended to understand how the WHERE clause works in order to produce the required output. Therefore, we will also learn some important things about the WHERE clause. So, let’s get started!

Introduction to PostgreSQL WHERE Clause

The WHERE clause is used to filter out the data and obtain the desired output. The WHERE clause is used with the SELECT, UPDATE and DELETE statements to get the targeted records and perform the specified operation. For example, if you want to display, update or delete the records of students who have dropped out in the last year, then you can use the WHERE clause.

The WHERE clause is used right after the FROM clause. Note that, the condition specified in the WHERE clause must be evaluated as true or false. Moreover, you can specify more than one condition using the AND and OR keywords.

The important thing worth noting is that column aliases can not be used in the WHERE clause.

Let’s now see the syntax of the WHERE clause.

PostgreSQL WHERE Clause Syntax

Following is the syntax of the WHERE clause in PostgreSQL.

Syntax:

SELECT column_list
FROM  table_name
WHERE condition
[ GROUP BY grouping_element ]
[ HAVING condition ]
[ ORDER BY expression [ ASC | DESC ] ]
[ LIMIT count ]
[ OFFSET start ];Code language: CSS (css)

Here,

• WHERE – The WHERE condition must appear immediately after the FROM clause
• condition – The condition must evaluate to the boolean result. Any record that doesn’t satisfy the condition will be removed from the result set.

You can add more filtering clauses after the WHERE condition such as GROUP BY, HAVING and ORDER BY etc.

To create the condition for the WHERE clause, you can use the following operators:

Now that we have learned all the prerequisites, let’s head towards some examples and see real-world usage of the WHERE clause.

PostgreSQL WHERE Clause Examples

To demonstrate the WHERE clause usage, we will need a table with some data in it. So, let’s create a table first.

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INTEGER,
    salary DECIMAL(10, 2),
    hire_date DATE,
    department VARCHAR(50),
    is_manager BOOLEAN
);

INSERT INTO employees (first_name, last_name, age, salary, hire_date, department, is_manager)
VALUES
    ('John', 'Doe', 28, 50000.00, '2020-01-15', 'Sales', true),
    ('Jane', 'Smith', 32, 60000.00, '2018-07-10', 'Marketing', false),
    ('Michael', 'Johnson', 45, 75000.00, '2015-03-22', 'Finance', true),
    ('Emily', 'Brown', 22, 42000.00, '2022-04-05', 'HR', false),
    ('William', 'Wilson', 38, 67000.00, '2017-09-17', 'Sales', false);
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Let’s see if the table contains the correct data.

Perfect!

WHERE clause with Equal (=) Operator

From the table, we will find all the employees who have a sales department.

SELECT * FROM employees
WHERE department = 'Sales';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

WHERE clause with Greater than(>) Operator

Using the greater than operator, we will find all the employees having salaries greater than 50000. Note that, since we are not using the greater than or equal to operator, the result set will exclude the salaries which are exactly 50000.

SELECT * FROM employees
WHERE salary > 50000;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

WHERE clause with AND Operator

Now we will combine two or more conditions using the AND operator and it will fetch only those records which satisfy all the conditions.

SELECT * FROM employees 
WHERE department = 'Sales' AND is_manager = true;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

WHERE clause with OR Operator

Using the OR operator, we can combine two or more conditions and the result set will include those records which satisfy at least one specified condition.

SELECT * FROM employees
WHERE department = 'Sales' OR department = 'HR';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

WHERE clause with NOT Operator

The NOT operator negates the condition. Simply, it will reverse the result. Let’s see the example.

SELECT * FROM employees
WHERE NOT department = 'Sales';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the query will return all the records which do NOT have the department sales.

WHERE clause with BETWEEN Operator

The BETWEEN operator is used to find the records within the specific range. Here, we will find the employees who have salaries between 50000 and 750000. Note that, the specified ranges are also considered to check the records against.

SELECT * FROM employees
WHERE salary BETWEEN 50000 AND 75000;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see, those employees having salaries of 50000 and 75000 are also included in the result.

WHERE clause with IN Operator

The IN operator is used to check the records against the list of values. For example, if you want to find the employees having departments in the specified list, you can use the IN operator.

SELECT * FROM employees
WHERE department IN ('Sales','Finance');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

WHERE clause with LIKE Operator

The LIKE operator is used to check the value against the pattern. For example, if you want to find the employees having names starting or ending with the letter ‘a’ or names that contain the word ‘ash’.

SELECT * FROM employees
WHERE first_name LIKE 'J%';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Conclusion

In this tutorial, we have learned a bunch of operators through real-world examples. Note that, the scope of the WHERE clause is not limited to the shown examples only. The WHERE clause is indeed one of the most used clauses in any DBMS. You can be creative with the conditions to find the desired output. We strongly recommend trying different kinds of conditions and combinations as much as you can.

Reference

PostgreSQL official documentation on pattern matching