SQL UNION and set operations interview questions, with answers

The questions, with answers

1. 1.What is the difference between UNION and UNION ALL?

   Both stack the rows of two SELECTs into one result. UNION removes duplicate rows — it deduplicates across both inputs and within each of them — while UNION ALL keeps every row exactly as produced. That makes UNION ALL both faster and, more often than not, correct: deduplication needs a sort or hash over the whole result, and it silently changes counts and totals if legitimately repeated rows exist. Reach for UNION only when you genuinely want a set; say UNION ALL by default in an interview and explain why.

   The follow-up: rows Pune, Delhi, Pune from one table and Delhi, Goa from another give three rows under UNION (Pune, Delhi, Goa) and five under UNION ALL.

   SELECT city FROM Customers
   UNION ALL              -- keeps every row, no dedup pass
   SELECT city FROM Suppliers;

2. 2.What does it mean for two SELECTs to be union-compatible?

   They must return the same number of columns, and the columns must have compatible types position by position — the first column of the second query is matched with the first column of the first, and so on, by position, never by name. The result takes its column names from the first SELECT. So SELECT id, name UNION SELECT name, id compiles if the types happen to be coercible but quietly puts ids under the name heading. Mismatched column counts are an immediate error; mismatched types are an error or an implicit conversion depending on the database, which is why the safest habit is to list the columns explicitly in the same order in both halves rather than using SELECT *.

3. 3.What do INTERSECT and EXCEPT do, and which databases support them?

   INTERSECT returns rows that appear in both queries; EXCEPT returns rows in the first query that do not appear in the second, and Oracle calls it MINUS. Both remove duplicates unless you write INTERSECT ALL or EXCEPT ALL, which PostgreSQL supports. PostgreSQL, SQL Server, Oracle and SQLite have had them for years; MySQL added INTERSECT and EXCEPT only in 8.0.31, and MariaDB in 10.3. On an older MySQL the rewrite is an INNER JOIN or EXISTS for INTERSECT and a LEFT JOIN with IS NULL, or NOT EXISTS, for EXCEPT. Being able to state the version cut-off is a tie-breaker that interviewers notice.

   -- students in both lists
   SELECT student_id FROM Enrolled2024
   INTERSECT
   SELECT student_id FROM Enrolled2025;
   
   -- enrolled in 2024 but not in 2025
   SELECT student_id FROM Enrolled2024
   EXCEPT
   SELECT student_id FROM Enrolled2025;

4. 4.Where can ORDER BY and LIMIT go in a UNION query?

   Only once, at the very end, and they apply to the combined result — an ORDER BY between the halves is a syntax error in most databases. If you need one half sorted or limited on its own (the top five from each table, say), wrap that half in parentheses as a subquery, or give it a derived table alias, and then UNION the results. The same rule covers the sort key: ORDER BY must name a column of the combined result, by the first query's column name or by position number, not by a column that exists only in the second half. MySQL and PostgreSQL both accept the parenthesised form; SQL Server needs the per-half limit inside a derived table with TOP.

   (SELECT name, score FROM ClassA ORDER BY score DESC LIMIT 5)
   UNION ALL
   (SELECT name, score FROM ClassB ORDER BY score DESC LIMIT 5)
   ORDER BY score DESC;   -- sorts the combined ten rows

5. 5.What is the difference between UNION and JOIN?

   They combine in different directions. UNION appends rows: two queries with the same shape become one taller result, and there is no matching condition. JOIN appends columns: rows from two tables are paired up by a condition, giving a wider result. So "one list of every customer and supplier name" is a UNION; "each order with its customer's name" is a JOIN. Candidates confuse them when both tables describe similar things — current and former employees are a UNION, employees and their departments are a JOIN. A quick test: if the two sources are the same kind of thing, stack them; if one refers to the other, join them.

6. 6.How does UNION handle NULLs and mixed data types?

   For duplicate removal, UNION treats two NULLs as equal — like GROUP BY and DISTINCT, and unlike the = operator — so two rows that are NULL in the same column collapse into one. For types, each output column takes a type that can hold both inputs: an INT column unioned with a DECIMAL becomes DECIMAL, and a VARCHAR(10) with a VARCHAR(50) becomes VARCHAR(50). Mixing a number with text is where dialects diverge — MySQL converts silently to a string, PostgreSQL raises an error asking you to cast — so cast explicitly when the halves differ. And a NULL literal in the first SELECT has no type of its own; the column's type comes from the second half, which occasionally surprises people who put the placeholder query first.

7. 7.How do set operations interact with duplicates in the inputs?

   UNION, INTERSECT and EXCEPT are true set operations: they treat each input as a set, so duplicates inside one input are collapsed as well as duplicates across the two. Rows Pune, Pune from one query and Pune from another give a single Pune under UNION and a single Pune under INTERSECT. The ALL variants switch to bag semantics — UNION ALL keeps all three, and INTERSECT ALL keeps as many copies as appear in both inputs (here, one). This is also why UNION can shrink a query's result unexpectedly: if a report needs one row per order and two orders happen to be identical in every selected column, UNION returns one of them. When in doubt, include a key column so rows can never be identical.

8. 8.What is the precedence when several set operations are mixed?

   In standard SQL — and in PostgreSQL, SQL Server and MySQL 8.0.31+ — INTERSECT binds tighter than UNION and EXCEPT, which are evaluated left to right at the same level, so A UNION B INTERSECT C means A UNION (B INTERSECT C). Oracle evaluates all three left to right unless you add parentheses (its documentation warns that INTERSECT may be given higher precedence in a future release), which means the identical query can return different rows on different databases. The professional answer is never to rely on the default: parenthesise every mixed expression so the intent is on the page, and if a query has more than two set operations, consider a CTE per step so each intermediate result has a name.

How the diagnostic asks it

One question from the SQL bank, exactly as a sitting would show it. The bank has 4 on set operations and 60 across SQL.