This page needs a newer browser to run its tools. You can still read everything here.

SQL (PostgreSQL, MySQL, SQLite) Module 6

Subqueries and set operations interview questions

Interview questions on subqueries and set operations: short warm-ups, then what each harder question tests, the first step of a good answer and its follow-ups.

15 questions: 4 warm-ups and 11 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher to mid

What is the difference between UNION and UNION ALL, and when do you use each?

Answered in the lesson UNION, INTERSECT and EXCEPT in SQL
Fresher to mid

What is a scalar subquery, and what does it return when its query finds no row?

Answered in the lesson Subqueries in SELECT, WHERE and FROM
Fresher to mid

What is the difference between IN and EXISTS with a subquery?

Answered in the lesson IN, EXISTS, ANY/ALL and anti-joins
Fresher to mid

What is a correlated subquery, and how often does it run?

Answered in the lesson Correlated subqueries in SQL

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Fresher to mid

A finance report adds up refunds with SUM over a UNION of two refund tables, and its total is lower than the two tables' totals added together. Nobody deleted anything. Explain what happened, and rewrite the query so that the total is right and stays right.
What it tests

Duplicate removal in UNION, different events that look identical, UNION ALL with a source column, and a check that catches the problem.

First step

Count rows before adding money: the rows of each table, and the rows of the UNION. Fewer rows in the UNION means it merged some. Then look at the selected columns: with only the amount, or the amount and the order id, two genuine refunds of the same value become one row. Combine with UNION ALL and add a column that names the source table.
Follow-ups
- Would SELECT DISTINCT on each table, followed by UNION ALL, also be right?
- How do you test the report so that the mistake cannot come back?
- When is UNION the right choice in a report?
From the lesson UNION, INTERSECT and EXCEPT in SQL
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

You copied a 3-million-row customers table to a new database and must show that the copy matches the original. How do you find missing, extra and changed rows with SQL, and what can make a check that returns nothing still miss differences?
What it tests

EXCEPT in both directions, duplicates and EXCEPT ALL, NULLs in set operations, collations and types across engines, and checking a large table without moving all of it.

First step

Compare the row counts, then run original EXCEPT copy and copy EXCEPT original over every column, in one database that can see both. The first lists rows that are missing or changed in the copy, the second rows that are extra or changed. Both empty, with equal counts, is good evidence, unless duplicates or a looser comparison hide something.
Follow-ups
- The table has duplicate rows. Is EXCEPT still enough?
- The original is in PostgreSQL and the copy in MySQL. What can differ although the data is the same?
- How do you check 3 million rows without copying them all into one place?
From the lesson UNION, INTERSECT and EXCEPT in SQL
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to mid

Write a query that returns the second-highest distinct order amount of an orders table as a single value, and NULL when there is no second amount. Then explain what changes when two orders share the highest amount.
What it tests

Scalar subqueries, NULL from an empty result, distinct values against rows, and portable alternatives to LIMIT with OFFSET.

First step

Use the highest amount as a scalar subquery and take the largest amount below it: SELECT max(amount) FROM orders WHERE amount < (SELECT max(amount) FROM orders). max() over no rows is NULL, so a table with one distinct amount returns NULL without special code, and ties at the top change nothing.
Follow-ups
- Why not ORDER BY amount DESC LIMIT 1 OFFSET 1?
- How do you return the Nth highest amount for any N?
- What changes if amount can be NULL?
From the lesson Subqueries in SELECT, WHERE and FROM
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Mid to senior

A nightly report ran on SQLite for a year. After the move to PostgreSQL it fails with "more than one row returned by a subquery used as an expression", although the data was copied unchanged. What was the report doing in SQLite, and how do you fix it?
What it tests

SQLite's first-row rule for scalar subqueries, why the old figures may have been wrong, and guards that state the intent of the query.

First step

Run the scalar subquery from the failing statement on its own, for the rows where it fails, in SQLite: it returns several rows, and SQLite silently used the first. Which row came first depended on the plan, so last year's figures may be wrong. Decide what the one value should mean, then write that rule into the subquery.
Follow-ups
- How do you find out whether last year's reports were wrong?
- Which guard do you choose, an aggregate, ORDER BY with LIMIT 1, or a unique constraint?
- How do you find other queries with the same problem before they fail?
From the lesson Subqueries in SELECT, WHERE and FROM
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to senior

