WHERE Clause in PostgreSQL

The WHERE clause in PostgreSQL is used to filter records based on a specified condition, allowing you to retrieve only the rows that meet the condition.

1. Basic Usage of WHERE

SELECT * FROM employees WHERE department = 'IT';

✅ Retrieves employees who work in the IT department.

2. Using Comparison Operators in WHERE

• = (equals)
• <> or != (not equal)
• > (greater than)
• < (less than)
• >= (greater than or equal)
• <= (less than or equal)

SELECT * FROM employees WHERE salary > 50000;

✅ Retrieves employees with a salary greater than 50,000.

3. Using AND and OR for Multiple Conditions

• AND: All conditions must be true.
• OR: At least one condition must be true.

SELECT * FROM employees WHERE department = 'IT' AND salary > 50000;

✅ Retrieves employees in IT with a salary greater than 50,000.

SELECT * FROM employees WHERE department = 'IT' OR department = 'HR';

✅ Retrieves employees in either the IT or HR department.

4. Using BETWEEN in WHERE

The BETWEEN operator is used to filter values within a range (inclusive).

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;

✅ Retrieves employees with a salary between 40,000 and 80,000.

5. Using IN in WHERE

The IN operator checks if a value matches any value in a list.

SELECT * FROM employees WHERE department IN ('IT', 'HR', 'Finance');

✅ Retrieves employees in the IT, HR, or Finance department.

6. Using LIKE for Pattern Matching

The LIKE operator is used for searching a specific pattern in a column.

SELECT * FROM employees WHERE first_name LIKE 'J%';

✅ Retrieves employees whose first name starts with 'J' (e.g., John, Jack).

7. Using IS NULL or IS NOT NULL

The IS NULL and IS NOT NULL operators are used to check for NULL values.

SELECT * FROM employees WHERE manager_id IS NULL;

✅ Retrieves employees who do not have a manager (i.e., manager_id is NULL).

8. Using EXISTS in WHERE

The EXISTS operator checks if a subquery returns any results.

SELECT * FROM employees eWHERE EXISTS (    SELECT 1 FROM departments d WHERE d.id = e.department_id AND d.location = 'New York');

✅ Retrieves employees who work in departments located in New York.

9. Using NOT with WHERE

The NOT keyword negates a condition.

SELECT * FROM employees WHERE NOT department = 'Sales';

✅ Retrieves employees not in the Sales department.

Performance Tip

• Using indexes on columns in WHERE conditions can significantly improve query performance, especially on large datasets.