Filtering with WHERE
AND, OR, NOT, IN, BETWEEN, LIKE, and NULL
Skills taught: sql.where, sql.and-or-not, sql.in, sql.between, sql.like, sql.null
Basic conditions
-- Equality
SELECT * FROM users WHERE city = 'Delhi';
-- Comparison
SELECT * FROM products WHERE price > 1000;
SELECT * FROM products WHERE price <= 500;
-- Not equal
SELECT * FROM products WHERE category != 'Books';
-- OR use: category <> 'Books'
-- Multiple conditions with AND / OR
SELECT * FROM products WHERE category = 'Electronics' AND price < 1000;
SELECT * FROM users WHERE city = 'Delhi' OR city = 'Mumbai';
-- NOT
SELECT * FROM products WHERE NOT category = 'Books';
-- Parentheses for grouping
SELECT * FROM products
WHERE (category = 'Books' OR category = 'Electronics')
AND price < 1000;
IN, BETWEEN, LIKE, and NULL
-- IN — match any value in a list
SELECT * FROM users WHERE city IN ('Delhi', 'Mumbai', 'Chennai');
-- NOT IN
SELECT * FROM products WHERE category NOT IN ('Books');
-- BETWEEN — inclusive range
SELECT * FROM products WHERE price BETWEEN 500 AND 1000;
-- Same as: price >= 500 AND price <= 1000
-- LIKE — pattern matching
SELECT * FROM products WHERE name LIKE 'W%'; -- starts with W
SELECT * FROM products WHERE name LIKE '%book%'; -- contains 'book'
SELECT * FROM users WHERE email LIKE '%@example.com';
-- % = any number of chars, _ = exactly one char
-- IS NULL / IS NOT NULL
SELECT * FROM orders WHERE total IS NOT NULL;
-- NEVER use: WHERE total = NULL (always returns nothing!)
Frequently Asked Questions
What is the difference between IN and = with OR?
WHERE city IN ('Delhi', 'Mumbai') is exactly equivalent to WHERE city = 'Delhi' OR city = 'Mumbai' — just shorter and more readable. IN is preferred for 3+ values. IN also accepts a subquery: WHERE id IN (SELECT user_id FROM orders).
Is LIKE case-sensitive?
In SQLite: LIKE is case-insensitive for ASCII characters (A matches a). In PostgreSQL: LIKE is case-sensitive; use ILIKE for case-insensitive. In MySQL: case-sensitivity depends on the collation. Best practice: normalise your data (store lowercase emails) to avoid depending on case-insensitive matching.