A customer statement joins customers, orders and payments and groups by customer. For some customers the ordered amount is exactly two or three times too high, and one customer with no payments is missing. Explain the cause and write a correct query.
What it tests

Row multiplication when two child tables join one parent, aggregating in derived tables first, outer joins for missing children, and reconciling totals.

First step

Count the joined rows per customer: they equal the customer's orders times their payments, so every order repeats once per payment. Aggregate orders and payments separately, in derived tables with one row per customer, then LEFT JOIN both to customers and turn missing totals into 0 with coalesce().
Follow-ups
- Why does count(DISTINCT o.id) look right while the sum stays wrong?
- Would correlated subqueries in the SELECT list be a better fix?
- How would you catch this mistake in a test?
From the lesson Subqueries in SELECT, WHERE and FROM
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to senior

A weekly report lists customers who have never ordered, using WHERE id NOT IN (SELECT customer_id FROM orders). The week guest checkout was released, it returned zero rows, and the marketing team celebrated. What happened, and how do you fix the report and keep this kind of bug out?
What it tests

NOT IN with a NULL in the subquery, three-valued logic, the safe anti-join forms, and schema and test changes that prevent the bug.

First step

Check whether orders.customer_id now contains NULLs: guest orders have no customer. With one NULL in the list, id NOT IN (...) is unknown for every customer without orders, so WHERE keeps no one. Rewrite the anti-join with NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id), which ignores the NULL.
Follow-ups
- Why did the report not fail with an error?
- Is adding WHERE customer_id IS NOT NULL inside the subquery an acceptable fix?
- How do you stop the same bug appearing in other reports?
From the lesson IN, EXISTS, ANY/ALL and anti-joins
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A PostgreSQL query keeps the products priced above ALL prices of a chosen category. Ported to SQLite as price > (SELECT max(price) ...), it returns different rows for some categories. Explain when and why, and write a SQLite rewrite that behaves like ALL.
What it tests

The definition of ALL for empty and NULL-containing subqueries, aggregate rewrites, NOT EXISTS with explicit NULL handling, and deciding the intended meaning.

First step

Test both on two edge cases: a category with no products and one with a NULL price. ALL is true over no rows, but max() of no rows is NULL, so the rewrite keeps nothing; max() ignores NULLs, while ALL becomes unknown. Write NOT EXISTS (SELECT 1 ... WHERE v >= x OR v IS NULL OR x IS NULL) instead.
Follow-ups
- Which behaviour does the business want for an empty category?
- Is the max() rewrite ever exactly right?
- How would you test the port?
From the lesson IN, EXISTS, ANY/ALL and anti-joins
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

Find the customers who have never ordered, among 20 million customers and 600 million orders in PostgreSQL. Which anti-join do you write, which indexes and constraints matter, and what do you look for in the plan?
What it tests

Anti-join forms and their plans, hashed subplans and memory, an index on the correlated column, NOT NULL constraints, and running a heavy query safely.

First step

Write NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id): it is correct with NULLs and the planner can run it as an anti-join. Check that orders.customer_id is indexed and, where the business allows, NOT NULL. Then read EXPLAIN: an anti-join node is what you want, a SubPlan run once per customer is what you avoid.
Follow-ups
- Why not NOT IN at this size?
- The result is needed every hour. What would you change?
- How do you check the plan without running the full query?
From the lesson IN, EXISTS, ANY/ALL and anti-joins
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to mid

Return each customer's latest order from orders(id, customer_id, placed_at, amount). Two orders can share a timestamp. Write the query, then change it so that exactly one row per customer comes back, and say which row you return and why.
What it tests

Correlated subqueries for the latest row per group, ties, tie-breakers on a unique key, window functions, and NULL timestamps.

First step

Start with the readable form: keep each order whose placed_at equals (SELECT max(placed_at) FROM orders o2 WHERE o2.customer_id = o.customer_id). It returns every tied order. For exactly one row, make the tie-break part of the question, for example the highest id, with ORDER BY placed_at DESC, id DESC LIMIT 1 or row_number().
Follow-ups
- What happens to a customer whose only order has a NULL placed_at?
- Which index makes the correlated version fast?
- How do you return the latest three orders of each customer?
From the lesson Correlated subqueries in SQL
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A report with a correlated subquery in its WHERE clause finished in seconds while the orders table was small. At 2 million orders it runs for over an hour. What do you check first, and what are your options to make it fast?
What it tests

