SQL Cheat Sheet for Testers (2026): Queries, Joins & Patterns

SQL Cheat Sheet for Testers

The SQL a tester actually uses, on one page — filtering, NULLs, joins, aggregates, subqueries, window functions and the data validation patterns you'll run after UI and API tests. Every query was run on MariaDB/MySQL against the small sample table below, and the results shown are real. For full explanations, see The Complete SQL for Testers Guide.

Sample Data

An orders table (order_id, emp_id, amount, status) holds four orders, one of them for a non-existent employee 9, and one with a NULL amount.

Command Categories

DELETE vs TRUNCATE vs DROP

• DELETE FROM t WHERE … — removes chosen rows; can be rolled back inside a transaction.
• TRUNCATE TABLE t — removes all rows quickly, keeps the table; usually can't be rolled back.
• DROP TABLE t — removes the table itself.

As a tester, you'll mostly use SELECT — and should never run DELETE or UPDATE without a WHERE clause, or against anything but a test database.

SELECT and Filtering

SELECT DISTINCT name, salary
FROM employees
WHERE dept = 'QA'
  AND salary BETWEEN 50000 AND 90000
  AND name LIKE 'N%'
  AND manager_id IS NOT NULL
ORDER BY salary DESC
LIMIT 10;
-- → Nikhil | 60000   (Naveen has no manager)

The NULL trap ⭐

SELECT COUNT(*) FROM employees WHERE email = NULL;   -- → 0  (always — NULL is never "equal")
SELECT COUNT(*) FROM employees WHERE email IS NULL;  -- → 1  (Meera)

Replace NULLs in output with COALESCE(email, '(missing)').

Joins ⭐

-- Self join: each employee with their manager
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- → Naveen NULL, Asha Naveen, Ravi NULL, Meera Ravi, Nikhil Naveen, John Ravi

-- Orphans: orders for employees that don't exist (a data-integrity bug)
SELECT o.*
FROM orders o
LEFT JOIN employees e ON e.id = o.emp_id
WHERE e.id IS NULL;
-- → order 12347, emp_id 9

-- Employees with no orders
SELECT name FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.emp_id = e.id);
-- → John, Meera, Naveen, Nikhil

Watch out: a filter on the right-hand table in WHERE turns a LEFT JOIN into an INNER JOIN — put it in ON instead. Full examples with result tables: SQL Joins Compared.

Aggregates and Grouping

SELECT dept, COUNT(*) AS total, AVG(salary) AS avg_salary
FROM employees
WHERE status = 'active'          -- filters rows BEFORE grouping
GROUP BY dept
HAVING COUNT(*) >= 2             -- filters groups AFTER grouping
ORDER BY avg_salary DESC;
-- → DEV | 2 | 82500
--   QA  | 3 | 72333.33

SELECT COUNT(*), COUNT(amount), SUM(amount), AVG(amount) FROM orders;
-- → 4 | 3 | 1848.00 | 616.00   (the NULL amount is skipped — AVG divides by 3, not 4)

Subqueries and Rankings

-- Second-highest salary ⭐
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- → 85000   (correct even though two people share the top salary of 95000)

-- Nth highest with DENSE_RANK (N = 2 here)
SELECT DISTINCT salary FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) t
WHERE rnk = 2;
-- → 85000

-- Rank within each department
SELECT name, dept, salary,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM employees;
-- DEV: Meera 1, Ravi 1, John 3   ← RANK leaves a gap after a tie
-- QA:  Naveen 1, Asha 2, Nikhil 3

RANK vs DENSE_RANK vs ROW_NUMBER: RANK leaves gaps after ties (1, 1, 3), DENSE_RANK doesn't (1, 1, 2), ROW_NUMBER numbers every row uniquely. Window functions need MySQL 8+ or MariaDB 10.2+. More: Subqueries & Nested Queries.

CASE, UNION and Useful Functions

SELECT name,
       COALESCE(email, '(missing)') AS email,
       CASE WHEN salary >= 90000 THEN 'High'
            WHEN salary >= 70000 THEN 'Mid'
            ELSE 'Entry' END AS band
FROM employees;

SELECT email FROM employees WHERE dept = 'QA'
UNION                       -- removes duplicates; UNION ALL keeps them
SELECT email FROM employees WHERE salary > 90000;

Also handy: UPPER(), LOWER(), TRIM(), LENGTH(), CONCAT(), SUBSTRING(), CURRENT_DATE, DATE_ADD(), and date arithmetic like CURRENT_DATE - INTERVAL 7 DAY.

Tester Query Patterns ⭐

-- Duplicates (a unique-email rule is broken)
SELECT email, COUNT(*) AS cnt
FROM employees
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;
-- → asha@x.com | 2

-- Verify the record your UI/API test just created
SELECT * FROM orders WHERE order_id = 12345;

-- Recently created data
SELECT COUNT(*) FROM bugs WHERE created_date >= CURRENT_DATE - INTERVAL 7 DAY;

-- Workload per assignee
SELECT assigned_to, COUNT(*) AS bug_count FROM bugs GROUP BY assigned_to ORDER BY bug_count DESC;

-- Pass/fail summary
SELECT status, COUNT(*) FROM test_results GROUP BY status;

-- Users who didn't log in today (condition in ON, not WHERE)
SELECT u.name
FROM users u
LEFT JOIN logins l ON u.id = l.user_id AND l.login_date = CURRENT_DATE
WHERE l.user_id IS NULL;

Typical checks after a UI or API action: the row exists with the right values, status fields changed correctly, no duplicates were created, related child rows exist, and nothing was orphaned by a delete. More: SQL Queries for Testers.

Transactions, ACID and Normalization

START TRANSACTION;
UPDATE employees SET salary = salary + 1000 WHERE id = 2;   -- 72000 → 73000 inside the transaction
ROLLBACK;                                                     -- back to 72000

Wrapping test-data changes in a transaction and rolling back leaves the database clean. ACID — Atomicity (all or nothing), Consistency (rules hold), Isolation (transactions don't interfere), Durability (committed data survives failures).

Practice writing these under time pressure in the SQL Interview Simulator.

From Real Projects

Much of my testing on Apkope and Canolog was about data: prospect and customer data captured from many channels on Apkope, and records flowing between sales, inventory, finance and service on Canolog. Checking what the application shows is only half the job — the data behind it has to be right too, which is where SQL helps testers most. Keep your most-used verification queries in one place for quick reuse.

📚 Official documentation: MySQL Reference Manual

FAQs

How much SQL does a tester need?

SELECT with filtering, NULL handling, joins, GROUP BY/HAVING, subqueries (second-highest salary) and a few validation patterns — enough to verify data after UI and API actions.

WHERE vs HAVING?

WHERE filters rows before grouping; HAVING filters groups after aggregation.

Why does "= NULL" return nothing?

NULL means "unknown", so comparisons with it are never true. Use IS NULL or IS NOT NULL.

How do you find the second-highest salary?

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees), or DENSE_RANK for the Nth highest.

How do you find duplicate records?

GROUP BY the columns that should be unique and keep groups with HAVING COUNT(*) > 1.

Go Deeper

• The Complete SQL for Testers Guide
• SQL Joins Explained
• Top 25 SQL Interview Questions for Testers