Joins are a very important part of a relational database.
Assume the following two tables exists:
| order_id | customer_id |
|---|---|
| 01 | 01 |
| 02 | 05 |
| 03 | 05 |
| 04 | 01 |
| 05 | 02 |
| customer_id | full_name | city |
|---|---|---|
| 01 | John Doe | Edinburgh |
| 02 | John Smith | London |
| 03 | Adam Roosevelt | London |
| 04 | Frank Adams | New York |
| 05 | Teddy Adams | New York |
It is clear there is a relationship between these two tables. The first table stores orders and the second stores details about customers.
This prevents unneccesary duplication of data since each order does not need to save the full_name and city values of the user for each
order.
However, they are unlinked, meaning that the values will some how need to be joined. This is where SQL's join command comes in.
Venn Diagrams
Before proceeding to how this works, it is important to understand Venn Diagrams. This page of this tutorial will describe them briefly.
The following Venn Diagram will be used in the examples below.
Union
The union (∪) of two sets (or in this case tables) is everything within both tables. In the example above, it is denoted by the peach colour.
Intersection
The intersection (∩) of two sets is everything that both sets have in common. It is where two sets meet.
One example, could be a list of colours that have red and blue in them. The lists looks like:
Both red and blue
- #f06
- #a0f
- #242
Contain red but do not contain blue
- #f00
- #f60
- #fa0
Contain blue but do not contain red
- #00f
- #06f
- #009
Notice that many of the colours here include a green element, since this will not change the result of the diagram.
In this case with the previous Venn Diagram, the second list (Contain red but do not contain blue) would be Table1 and the third list (Contain blue but do not contain red) would be Table 2.
The first list (Both red and blue) would be the intersection, since it contains colours with both red and blue. In the Venn Diagram example, this would be the darker blue section which contains both Table1 and Table2.
Joins
A join performs one of the operations such as selecting everything in Table1 or everything in Table2 or everything in the union or intersection of these tables. The next few pages of this tutorial will discuss these joins.
