Set theory for SQL Joins

Joining Data in SQL

Maham Faisal Khan

Senior Content Developer, DataCamp

Venn diagrams and set theory

A figure showing three Venn diagrams: UNION, INTERSECT, and EXCEPT. The UNION diagram has two overlapping circles, fully colored in green. The INTERSECT diagrams has two overlapping circles, but only the overlapping part is colored in green. The EXCEPT diagram only has the part of the left circle that has nothing in common with the right circle colored in green.

Venn diagrams and set theory

An image that shows three Venn diagrams, with two shaded out. Only the diagram with UNION is in focus. The UNION diagram has two overlapping circles, fully colored in green.

UNION diagram

UNION takes two tables as input, and returns all records from both tables

A diagram showing two tables, left and right. On the right side of the diagram, there is an illustration of what happens when a UNION operation is performed on the two tables.

UNION ALL diagram

UNION ALL takes two tables and returns all records from both tables, including duplicates

A diagram showing two tables, left and right. On the right side of the diagram, there is an illustration of what happens when a UNION ALL operation is performed on the two tables.

UNION and UNION ALL syntax

UNION syntax

SELECT *
FROM left_table
UNION
SELECT *
FROM right_table;

UNION ALL syntax

SELECT *
FROM left_table
UNION ALL
SELECT *
FROM right_table;

UNION and UNION ALL syntax

A diagram showing two tables, left and right. On the right side of the diagram, there is an illustration of what happens when a UNION operation is performed on the two tables. Arrows call out that the left and right table require the same number of columns of the same field types.

UNION and UNION ALL syntax

To the monarchs table

SELECT *
FROM monarchs;

| country        | continent   | monarch                 |
|----------------|-------------|-------------------------|
| Brunei         | Asia        | Hassanal Bolkiah        |
| Oman           | Asia        | Haitham bin Tarik       |
| Norway         | Europe      | Harald V                |
| United Kingdom | Europe      | Charles III             |

Prime ministers, meet the monarchs

SELECT monarch AS leader, country
FROM monarchs
UNION
SELECT prime_minister, country
FROM prime_ministers
ORDER BY country, leader
LIMIT 10;

After the UNION

|-------------------------|----------------|
| leader                  | country        |
|-------------------------|----------------|
| Anthony Albanese        | Australia      |
| Hassanal Bolkiah        | Brunei         |
| Mostafa Madbouly        | Egypt          |
| Narendra Modi           | India          |
| Christopher Luxon       | New Zealand    |
| Harald V                | Norway         |
| Jonas Gahr Støre        | Norway         |
| Haitham bin Tarik       | Oman           |
| Shehbaz Sharif          | Pakistan       |
| Luís Montenegro         | Portugal       |
|-------------------------|----------------|

UNION ALL with the leaders

SELECT monarch AS leader, country
FROM monarchs
UNION ALL
SELECT prime_minister, country
FROM prime_ministers
ORDER BY leader, country
LIMIT 10;

UNION ALL result

| leader                  | country        |
|-------------------------|----------------|
| Anthony Albanese        | Australia      |
| Hassanal Bolkiah        | Brunei         |
| Hassanal Bolkiah        | Brunei         |
| Mostafa Madbouly        | Egypt          |
| Narendra Modi           | India          |
| Christopher Luxon       | New Zealand    |
| Harald V                | Norway         |
| Jonas Gahr Støre        | Norway         |
| Haitham bin Tarik       | Oman           |
| Haitham bin Tarik       | Oman           |

Let's practice!

Joining Data in SQL