How correlated subqueries scale, reading plans for subqueries that run per row, the index on the correlated column, and rewrites as joins or window functions.

First step

Read the plan. A correlated subquery that scans orders for every outer row costs outer rows times inner rows, here up to 2 million × 2 million row visits. Check for an index on the correlated column together with the column the subquery aggregates; add it, or rewrite the query as a join to a grouped derived table or a window function.
Follow-ups
- The index exists, but the plan does not use it. Why might that be?
- When is a rewrite better than adding the index?
- How do you prove that the rewrite returns the same rows?
From the lesson Correlated subqueries in SQL
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to mid

Two versions of a customer report count orders: one with a correlated subquery in the SELECT list, the other with LEFT JOIN orders and COUNT(*). They agree for most customers but not for all. Which customers differ, which version is right, and why?
What it tests

COUNT(*) against COUNT(column) after an outer join, rows filled with NULLs by the outer join, and checking a rewrite against the original.

First step

Compare them for a customer with no orders: the correlated count() is 0, but the LEFT JOIN keeps one row with NULLs for that customer, and COUNT() counts it as 1. Count a column of orders that is never NULL in a real match, such as o.id, and the two versions agree.
Follow-ups
- Could the join version also count too many for customers who do have orders?
- Which version is faster for 10 customers, and which for 10 million?
- How would you test that the two versions agree?
From the lesson Correlated subqueries in SQL
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes

The lessons of this module

UNION, INTERSECT and EXCEPT in SQL Free lesson, Pro answers: 20 minutes
Subqueries in SELECT, WHERE and FROM Free core, Pro depth: 8 of 25 minutes free
IN, EXISTS, ANY/ALL and anti-joins Free core, Pro depth: 9 of 30 minutes free
Correlated subqueries in SQL Free core, Pro depth: 8 of 25 minutes free

All SQL (PostgreSQL, MySQL, SQLite) interview questions · The SQL (PostgreSQL, MySQL, SQLite) track

Warm-up questions

What is the difference between UNION and UNION ALL, and when do you use each?

What is a scalar subquery, and what does it return when its query finds no row?

What is the difference between IN and EXISTS with a subquery?

What is a correlated subquery, and how often does it run?

Questions with model answers

A finance report adds up refunds with SUM over a UNION of two refund tables, and its total is lower than the two tables' totals added together. Nobody deleted anything. Explain what happened, and rewrite the query so that the total is right and stays right.

You copied a 3-million-row customers table to a new database and must show that the copy matches the original. How do you find missing, extra and changed rows with SQL, and what can make a check that returns nothing still miss differences?

Write a query that returns the second-highest distinct order amount of an orders table as a single value, and NULL when there is no second amount. Then explain what changes when two orders share the highest amount.

A nightly report ran on SQLite for a year. After the move to PostgreSQL it fails with "more than one row returned by a subquery used as an expression", although the data was copied unchanged. What was the report doing in SQLite, and how do you fix it?

A customer statement joins customers, orders and payments and groups by customer. For some customers the ordered amount is exactly two or three times too high, and one customer with no payments is missing. Explain the cause and write a correct query.

A weekly report lists customers who have never ordered, using WHERE id NOT IN (SELECT customer_id FROM orders). The week guest checkout was released, it returned zero rows, and the marketing team celebrated. What happened, and how do you fix the report and keep this kind of bug out?

A PostgreSQL query keeps the products priced above ALL prices of a chosen category. Ported to SQLite as price > (SELECT max(price) ...), it returns different rows for some categories. Explain when and why, and write a SQLite rewrite that behaves like ALL.

Find the customers who have never ordered, among 20 million customers and 600 million orders in PostgreSQL. Which anti-join do you write, which indexes and constraints matter, and what do you look for in the plan?

Return each customer's latest order from orders(id, customer_id, placed_at, amount). Two orders can share a timestamp. Write the query, then change it so that exactly one row per customer comes back, and say which row you return and why.

A report with a correlated subquery in its WHERE clause finished in seconds while the orders table was small. At 2 million orders it runs for over an hour. What do you check first, and what are your options to make it fast?

Two versions of a customer report count orders: one with a correlated subquery in the SELECT list, the other with LEFT JOIN orders and COUNT(*). They agree for most customers but not for all. Which customers differ, which version is right, and why?