SQL interview questions for testers on nulls, joins, duplicates and transactions

In this article

1. The practice tables used below
2. Filtering, nulls and sorting
3. Joins
4. Counting, grouping and duplicates
5. Deleting, combining and keys
6. Transactions and test data
7. Follow-up questions
8. Sources

The practice tables used below

Work through the entries with the SQL interview questions guide open beside you. Two tiny illustrative tables make every count checkable by hand. The users table holds five users, and two of them (Asha and Dara) share one email address.

The orders table holds five orders: users 1 and 2 have two each, order 104 has a missing amount, and order 105 belongs to user 9, who does not exist.¹

Filtering, nulls and sorting

How do you find rows where a value is missing, and why does = NULL fail?

Use IS NULL. A null is a marker for an unknown value, so col = NULL is never true. The PostgreSQL manual on comparison operators (accessed October 2026) says not to write it because two unknown values cannot be known to be equal. On the practice orders, amount = NULL returns zero rows and amount IS NULL returns one, order 104.¹

SELECT * FROM orders WHERE amount IS NULL;

Why it is asked: it is the first check that you understand null as "unknown", not as zero or an empty string. Read the full explanation: the SQL interview questions guide for null handling.

Why does amount <> 40 skip rows where amount is null?

An ordinary comparison with a null input yields null, not true or false (comparison operators, PostgreSQL 18 manual, accessed October 2026). A WHERE clause keeps only rows whose condition is true, so null rows vanish. On the practice orders, amount <> 40 returns three rows, not four, because order 104 drops out.¹ Add OR amount IS NULL when those rows matter.

Why it is asked: silently missing rows are a classic way a test query hides a defect. Read the full explanation: how SQL treats nulls in conditions.

What do IS DISTINCT FROM and IS NOT DISTINCT FROM do?

They are null-safe comparisons. IS DISTINCT FROM treats null as an ordinary comparable value: two nulls count as not distinct, and a null against a value counts as distinct. The result is always true or false, never null (PostgreSQL 18 manual on null-safe comparison, accessed October 2026). They suit comparing a column before and after a change when either side may be null.

Why it is asked: it shows you know how to compare data that may contain gaps. Read the full explanation: more on comparing data with gaps.

How do you sort results in ascending and descending order?

Add an ORDER BY clause at the end of the query. Rows sort ascending by default, DESC reverses the order, and several columns can be listed so the second breaks ties in the first. The manual advises an explicit top-level ORDER BY when you need results in a particular order (PostgreSQL 18 manual on sorting, accessed October 2026), so a test that compares row order must always sort.

Why it is asked: unsorted results make checks flaky. Read the full explanation: how to sort rows ascending and descending with undefined.

Joins

What is the difference between an inner join and a left join?

A join builds a derived table from two tables by a join condition. INNER is the default and keeps only rows that match. A left join does an inner join first, then adds each unmatched left row with nulls in the right-table columns (PostgreSQL 18 manual on joined tables, accessed October 2026). On the practice tables the inner join gives four rows and the left join seven.¹

SELECT u.name, o.amount FROM users u JOIN orders o ON o.user_id = u.id;       -- inner: 4 rows
SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON o.user_id = u.id;  -- left: 7 rows

Why it is asked: choosing the wrong join changes the row count and hides records. Read the full explanation: what SQL joins are and how inner, left, right and full joins differ.

How do you find users who have no orders?

Left join users to orders, then keep rows where the order key is null. A left join keeps at least one row for each left row (PostgreSQL 18 manual on outer joins, accessed October 2026), so every user survives, and the ones with no match show nulls. On the practice data this returns three users.¹

SELECT u.name FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.user_id IS NULL;

Why it is asked: finding missing child records is a routine data-validation task. Read the full explanation: how left joins find unmatched rows.

How do you find orphan records, such as orders whose user does not exist?

Reverse the direction: left join orders to users and keep rows where the user key is null. The manual's outer-join example shows the idea, with empty values substituted for the right table when no match exists (PostgreSQL 18 tutorial on joins, accessed October 2026). The practice data returns one orphan, order 105.¹

SELECT o.id FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL;

Why it is asked: orphans point to missing constraints or failed deletes. Read the full explanation: joins that reveal missing parent rows.

Why does a WHERE filter on the right table turn a left join into an inner join?

Unmatched rows carry nulls in the right-table columns, and a condition such as amount > 30 is not true for null, so WHERE removes them. After the left join of seven rows, WHERE amount > 30 leaves two.¹ Put the condition in the ON clause to keep all left rows: that version returns five.¹

-- 2 rows: the filter removes the unmatched users
SELECT u.name, o.amount FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.amount > 30;

-- 5 rows: every user is kept
SELECT u.name, o.amount FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.amount > 30;

