Part 4.1Basics of joins

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

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.

Code preview
Feedback 👍
Comments are sent via email to me.