Filtering

The WHERE clause filters rows based on conditions. Only rows where the condition evaluates to TRUE are included in the result.

Comparison Operators

SELECT name, salary FROM employees WHERE salary > 80000;

SELECT * FROM orders WHERE status = 'shipped';

SELECT * FROM products WHERE price <= 9.99;

Logical Operators: AND, OR, NOT

Combine multiple conditions with logical operators. Use parentheses to control precedence.

-- AND: both conditions must be true
SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 90000;

-- OR: at least one condition must be true
SELECT * FROM employees
WHERE department = 'Engineering' OR department = 'Research';

-- NOT: negates a condition
SELECT * FROM employees
WHERE NOT active;

-- Combining with parentheses
SELECT * FROM employees
WHERE (department = 'Engineering' OR department = 'Research')
  AND salary > 80000;

BETWEEN

Tests whether a value falls within an inclusive range. Equivalent to col >= low AND col <= high.

-- Numeric range
SELECT * FROM employees
WHERE salary BETWEEN 50000 AND 100000;

-- Date range
SELECT * FROM orders
WHERE order_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31';

-- NOT BETWEEN
SELECT * FROM products
WHERE price NOT BETWEEN 10 AND 50;

IN (List)

Tests whether a value matches any value in a list.

-- Match against a list of values
SELECT * FROM employees
WHERE department IN ('Engineering', 'Research', 'Design');

-- Numeric list
SELECT * FROM orders
WHERE status_code IN (1, 2, 5);

-- NOT IN
SELECT * FROM employees
WHERE department NOT IN ('Sales', 'Marketing');

LIKE and ILIKE

Pattern matching on string columns. LIKE is case-sensitive; ILIKE is case-insensitive.

-- Starts with 'A'
SELECT * FROM employees WHERE name LIKE 'A%';

-- Ends with 'son'
SELECT * FROM employees WHERE name LIKE '%son';

-- Contains 'eng'
SELECT * FROM employees WHERE department LIKE '%eng%';

-- Exactly 5 characters
SELECT * FROM products WHERE code LIKE '_____';

-- Case-insensitive match
SELECT * FROM employees WHERE name ILIKE '%alice%';

-- NOT LIKE
SELECT * FROM files WHERE path NOT LIKE '%.tmp';

IS NULL / IS NOT NULL

Tests whether a value is NULL. Note that = NULL does not work as expected — always use IS NULL.

-- Find rows with missing values
SELECT * FROM employees WHERE department IS NULL;

-- Find rows with non-missing values
SELECT * FROM employees WHERE department IS NOT NULL;

-- Common pattern: filter then coalesce
SELECT name, COALESCE(department, 'Unassigned') AS dept
FROM employees
WHERE salary IS NOT NULL;

Combining Multiple Filters

SELECT name, department, salary
FROM employees
WHERE department IN ('Engineering', 'Research')
  AND salary BETWEEN 80000 AND 150000
  AND name LIKE 'A%'
  AND manager_id IS NOT NULL
ORDER BY salary DESC;