PostgreSQL WHERE Clause

The WHERE clause in PostgreSQL is used to filter records in queries. It allows you to retrieve, update, or delete only those rows that meet specific conditions. Proper use of WHERE is critical to prevent unwanted changes and improve query performance.

1. Basic Syntax

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

- condition is an expression that evaluates to TRUE, FALSE, or NULL. - Only rows where the condition evaluates to TRUE are returned or affected.

2. Comparison Operators

Common operators used in WHERE conditions:

• = → equal
• != or <> → not equal
• >, < → greater than, less than
• >=, <= → greater than or equal, less than or equal
• BETWEEN ... AND ... → range check
• IN (...) → matches any value in a list
• LIKE → pattern matching
• IS NULL / IS NOT NULL → checks for NULL values

3. Example: Simple WHERE Conditions

-- Select employees in IT department
            SELECT * FROM employees
            WHERE department = 'IT';

            -- Select employees with salary greater than 50000
            SELECT * FROM employees
            WHERE salary > 50000;

            -- Select employees in HR or Finance
            SELECT * FROM employees
            WHERE department IN ('HR', 'Finance');

            -- Select employees with salary between 40000 and 60000
            SELECT * FROM employees
            WHERE salary BETWEEN 40000 AND 60000;

4. Using Logical Operators

You can combine multiple conditions using logical operators:

• AND – all conditions must be TRUE
• OR – at least one condition must be TRUE
• NOT – negates a condition

-- Select IT employees with salary > 60000
            SELECT * FROM employees
            WHERE department = 'IT' AND salary > 60000;

            -- Select employees in HR or with salary < 50000
            SELECT * FROM employees
            WHERE department = 'HR' OR salary < 50000;

            -- Select employees not in IT
            SELECT * FROM employees
            WHERE NOT department = 'IT';

5. Pattern Matching with LIKE

The LIKE operator is used to match patterns in text data. - % → matches any sequence of characters - _ → matches a single character

-- Select employees whose name starts with 'A'
            SELECT * FROM employees
            WHERE name LIKE 'A%';

            -- Select employees whose name ends with 'son'
            SELECT * FROM employees
            WHERE name LIKE '%son';

            -- Select employees whose name has 'li' in it
            SELECT * FROM employees
            WHERE name LIKE '%li%';

            -- Single character match
            SELECT * FROM employees
            WHERE name LIKE '_lice'; -- matches 'Alice'

6. Handling NULL Values

NULL values need special handling since comparison operators (=, !=) do not work with NULL.

-- Select employees with no department assigned
            SELECT * FROM employees
            WHERE department IS NULL;

            -- Select employees with a department assigned
            SELECT * FROM employees
            WHERE department IS NOT NULL;

7. Using EXISTS and Subqueries

The EXISTS operator checks for the existence of rows in a subquery.

-- Select employees who have bonuses
            SELECT * FROM employees e
            WHERE EXISTS (
            SELECT 1 FROM bonuses b
            WHERE b.emp_id = e.id
            );

8. Using ANY and ALL

PostgreSQL supports ANY and ALL to compare values against a set:

-- Employees with salary greater than any bonus amount
            SELECT * FROM employees
            WHERE salary > ANY (SELECT bonus FROM bonuses);

            -- Employees with salary greater than all bonus amounts
            SELECT * FROM employees
            WHERE salary > ALL (SELECT bonus FROM bonuses);

9. Combining WHERE with ORDER BY, LIMIT

Filtering often goes hand-in-hand with sorting and limiting results.

-- Top 5 highest paid IT employees
            SELECT * FROM employees
            WHERE department = 'IT'
            ORDER BY salary DESC
            LIMIT 5;

10. Best Practices

• Always use indexes on columns frequently used in WHERE for performance.
• Use EXPLAIN to analyze query execution plans.
• Be careful with NULL comparisons; use IS NULL or IS NOT NULL.
• Combine logical conditions clearly using parentheses to avoid ambiguity.

Conclusion

The WHERE clause is one of the most powerful and essential features in PostgreSQL. It allows you to filter and target specific rows for SELECT, UPDATE, DELETE, and even INSERT (with subqueries). Mastering WHERE is critical for writing efficient and accurate queries. In the next tutorial, we will explore the ORDER BY clause to sort query results.