Detailed explanation
UNION and UNION ALL are SQL set operators that combine query results vertically: rows from one SELECT are combined with rows from another. They do not match related rows horizontally like JOIN. Each SELECT must return the same number of columns, and columns in corresponding positions must have compatible data types; their names need not match. Type-conversion rules vary by database, so explicit casts can prevent errors or unexpected conversions. The combined result normally takes its column names from the first SELECT.
The key difference is duplicate handling. UNION returns distinct rows across the entire combined result, including removing duplicates already present within either input. UNION ALL preserves every occurrence. For example, SELECT city FROM customers UNION SELECT city FROM suppliers returns each city value once. Replacing UNION with UNION ALL retains repeated cities from both tables. If customers produces Delhi, Pune, Pune and suppliers produces Pune, Chennai, UNION returns three distinct city values, while UNION ALL returns five rows. Neither query guarantees their display order.
Duplicate detection considers the complete selected row, not just one column. With SELECT city, state, two rows are duplicates only when both corresponding values match under the database's comparison rules. For set-operation duplicate elimination, NULL values in corresponding positions are treated as equal, unlike ordinary comparisons using =. Under the hood, UNION typically requires a sort-based or hash-based deduplication step. That step consumes CPU and memory and may spill to disk for large results. UNION ALL avoids deduplication, so it is generally faster, although actual execution plans and timings depend on the database, indexes, and data.
Choose the operator according to the required result, not performance alone. Use UNION when you need a unique combined list, such as cities served by either customers or suppliers. Use UNION ALL when repeated rows are meaningful, such as combining transaction records, or when uniqueness is already guaranteed across both inputs. Interviewers expect you to explain compatibility requirements, whole-row duplicate removal, and the performance trade-off. To guarantee output order, apply a final ORDER BY to the combined result; UNION's possible internal sorting is not an ordering guarantee.
Key points to remember
- Both operators stack results vertically and require equal column counts with compatible types in corresponding positions.
- UNION removes duplicate complete rows, including duplicates within either input.
- UNION ALL keeps all rows, so its row count equals the sum of the input row counts.
- UNION typically adds sorting or hashing for deduplication; UNION ALL avoids that work.
- Neither operator guarantees row order; use a final ORDER BY when ordering matters.
Common mistakes candidates make
- Saying UNION removes duplicates based on just the first selected column rather than the complete row.
- Choosing UNION automatically and accidentally removing meaningful repeated records.
- Assuming UNION ALL returns the first query's rows before the second query's rows in a guaranteed order.
Likely follow-up questions
How is UNION different from JOIN?
UNION stacks compatible result rows vertically. JOIN combines columns from table rows according to a join condition, potentially producing multiple matches.
Can UNION ALL replace UNION when both tables have primary keys?
Not automatically: primary keys guarantee uniqueness within each table, not across both query results. Also, selecting only non-key columns can introduce duplicates even within a single input.
How do you sort the combined city results?
Use SELECT city FROM customers UNION ALL SELECT city FROM suppliers ORDER BY city. The final ORDER BY sorts the combined result, not just the supplier rows.
Interview tip: Start with duplicate handling, illustrate it with a repeated city, then explain compatibility requirements and the deduplication cost.