Filtering and Sorting

Comparison and logical operators#

SELECT * FROM products
WHERE category = 'electronics' AND price < 800;

SELECT * FROM products
WHERE price BETWEEN 50 AND 500
   OR category IN ('home', 'toys');

BETWEEN is inclusive on both ends. IN is shorthand for a list of ORs and reads much better.

Precedence will bite you#

AND binds tighter than OR. This query:

WHERE category = 'home' OR category = 'toys' AND price < 100

means home (any price) OR (toys AND cheap). If you meant the other grouping, add parentheses. I use parentheses any time AND and OR share a line, even when I know the rules.

NULL is not a value#

SELECT * FROM customers WHERE city = NULL;     -- returns nothing
SELECT * FROM customers WHERE city IS NULL;    -- correct
SELECT * FROM customers WHERE city <> 'Pune';  -- misses NULL rows too

SQL uses three-valued logic: true, false, unknown. WHERE keeps only rows where the condition is true. NULL <> 'Pune' is unknown, so Dan disappears. To include him:

WHERE city <> 'Pune' OR city IS NULL
-- or: WHERE NOT (city <=> 'Pune')

<=> is MySQL's NULL-safe equals.

Pattern matching#

SELECT * FROM customers WHERE email LIKE '%@example.com';
SELECT * FROM customers WHERE name LIKE 'A___ %';    -- _ is exactly one char
SELECT * FROM customers WHERE name REGEXP '^(Asha|Ravi)';

% matches any run of characters, _ matches one. A leading % (like '%son') can't use a normal index, so MySQL scans every row. 'son%' can use an index. REGEXP is more powerful and always scans.

Case sensitivity depends on collation. With the default _ci collations, LIKE 'asha%' matches Asha.

Sorting and limiting#

SELECT name, price FROM products
ORDER BY category ASC, price DESC
LIMIT 3;

Without ORDER BY, row order is not guaranteed, even if it looks stable today. NULLs sort first in ascending order in MySQL.

Pagination:

SELECT id, name FROM products ORDER BY id LIMIT 10 OFFSET 20;

OFFSET 20 still reads and discards 20 rows. At OFFSET 1000000 that hurts. Later you'll see keyset pagination (WHERE id > last_seen_id), which stays fast.

Common mistakes#

• = NULL instead of IS NULL.
• Mixing AND and OR without parentheses.
• Using LIMIT without ORDER BY and treating the result as meaningful.
• Leading-wildcard LIKE on a large table.

Practice#

Write queries to find: customers with no city; products costing 100 to 500 that aren't electronics; customers whose email starts with a or m; and the two most expensive products. Then write one that returns all customers not in Pune, including those with NULL city, and explain why the naive version fails.