How to use the WHERE clause in PostgreSQL

Introduction

The WHERE clause in PostgreSQL is a powerful tool for filtering records. It allows you to specify conditions on columns for the rows to be returned. This guide explains the usage of WHERE with practical and progressively advanced examples.

Basic Usage of WHERE

The basic syntax for using WHERE in PostgreSQL is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Let’s say we have a table employees with columns id, name, position, and salary. To find all employees who work as ‘Manager’, you’d use:

SELECT *
FROM employees
WHERE position = 'Manager';

In this query, * denotes that all columns should be returned for each row that satisfies the condition position = 'Manager'.

Using Operators in WHERE

In PostgreSQL, you can use various operators within the WHERE clause, such as:

• Equality: =
• Inequality: != or <>
• Less than and greater than: <, >
• Less than or equal to and greater than or equal to: <=, >=
• Between: BETWEEN
• Like (for pattern matching): LIKE
• In (to specify multiple possible values): IN

Filtering employees who earn more than 50000 would look like this:

SELECT name, salary
FROM employees
WHERE salary > 50000;

Combining Conditions with AND, OR, NOT

To specify multiple conditions, you can use AND, OR, and NOT. For example, to find managers with a salary greater than 60000:

SELECT name, salary
FROM employees
WHERE position = 'Manager'
AND salary > 60000;

You can also find employees who are either ‘Sales Representative’ or ‘Consultant’ like this:

SELECT name, position
FROM employees
WHERE position = 'Sales Representative'
OR position = 'Consultant';

Using NOT, you can exclude certain records:

SELECT name, salary
FROM employees
WHERE NOT position = 'Intern';

Pattern Matching with LIKE

The LIKE operator is used for pattern matching within the WHERE clause. For example, to find employees whose names start with the letter ‘J’:

SELECT name
FROM employees
WHERE name LIKE 'J%';

Using ‘_’ in LIKE can match any single character:

SELECT name
FROM employees
WHERE name LIKE 'J_n%';

Range Queries with BETWEEN

For specifying a range of values, BETWEEN is used:

SELECT name, salary
FROM employees
WHERE salary BETWEEN 40000 AND 80000;

Remember that BETWEEN is inclusive; it includes the boundary values as well.

IN Clause

The IN clause helps specify a list of values you want to match against in a column:

SELECT name, position
FROM employees
WHERE position IN ('Manager', 'Team Lead', 'Developer');

This returns employees who are either Manager, Team Lead, or Developer.

Using WHERE with Subqueries

A subquery is a query within another query. For example, if you need to find the names of employees who have the highest salary:

SELECT name
FROM employees
WHERE salary = (
 SELECT MAX(salary)
 FROM employees
);

Using WHERE with JOINs

WHERE can also be used to filter records when combining tables with JOINs. Assume we have another table departments:

SELECT e.name, d.name AS department_name
FROM employees e
JOIN departments d
 ON e.department_id = d.id
WHERE d.name = 'Sales';

This query retrieves all employees working in the Sales department.

Conclusion

With this guide, you should now have a solid understanding of using the WHERE clause in PostgreSQL. Practice with these examples, and explore more with your datasets to master efficient data retrieval.