SQL Interview Questions

UNION and UNION ALL are SQL operators used to combine the results of two or more SELECT statements. While they serve a similar purpose, there are key differences in how they handle duplicate rows.

UNION

The UNION operator combines the result sets of two or more SELECT statements and removes duplicate rows from the final result set. It performs an implicit DISTINCT operation to eliminate any duplicate records.

• Syntax:

  SELECT column1, column2, ...
  FROM table1
  UNION
  SELECT column1, column2, ...
  FROM table2;

• Example:

  SELECT name FROM employees
  UNION
  SELECT name FROM customers;

• Result:

  • Combines unique names from both the employees and customers tables.
  • If the name "John Doe" appears in both tables, it will appear only once in the final result set.

UNION ALL

The UNION ALL operator combines the result sets of two or more SELECT statements but does not remove duplicate rows. It includes all records, including duplicates, in the final result set.

• Syntax:

  SELECT column1, column2, ...
  FROM table1
  UNION ALL
  SELECT column1, column2, ...
  FROM table2;

• Example:

  SELECT name FROM employees
  UNION ALL
  SELECT name FROM customers;

• Result:

  • Combines all names from both the employees and customers tables.
  • If the name "John Doe" appears in both tables, it will appear twice in the final result set.

Key Differences

1. Handling Duplicates:

   • UNION: Removes duplicate rows, ensuring that each row in the final result set is unique.
   • UNION ALL: Retains duplicate rows, including all records from each SELECT statement.

2. Performance:

   • UNION: Typically slower than UNION ALL because it requires additional processing to remove duplicates.
   • UNION ALL: Generally faster since it does not perform the duplicate elimination step.

3. Use Cases:

   • UNION: Use when you need a unique set of results without duplicates.
   • UNION ALL: Use when you want to retain all duplicates or when you are certain there are no duplicates and you need better performance.

Practical Examples

Example 1: UNION

Suppose you have two tables, sales_2022 and sales_2023, and you want a list of unique products sold in both years.

SELECT product_id FROM sales_2022
UNION
SELECT product_id FROM sales_2023;

• This query returns a list of unique product_ids from both tables, eliminating any duplicates.

Example 2: UNION ALL

Suppose you want a combined list of all products sold in both years, including duplicates.

SELECT product_id FROM sales_2022
UNION ALL
SELECT product_id FROM sales_2023;

• This query returns all product_ids from both tables, including any duplicates.

Conclusion

The choice between UNION and UNION ALL depends on your specific needs:

• Use UNION when you need to eliminate duplicates and ensure that the final result set contains unique rows.
• Use UNION ALL when you need to include all records, including duplicates, and when performance is a consideration.

Understanding these differences helps in writing efficient and correct SQL queries tailored to the requirements of your data analysis or reporting tasks.