Cheat sheets / SQL for testers
SQL for testers
Joins, grouping, duplicates, and the queries you write to verify data.
Joins
- INNER
- Only rows that match on both sides.
- LEFT
- All rows on the left, matches or NULL on the right. Use with IS NULL to find missing links.
- FULL / CROSS
- All rows from both sides; every combination.
SELECT c.* FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL;Grouping
- GROUP BY with HAVING
- WHERE filters rows before grouping; HAVING filters groups after.
- Duplicates
- Group by the key and keep counts above one.
SELECT customer_id, SUM(amount) t FROM orders GROUP BY customer_id HAVING SUM(amount) > 1000;SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;Ranking and top N
- Nth highest
- DENSE_RANK handles ties better than LIMIT and OFFSET.
- Latest row per group
- ROW_NUMBER partitioned by the group, ordered by time descending, keep 1.
SELECT * FROM (SELECT e.*, DENSE_RANK() OVER (ORDER BY salary DESC) r FROM emp e) x WHERE r = 2;Traps
- NULL comparisons
- = NULL never matches; use IS NULL. COUNT(col) skips NULLs, COUNT(*) does not.
- DELETE vs TRUNCATE vs DROP
- Rows with a WHERE and a log; all rows fast; the table itself.