UNION and UNION ALL

 `UNION` and `UNION ALL` are both set operations in SQL used to combine the result sets of two or more SELECT queries. However, they differ in terms of their behavior with respect to duplicate rows.

UNION

1. Purpose
   - `UNION` is used to combine the result sets of two or more SELECT statements, and it eliminates duplicate rows from the final result set.

2. Duplicate Removal

   - Duplicate rows are removed from the combined result set. If a row appears in multiple SELECT queries, it will only appear once in the final result.

3. Performance
   - `UNION` may have a slight performance overhead due to the need to check and remove duplicate rows.

4. Syntax

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

UNION ALL

1. Purpose

   - `UNION ALL` is used to combine the result sets of two or more SELECT statements, and it includes all rows from each SELECT, including duplicates.

2. Duplicate Removal

   - `UNION ALL` does not remove duplicate rows. If a row appears in multiple SELECT queries, it will appear as many times in the final result as it appears in the individual SELECTs.

3. Performance

   - `UNION ALL` is generally faster than `UNION` because it doesn't need to perform the additional step of checking for and removing duplicate rows.

4. Syntax

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

Summary

- Use `UNION` when you want to combine result sets and remove duplicate rows from the final result.

- Use `UNION ALL` when you want to combine result sets and include all rows from each SELECT, regardless of duplicates.

- `UNION` is typically used when duplicate rows need to be eliminated, and `UNION ALL` is used when duplicate rows should be retained or when performance is a primary consideration.

Example

-- Using UNION to combine result sets and remove duplicates
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

-- Using UNION ALL to combine result sets and include duplicates
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;