Join vs UNION

Hook: Interviewers love this question because one tiny word change can turn a correct query into a wrong report, a slow query, or a huge row explosion.

Question: What is the difference between JOIN and UNION in SQL?

Answer: JOIN combines rows from two tables side by side using a matching condition, so the result has columns from both tables. UNION stacks the results of two separate SELECT queries on top of each other, so the result has the same columns but more rows. If I want to enrich data, I use JOIN; if I want to merge two similar lists, I use UNION.

Interview-Ready Answer: I use JOIN when I need to combine columns from related tables, like orders with customers, and I use UNION when I need to stack two result sets with the same shape, like active users from two regions. A key detail is that UNION removes duplicates by default, while UNION ALL keeps them and is usually faster. So, join is about matching rows; union is about appending result sets.

Detailed Explanation:

What each one really means

Think of SQL in two different modes:

1. JOIN is a row-matching operation. A JOIN says: find rows that relate to each other through a key or condition, then place their columns together in one output row.
2. UNION is a set operation. A set operation works on whole result sets, not on table relationships. It says: take the output of one query, then append the output of another query that has the same column layout.
3. In most dialects, plain JOIN means INNER JOIN, which keeps only matching rows. Outer joins like LEFT JOIN keep unmatched rows too.
4. UNION means UNION DISTINCT in standard SQL, so duplicates are removed unless you write UNION ALL.

How the engine usually executes them

1. For a JOIN, the database reads rows from both sides and checks the ON condition. The optimizer chooses a join algorithm such as nested loop (check each row against another set), hash join (build a hash table for fast lookups), or merge join (walk two sorted inputs together).
2. If the join condition matches many rows on both sides, the output can grow fast. That is why a many-to-many join can create a huge result even when both inputs are small.
3. For UNION ALL, the engine usually just concatenates the two streams. It does not need to compare rows for duplicates, so it can often start returning rows quickly.
4. For UNION, the engine must also remove duplicates from the combined output. It typically does that with a sort or a hash-based distinct step.
5. After a UNION or UNION ALL, the ORDER BY belongs at the end of the whole statement. Sorting inside one branch does not guarantee final order.

When to use each one

• Use JOIN when you need extra columns from another table, such as customer name, department name, or product price.
• Use UNION when you have two queries that return the same kind of rows, such as active users from region A and active users from region B.
• Use UNION ALL when you want every row preserved, or when duplicates are acceptable and speed matters.
• Do not use JOIN to combine unrelated lists, and do not use UNION when you really need columns from two tables in the same row.

JOIN vs UNION at a glance

Performance and complexity

The exact cost depends on the plan, but the mental model is useful:

• A good JOIN on indexed keys is often close to linear for the matched rows, especially with hash or merge joins. A bad nested-loop join without indexes can behave like O(n*m), which gets expensive very fast.
• UNION ALL is usually near O(n + m) because it just appends rows.
• UNION needs duplicate removal, so it is usually more expensive than UNION ALL. With sorting, it can feel like O((n+m) log(n+m)); with hashing, the average case is closer to linear but uses memory.
• On large data, UNION can spill to disk if the dedupe step does not fit in memory. That can turn a query that should take seconds into one that takes minutes.

Important edge cases

1. A JOIN does not guarantee one output row per input row. If one customer has 5 orders, joining customers to orders returns 5 rows for that customer.
2. A missing join condition can create a cross join effect, meaning every row matches every other row, which can explode row counts.
3. UNION requires the same number of columns in each branch, and the data types must be compatible. The column names in the final result usually come from the first SELECT.
4. UNION compares the combined results for duplicates, not each branch separately.
5. NULL handling matters: outer joins can produce NULL values for missing matches, but UNION simply treats rows as rows; it does not fill in missing columns.

Memory rule: JOIN stitches columns together; UNION stacks rows into one taller pile. If you remember that picture, you will almost never confuse them in an interview.

Real-World Story: A retail analytics team had separate web_signups and mobile_signups tables, plus a customers table with loyalty tier. To build one daily report, they should have used UNION ALL for the two signup streams, then JOIN the merged result to customers for enrichment. A developer mixed that up and used JOIN between the signup tables on email, which silently dropped users who signed up in only one channel and duplicated users who appeared in both.

The symptom was ugly: the dashboard underreported new signups by about 20%, marketing thought a campaign failed, and the database spent extra time sorting and joining far more rows than expected. In the query logs, the team saw big row estimates and slow execution around the reporting job. The fix was simple: stack the two event sources with UNION ALL, dedupe later only if needed, and reserve JOIN for the customer enrichment step.

Follow-up & Tricky Questions:

• What is the difference between INNER JOIN and LEFT JOIN? INNER JOIN keeps only matching rows, while LEFT JOIN keeps every row from the left table and fills missing right-side columns with NULL.
• When should I use UNION ALL instead of UNION? Use UNION ALL whenever duplicates are acceptable or you plan to dedupe later, because it avoids the extra distinct step and is usually faster.
• Can I UNION queries with different column names? Yes. The column names can differ, but the number of columns and their types must be compatible; the final output names usually come from the first SELECT.
• How can a join create too many rows? If the join key is not unique on one or both sides, one row can match many rows, so the output multiplies instead of staying 1-to-1.
• Does ORDER BY inside each branch control the final UNION order? No. Only the final ORDER BY after the full UNION or UNION ALL controls the returned order.
• What join algorithm might the optimizer pick? Common choices are nested loop, hash join, and merge join. The optimizer picks based on indexes, statistics, and estimated row counts.

• Gotcha: Does UNION sort rows? No. It removes duplicates, but without a final ORDER BY the output order is not guaranteed.
• Gotcha: Does JOIN remove duplicates automatically? No. A join can duplicate rows if the match is one-to-many or many-to-many.
• Gotcha: Is plain JOIN the same as CROSS JOIN? No. Plain JOIN usually means INNER JOIN. A missing ON clause, however, can accidentally behave like a cross join and explode the row count.

Common Mistakes:

• Mistake: Using UNION when you actually need related columns in one row. Fix: Use JOIN to combine tables side by side.
• Mistake: Using JOIN when you really want to append two similar lists. Fix: Use UNION ALL or UNION depending on whether duplicates should remain.
• Mistake: Forgetting that UNION requires the same number of columns. Fix: Align the SELECT lists and cast types when needed.
• Mistake: Assuming joins never duplicate rows. Fix: Check key uniqueness and remember that one-to-many joins multiply rows.

Memory Hook: JOIN stitches columns; UNION stacks rows. If you can picture a zipper versus a stack of trays, you can answer this under pressure.

Cheat Sheet:

• JOIN = match rows and bring columns together.
• UNION = append result sets with the same shape.
• UNION removes duplicates by default; UNION ALL keeps them.
• JOIN can multiply rows if the relationship is one-to-many.
• UNION needs the same column count and compatible types.
• Final ORDER BY goes at the end, not in the middle.

Practice Tasks:

• Write a LEFT JOIN that shows every customer, even customers with no orders.
• Combine two monthly sales tables with UNION ALL, then compare the row count to UNION.
• Create a one-to-many join and observe how one parent row becomes several output rows.