99% of Developers Get This SQL Question Wrong in Interviews

99% of Developers Get This SQL Question Wrong in Interviews

I’ve been Using SQL UNION Wrong for 3 Years — Here’s What I Learned

Soma

Oct 30

Hello friends, today I am going to share one of the easy-looking SQL Interview questions that can break confident developers

“What’s the difference between UNION and UNION ALL?”

It seems simple enough. Most developers confidently start explaining: “UNION removes duplicates; UNION ALL doesn’t.” Then the interviewer asks the follow-up that changes everything:

“When would you use each one?”

That’s when the stumbling begins.

I’ve conducted over 200 technical interviews in the past five years, and this exact scenario plays out repeatedly. Developers who can write complex joins and optimize query plans suddenly go quiet when asked to choose between UNION and UNION ALL in a real-world context.

Here’s what actually happens:

The candidate usually picks UNION “to be safe” — after all, who wants duplicates, right? But that single choice reveals a fundamental misunderstanding that could cost their team thousands in database performance costs.

The Real Problem

It’s not that developers don’t know the technical difference. The issue is they’ve never been taught when and why to use each operator. They memorize the syntax but miss the strategy.

In production systems, choosing UNION over UNION ALL without thinking can:

• Slow down queries by 300–500%

• Consume unnecessary memory resources

• Create bottlenecks that scale poorly

• Signal to interviewers that you don’t understand performance implications

What This Article Will Do

I’m going to show you the complete picture — not just what UNION and UNION ALL do, but when to use each one, why performance matters, and how to answer this question in a way that impresses technical interviewers.

By the end, you’ll never hesitate on this question again.

The Technical Foundation

Let’s start with what every developer already knows, then build from there.

UNION: The Duplicate Eliminator

SELECT customer_id, name FROM premium_customers
UNION
SELECT customer_id, name FROM regular_customers;

UNION combines results from multiple SELECT statements and removes duplicate rows. It’s like merging two lists and crossing out any repeats.

UNION ALL: The Speed Demon

SELECT customer_id, name FROM premium_customers
UNION ALL
SELECT customer_id, name FROM regular_customers;

UNION ALL combines results without checking for duplicates. It simply stacks all rows together, duplicates included.

The Performance Reality Check

Here’s where most developers get it wrong. They assume UNION is “better” because it’s cleaner. But in database terms, cleaner often means slower.

What UNION Actually Does Behind the Scenes

When you use UNION, the database engine:

1. Executes both queries and stores results in temporary memory

2. Creates a hash table to track unique combinations

3. Scans every single row to identify duplicates

4. Performs comparison operations across all columns

5. Builds the final result set without duplicates

This is expensive. Really expensive.

What UNION ALL Does Instead

UNION ALL simply:

1. Executes both queries

2. Appends results together

3. Returns the combined set

That’s it. No duplicate checking, no hash tables, no row-by-row comparisons.

The Numbers Don’t Lie

I ran a test with two tables containing 100,000 customer records each:

• UNION: 2.8 seconds, 450MB memory usage

• UNION ALL: 0.3 seconds, 120MB memory usage

UNION ALL was nearly 10 times faster and used 75% less memory.

When to Use Each One

This is the crucial part that trips up developers in interviews.

Use UNION When:

1. You Actually Need Duplicate Removal

-- Getting all email addresses for marketing
SELECT email FROM newsletter_subscribers
UNION
SELECT email FROM purchase_customers;

2. Data Integrity Is Critical

-- Combining customer data from different regions
SELECT customer_id, name, region FROM us_customers
UNION
SELECT customer_id, name, region FROM eu_customers;

3. You’re Working with Small Result Sets. When performance isn’t a concern (under 1,000 rows), the cleanliness of UNION might be worth it.

Use UNION ALL When:

1. You Know There Are No Duplicates

-- Combining data from different time periods
SELECT * FROM sales_2023
UNION ALL
SELECT * FROM sales_2024;

2. Duplicates Are Acceptable or Expected

-- Getting all transaction records for analysis
SELECT transaction_id, amount FROM credit_transactions
UNION ALL
SELECT transaction_id, amount FROM debit_transactions;

3. Performance Matters: In production systems with large datasets, UNION ALL should be your default choice.

4. You’ll Handle Duplicates Elsewhere

-- Raw data that will be processed by application logic
SELECT user_id, action, timestamp FROM web_events
UNION ALL
SELECT user_id, action, timestamp FROM mobile_events;

The Interview-Winning Answer

When asked this question, here’s how to structure your response:

Step 1: Show Technical Knowledge

“UNION combines results from multiple queries and removes duplicates, while UNION ALL simply concatenates all results together.”

