Union & Union All

UNION: The `UNION` operator in SQL is used to combine the results of two or more SELECT statements into a single result set. It removes duplicate rows from the result set, so each row appears only once.

UNION ALL: On the other hand, the `UNION ALL` operator also combines the results of two or more SELECT statements into a single result set, but it includes all rows from all SELECT statements, including duplicates.

 

 

 

 

 

To illustrate:

Let's consider two tables, A and B.

Table A:

id | name
---------
1  | Alice
2  | Bob
3  | Charlie

Table B:

id | name
---------
2  | Bob
3  | Charlie
4  | David

When we combine these tables using `UNION` and `UNION ALL`:

- With `UNION`, the result would be:

SELECT * FROM A
UNION
SELECT * FROM B;

id | name
---------
1  | Alice
2  | Bob
3  | Charlie
4  | David

Bob and Charlie appear only once in the result because duplicates are removed.

 

- With `UNION ALL`, the result would be:

SELECT * FROM A
UNION ALL
SELECT * FROM B;

id | name
---------
1  | Alice
2  | Bob
3  | Charlie
2  | Bob
3  | Charlie
4  | David

Bob and Charlie appear twice in the result because duplicates are retained.