PostgreSQL WHERE statement

PostgreSQL WHERE statement is used to filter results from SELECT, INSERT, UPDATE, or DELETE statement.

The syntax for WHERE statement in PostgreSQL

WHERE conds;

Statement parameters and arguments

• conds – The conditions that must be met to select records.

Example of a statement with one condition

It’s difficult to explain the PostgreSQL syntax of the WHERE statement, so let’s look at a few examples.

SELECT *
FROM empls
WHERE f_name = 'Frosya';

In this example, we used the WHERE statement to filter our results from the employee table. The above SELECT operator returns all rows from the employee table, where first_name is ‘Frosya’. Since SELECT uses *, all fields from the employee table will appear in the result set.

An example statement using the AND condition

SELECT *
FROM empls
WHERE l_name = 'Anderson'
AND empl_id <= 400;

This example uses the WHERE statement to define several conditions. In this case, the SELECT operator uses the AND condition to return all lines from employees whose last_name is ‘Anderson’ and whose employee_id is less than or equal to 400.

An example of a statement using the OR condition

SELECT product_name
FROM products
WHERE product_type = 'Hardware'
OR product_type = 'Software';

This example uses the WHERE statement to define several conditions, but instead of using the AND condition, it uses the OR condition. In this case, this SELECT statement will return all product_name values where product_type has ‘Hardware’ or ‘Software’.

Example of the combining statement of AND and OR conditions

SELECT *
FROM empls
WHERE (l_name = 'Abramov' AND first_name = 'Petr')
OR (empl_id >= 699);

This example uses the WHERE statement to define several conditions, but it combines AND and OR conditions. This example returns all employees whose last_name is ‘Abramov’ and whose first_name is ‘Petr’, as well as all entries from employees whose employee_id is greater than or equal to 699.

The brackets define the order in which the AND and OR conditions are evaluated

Example operator when connecting tables:

SELECT products.product_name, inventory.quantity
FROM products
INNER JOIN inventory
ON products.product_id = inventory.product_id
WHERE products.product_type = 'Hardware';

This example uses the WHERE statement to connect several tables to a single SELECT statement. This SELECT statement will return all product_name and quantity values if the product and inventory tables have a corresponding product_id based entry and where product_type has a value of ‘Hardware’.

PostgreSQL: Where Clause and AND | Course