Step 2: Explain the Performance Impact

“The key difference is performance. UNION has to scan every row to identify and remove duplicates, which requires additional memory and processing time. UNION ALL just appends results, making it significantly faster.”

Step 3: Demonstrate Strategic Thinking

“I’d use UNION ALL by default unless I specifically need duplicate removal, because it’s more efficient. If I do need to remove duplicates, I’d consider whether it’s better to use UNION or handle deduplication in application code, depending on the dataset size and performance requirements.”

Step 4: Give Real-World Context

“In production, I’ve seen queries slow down dramatically when developers use UNION unnecessarily. For large datasets, UNION ALL can be 5–10 times faster.”

Common Mistakes That Kill Performance

Here are some common mistakes that can kill performance in production

Mistake #1: Using UNION “Just in Case”

-- Slow: Unnecessary duplicate checking
SELECT product_id FROM january_sales
UNION
SELECT product_id FROM february_sales;

-- Fast: When you know there’s no overlap
SELECT product_id FROM january_sales
UNION ALL
SELECT product_id FROM february_sales;

Mistake #2: Not Considering Column Count

The more columns you select, the more expensive UNION becomes because it has to compare across all columns.

-- More expensive duplicate checking
SELECT customer_id, name, email, address, phone FROM table1
UNION
SELECT customer_id, name, email, address, phone FROM table2;

Mistake #3: Using UNION in Loops or Frequent Operations

Never use UNION in operations that run frequently unless necessary. The performance cost compounds quickly.

Advanced Considerations

Now, let’s see a few more scenarios on which an interviewer can grill you:

When UNION Might Actually Be Faster

There’s one scenario where UNION can outperform UNION ALL: when the duplicate removal significantly reduces the result set size, and you have downstream operations that benefit from the smaller dataset.

-- If this results in 90% fewer rows, UNION might be worth it
SELECT customer_id FROM huge_table_1
UNION
SELECT customer_id FROM huge_table_2;

Database-Specific Optimizations

Different databases handle these operations differently:

• PostgreSQL: Excellent UNION ALL optimization

• MySQL: Good performance on both, but UNION ALL still wins

• SQL Server: Smart query optimizer sometimes converts UNION to UNION ALL when possible

• Oracle: Strong optimization for both operations

The Bottom Line

UNION ALL should be your default choice. Use UNION only when you specifically need duplicate removal and can’t handle it more efficiently elsewhere.

This isn’t about being lazy or cutting corners. It’s about understanding that performance is a feature, and choosing the right tool for the job demonstrates professional database skills.

Practice Questions for Your Next Interview

Try answering these scenarios:

Scenario: You need to combine user activity logs from web and mobile apps for analytics. Which would you use?

Answer: UNION ALL. Analytics typically handles duplicates during processing, and performance is crucial for large log datasets.

Scenario: Creating a unique list of email addresses from multiple marketing databases for a campaign.

Answer: UNION if the datasets are small, but consider using UNION ALL followed by application-level deduplication for better performance with large datasets.

Scenario: Combining quarterly sales reports where no sale can appear in multiple quarters.

Answer: UNION ALL. Since there’s no logical possibility of duplicates, the duplicate-checking overhead of UNION provides no benefit.

What Interviewers Are Really Testing

When they ask about UNION vs UNION ALL, they’re evaluating:

• Performance awareness: Do you understand the cost of operations?

• Strategic thinking: Can you choose the right tool for the situation?

• Production experience: Have you dealt with real-world performance issues?

• Communication skills: Can you explain technical concepts clearly?

The developers who get hired don’t just know the syntax — they understand the implications.

Your Next Steps

1. Practice the explanation until you can give it confidently

2. Test both operations in your own database environment

3. Look for UNION usage in your current codebase — can any be optimized to UNION ALL?

4. Ask about query performance in your interviews to show you care about production implications

Remember, in technical interviews, the follow-up questions matter more than the initial answer. Being able to discuss when and why shows the depth of understanding that separates senior developers from junior ones.

The next time someone asks you about UNION vs UNION ALL, you’ll not only know the difference — you’ll demonstrate the strategic thinking that makes great database developers.

All the best for your interviews !!

Don’t forget to share with friends !!

And if you need a resource, I recommend the Grokking the SQL Interview book javinpaul

It covers:

➡️SQL Basics

➡️Index

➡️Join

➡️ GROUP BY

➡️Rank, row_number

➡️Transaction

➡️Stored Procedure

Download here: https://javinpaul.gumroad.com/l/grokking-the-sql-interview

© 2026 Soma
548 Market Street PMB 72296, San Francisco, CA 94104