Filtering with WHERE

Filter rows using comparisons, boolean logic, ranges, pattern matching, and NULL checks.

Comparison Operators

SELECT * FROM data WHERE id > 3;
SELECT * FROM data WHERE val = 20.0;
SELECT * FROM data WHERE name = 'carol';

Boolean Logic (AND, OR, NOT)

SELECT id FROM data WHERE id > 2 AND id < 5;
SELECT id FROM data WHERE id = 1 OR id = 5;
SELECT id FROM data WHERE NOT (id > 3);
SELECT id FROM data WHERE (cat = 'a' AND val > 20.0) OR cat = 'c';

BETWEEN

SELECT id FROM data WHERE id BETWEEN 2 AND 4;
SELECT id FROM data WHERE id NOT BETWEEN 2 AND 4;
SELECT id FROM data WHERE val BETWEEN 20.0 AND 40.0;

IN

SELECT id FROM data WHERE id IN (1, 3, 5);
SELECT id FROM data WHERE id NOT IN (1, 3, 5);
SELECT name FROM data WHERE cat IN ('a', 'c');

LIKE and ILIKE

-- Case-sensitive pattern matching
SELECT name FROM data WHERE name LIKE 'a%';    -- starts with 'a'
SELECT name FROM data WHERE name LIKE '%e';    -- ends with 'e'
SELECT name FROM data WHERE name LIKE '%o%';   -- contains 'o'

-- Case-insensitive
SELECT s FROM mixed WHERE s ILIKE 'a%';
SELECT s FROM mixed WHERE s ILIKE '%an%';

IS NULL / IS NOT NULL

SELECT * FROM t WHERE val IS NULL;
SELECT * FROM t WHERE val IS NOT NULL;

WHERE with Expressions

SELECT id FROM data WHERE id + 1 > 4;
SELECT id FROM data WHERE val * 2.0 > 60.0;
SELECT id FROM data WHERE ABS(val - 30.5) < 1.0;