UNION ALL with duplicates in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL UNION ALL operator do?

UNION ALL combines the results of two or more SELECT queries and includes all rows, even duplicates.

beginner

How does UNION ALL differ from UNION?

UNION removes duplicate rows from the combined result, while UNION ALL keeps all duplicates.

beginner

Given two tables with overlapping data, which operator would you use to keep all rows including duplicates?

Use UNION ALL to keep all rows including duplicates from both tables.

beginner

Write a simple SQL query using UNION ALL to combine two tables named 'A' and 'B' with the same columns.

SELECT * FROM A UNION ALL SELECT * FROM B;

intermediate

Why might you choose UNION ALL over UNION in a query?

Because UNION ALL is faster since it does not check for duplicates, and you want to keep all rows including duplicates.

What happens to duplicate rows when using UNION ALL?

A Duplicates are kept

B Duplicates are removed

C Only one duplicate is kept

D Query fails if duplicates exist

UNION ALL keeps all rows including duplicates.

Which SQL operator removes duplicate rows when combining results?

A UNION ALL

B INTERSECT

C UNION

D EXCEPT

UNION removes duplicates, UNION ALL does not.

Which operator is generally faster when combining two result sets?

A UNION ALL

B UNION

C INTERSECT

D EXCEPT

UNION ALL is faster because it does not check for duplicates.

If you want to combine two tables and keep all rows including duplicates, which query is correct?

A SELECT * FROM A UNION SELECT * FROM B;

B SELECT * FROM A UNION ALL SELECT * FROM B;

C SELECT * FROM A INTERSECT SELECT * FROM B;

D SELECT * FROM A EXCEPT SELECT * FROM B;

UNION ALL keeps all rows including duplicates.

What is a key reason to use UNION ALL instead of UNION?

A To filter rows

B To remove duplicates

C To sort results automatically

D To improve query speed

UNION ALL is faster because it skips duplicate removal.

Explain the difference between UNION and UNION ALL in SQL.

Think about how duplicates are handled and speed.

Describe a scenario where using UNION ALL is better than UNION.

Consider when duplicates are important and speed matters.