Why it is asked: it tests whether you can work out a row count, not only write syntax. Read the full explanation: how join conditions and filters interact.

Counting, grouping and duplicates

What is the difference between COUNT(*) and COUNT(column)?

The COUNT(*) form counts rows. The COUNT(column) form counts only rows where that column is not null (aggregate functions, PostgreSQL 18 tutorial, accessed October 2026). On the practice orders, COUNT(*) is five and COUNT(amount) is four.¹ The same null rule explains why AVG(amount) gives 35 (140 over four values), not 28 (140 over five rows).¹

Why it is asked: a count that ignores nulls is a frequent source of wrong expected values. Read the full explanation: counting and aggregates with nulls in the SQL guide.

What is the difference between WHERE and HAVING?

WHERE selects input rows before groups and aggregates are computed, so it cannot contain aggregate functions. HAVING filters groups after aggregation and normally contains aggregates (PostgreSQL 18 manual, accessed October 2026). The manual's example groups by city, and HAVING max(temp_lo) < 40 keeps only Hayward. Put non-aggregate filters in WHERE.

Why it is asked: confusing the two is a common syntax and logic error. Read the full explanation: grouping and filtering in the SQL guide.

How do you find duplicate records?

Group by the column that should be unique and keep groups seen more than once. This works because HAVING filters after the groups are counted (PostgreSQL 18 manual on grouping, accessed October 2026). On the practice users it returns one group, the shared address, with a count of two.¹

SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

Why it is asked: duplicate detection is a standard data-quality check. Read the full explanation: duplicate checks with undefined and undefined.

How do you total only some rows, such as passed against failed tests?

Put a CASE expression with a WHEN branch inside the aggregate: sum a one for each row that meets the condition and a zero otherwise. PostgreSQL also offers COUNT(*) FILTER (WHERE status = 'failed') as an equivalent (aggregate expressions in the PostgreSQL 18 manual, accessed October 2026). One query then returns several conditional totals side by side, without a query per status.

Why it is asked: it shows you can build a summary report in a single pass. Read the full explanation: how a conditional total works with undefined and undefined, how to use undefined statements in SQL and undefined and undefined with multiple conditions.

How do you find the second-highest value, and what is the tie trap?

Rank the distinct values and take rank two: DENSE_RANK() OVER (ORDER BY amount DESC) gives tied rows the same rank without gaps (PostgreSQL 18 manual on window functions, accessed October 2026). The shortcut ORDER BY amount DESC LIMIT 1 OFFSET 1 returns the second row, which repeats the top value if two rows tie. In PostgreSQL, DESC sorts nulls first (sorting rows, PostgreSQL 18 manual, accessed October 2026), so add WHERE amount IS NOT NULL or NULLS LAST; without it the shortcut returns 60 on the practice orders, not the correct 40.¹

SELECT amount FROM (
  SELECT amount, DENSE_RANK() OVER (ORDER BY amount DESC) AS r FROM orders
  WHERE amount IS NOT NULL
) t WHERE r = 2;

Why it is asked: it separates a memorised shortcut from understanding ties and nulls. Read the full explanation: ranking and window questions in the SQL guide.

Deleting, combining and keys

What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes the rows you select with WHERE and leaves the table. TRUNCATE quickly removes all rows from a table, with no row filter (PostgreSQL 18 manual on undefined, accessed October 2026). DROP TABLE removes the table itself, structure included. Rollback behaviour differs between database products, so check yours.

Why it is asked: choosing the wrong one wipes test data or the table. Read the full explanation: data-changing statements in the SQL guide.

What is the difference between UNION and UNION ALL, and how does EXCEPT help compare tables?

UNION stacks two result sets and removes duplicate rows; UNION ALL keeps them. EXCEPT returns rows from the first query that are absent from the second (PostgreSQL 18 manual on combining queries, accessed October 2026). Testers run source EXCEPT target to find rows lost in a migration. Combining the users email column with itself, UNION ALL gives ten rows and UNION four (a, b, c and e).¹

SELECT email FROM users UNION ALL SELECT email FROM users;  -- 10 rows
SELECT email FROM users UNION SELECT email FROM users;      -- 4 rows

SELECT user_id FROM orders EXCEPT SELECT id FROM users;  -- returns 9, the orphan's user

Why it is asked: data-migration and extract-transform-load checks rely on these set operators. Read the full explanation: set operators in the SQL guide.

What is the difference between a primary key and a foreign key?

A primary key identifies each row: its values must be unique and not null. A foreign key says a column's values must match a row in another table (PostgreSQL 18 manual on constraints, accessed October 2026). Declaring user_id as a foreign key to users(id) makes the database reject order 105, so that orphan could not be inserted. On the practice data the constraint below cannot even be added until order 105 is fixed; afterwards such a row is rejected.

ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users (id);

