Until now, our databases seemed like a collection of isolated islands. We had a Customers table on one side and an Orders table on the other.
For now, that’s fine. But the relational model makes sense precisely when we connect those islands to compose more complex results.
The JOIN clause is the mechanism that allows us to combine rows from two or more tables based on a related column between them.
It is, without a doubt, the most important and frequent operation in SQL, and one of the hardest to visualize and learn. So we’ll go step by step to understand them well, between this and the next article.
JOIN Syntax
Actually, the general syntax of a JOIN is always the same,
FROM Left_Table AliasA
[TYPE] JOIN Right_Table AliasB
ON AliasA.Column = AliasB.Column
- Left Table (Left): It’s the first table you write.
- [TYPE]: This is where you choose the type of JOIN.
- Right Table (Right): It’s the table you want to attach.
- ON …: Defines which columns must match to consider two rows a pair.
For SQL, the reading order matters. The table you put first, (in the FROM), is the one we will consider “on the left”.
The difference between one type of JOIN and another will be
- The TYPE we put (you saw that coming, right?)
- The value association we make in the ON…
Types of JOIN
The main types of JOIN we are going to see are the following
| Type | Description | Generates NULLs? |
|---|---|---|
| INNER | Only matches | No |
| LEFT | Everything from the left + matches | Yes (on the right) |
| RIGHT | Everything from the right + matches | Yes (on the left) |
| FULL | Everything from both sides | Yes (on both sides) |
The INNER JOIN is the most common type of JOIN.
It returns only the rows that have matches in both tables. If there are no matches, they are not included in the result.
The LEFT JOIN returns all rows from the left table (the first table mentioned in the query) and the matching rows from the right table.
If there are no matches in the right table, NULL values are returned for the columns of that table.
The RIGHT JOIN is similar to LEFT JOIN, but returns all rows from the right table and the matching rows from the left table.
If there are no matches in the left table, NULL values are returned for the columns of that table.
The FULL JOIN returns all rows from both tables, combining the rows that have matches and returning NULL for the columns of the table that has no matches.
We will look at them in more detail in the next article
Visualizing JOINS
To understand what SQL Server does when we cross tables, it’s best to visualize it as Venn diagrams (mathematical sets).
Depending on which part of the circles we color (the intersection, only the left, everything…), we will get different data.