Why it is asked: keys are the database's own defence against the orphan checks above. Read the full explanation: how relational tables relate, in the SQL guide.

Transactions and test data

What is a transaction, and what does atomic mean?

A transaction groups several statements into one unit. It is atomic: from the point of view of other transactions, it either happens completely or not at all (PostgreSQL 18 manual, accessed October 2026). In PostgreSQL you surround the statements with BEGIN and COMMIT. Committed changes become visible together, and rolled-back changes never become visible.

Why it is asked: partial updates are a defect class testers must recognise. Read the full explanation: What is transaction control language (TCL) in SQL?.

How can a tester use ROLLBACK to keep test data clean?

Start a transaction, run the setup and the statements under test, check the results with SELECT, then ROLLBACK. The changes never become visible to other sessions, so the data returns to its earlier state (PostgreSQL 18 manual on rollback, accessed October 2026). In an illustrative transfer of 100.00, a balance of 500 reads 400 inside the transaction and 500 again after the rollback.¹

Why it is asked: it shows you protect shared test environments. Read the full explanation: rollback and commit in transaction control.

What is a savepoint?

A savepoint marks a point inside a transaction so that you can undo only the later work. After defining one with SAVEPOINT, you roll back to it with ROLLBACK TO and the earlier statements stay in the transaction (PostgreSQL 18 manual on savepoints, accessed October 2026). The manual's example debits an account, sets a savepoint, then rolls back only the step after it.

Why it is asked: it separates basic commit and rollback knowledge from finer control. Read the full explanation: savepoints and partial rollback in TCL.

How do you check that an action on the screen saved the right data?

Record what you entered, perform the action, then query the table for that record by a unique value and compare each column with what you expected. Check the row count too: one record, not zero and not two. Use IS NOT DISTINCT FROM for columns that may be null, and clean up the test row afterwards.

Why it is asked: it tests whether you verify the back end, not only the screen. Read the full explanation: back-end checks in the complete SQL guide.

Follow-up questions

What SQL queries are asked in a QA tester interview?

Candidates typically prepare for the topics above: null handling, joins, grouping, duplicates, set operations, keys and transactions, because they are the checks a tester runs on data. The set differs by employer, so treat the list as practice, not as a forecast. The wider SQL interview questions guide covers the rest of the language.

How much SQL does a tester need to know?

A strong answer shows you can read and write SELECT queries with filters, joins and grouping, and explain why a count or a missing row is wrong. Writing schema changes is less central than reading data accurately. Say which database you used, since details differ between products.

Which SQL joins do testers use most?

In most data checks you will need the inner join and the left join: the first confirms matching records, the second, with a null test, finds missing or orphan ones. Know the row-count effect of each, as shown in the table near the top.

How do you practise SQL for a tester interview?

Build a small data set like the practice tables, write each query, and work out the row count before you run it. Then say the answer aloud in two or three sentences. The coding interview guide describes the same think-aloud routine.

Sources

1. Joined Tables and Sorting Rows (PostgreSQL 18 manual, table expressions) — PostgreSQL Global Development Group, PostgreSQL 18 manual, 26 Oct 2026
2. Joins Between Tables (PostgreSQL 18 manual, tutorial) — PostgreSQL Global Development Group, PostgreSQL 18 manual, 26 Oct 2026
3. Aggregate Functions (PostgreSQL 18 manual, tutorial) — PostgreSQL Global Development Group, PostgreSQL 18 manual, 26 Oct 2026
4. Comparison Functions and Operators (PostgreSQL 18 manual) — PostgreSQL Global Development Group, PostgreSQL 18 manual, 26 Oct 2026
5. Transactions (PostgreSQL 18 manual, tutorial) — PostgreSQL Global Development Group, PostgreSQL 18 manual, 26 Oct 2026
6. TRUNCATE (PostgreSQL 18 manual) — PostgreSQL Global Development Group, PostgreSQL 18 manual, 26 Oct 2026
7. Combining Queries: UNION, INTERSECT, EXCEPT (PostgreSQL 18 manual) — PostgreSQL Global Development Group, PostgreSQL 18 manual, 26 Oct 2026
8. Constraints (PostgreSQL 18 manual) — PostgreSQL Global Development Group, PostgreSQL 18 manual, 26 Oct 2026
9. Window Functions (PostgreSQL 18 manual) — PostgreSQL Global Development Group, PostgreSQL 18 manual, 26 Oct 2026
10. Value Expressions: aggregate expressions and FILTER (PostgreSQL 18 manual) — PostgreSQL Global Development Group, PostgreSQL 18 manual, 26 Oct 2026
11. Sorting Rows: ORDER BY and NULLS FIRST / NULLS LAST (PostgreSQL 18 manual) — PostgreSQL Global Development Group, PostgreSQL 18 manual, 26 Oct 2026