Joins in SQL Tutorial

SQL Server is a relational database management system. One of the key principles of the relational database is that data is stored across multiple tables.

We will need to be able to join tables together in order to extract the data we need. We use primary and foreign keys to join tables.

Primary Key

A primary key is a column that is used to uniquely identify each row in a table. This uniqueness can be achieved by using a sequential integer as an identity column. Or sometimes, existing columns naturally contain unique values, and they can be used.

In the below example, we can see the first few rows from the artist table. It has two columns, artist_id, and name. The artist_id column acts as a primary key for this table, it is an integer column, and each value is different.

+-----------+-------------------+
| artist_id | name              |
|-----------+-------------------|
| 1         | AC/DC             |
| 2         | Accept            |
| 3         | Aerosmith         |
| 4         | Alanis Morissette |
| 5         | Alice In Chains   |
+-----------+-------------------+

Now let's look at the album table. Can you spot the primary key? Yes, it's the album_id column. It's common for the primary key to be named "table-name underscore id". But, you'll have noticed there is also an artist_id column. That also consists of integers and has the same name as the artist_id column in the artist table. What does this mean?

+----------+-------------------------+-----------+
| album_id | title                   | artist_id |  
|----------+-------------------------+-----------|
| 1        | For Those About To Rock | 1         |
| 2        | Balls to the Wall       | 2         |
| 3        | Restless and Wild       | 2         |
| 4        | Let There Be Rock       | 1         |
| 5        | Big Ones                | 3         |
+----------+-------------------------+-----------+

Foreign Keys

Well, from the previous output, it is clear that the artist_id in the artist table acts as a Foreign_key to the album table. The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

• artist table

+-----------+-------------------+
| artist_id | name              |
|-----------+-------------------|
| 1         | AC/DC             |
| 2         | Accept            |
| 3         | Aerosmith         |
| 4         | Alanis Morissette |
| 5         | Alice In Chains   |
+-----------+-------------------+

• album table

+----------+-------------------------+-----------+
| album_id | title                   | artist_id |  
|----------+-------------------------+-----------|
| 1        | For Those About To Rock | 1         |
| 2        | Balls to the Wall       | 2         |
| 3        | Restless and Wild       | 2         |
| 4        | Let There Be Rock       | 1         |
| 5        | Big Ones                | 3         |
+----------+-------------------------+-----------+

This means that when the artist_id in the album table matches the artist_id in the artist table, those rows can be linked as a JOIN.

Joining Album and Artist

Rows 1 and 4 of the artist table have an artist_id of 1. The artist_id 1 in the artist table is AC/DC. So, when we join the two tables together, we return the album details from the album table, and the corresponding artist details from the artist table - joined using the artist_id field, which is common to both tables.

This is known as an inner join.

SELECT
  album_id,
  title,
  album.artist_id,
  name AS artist_name
FROM album
INNER JOIN artist ON artist.artist_id = album.artist_id
WHERE album.artist_id = 1;

+----------+-------------------------+-----------+-------------+
| album_id | title                   | artist_id | artist_name |
|----------+---------------------------------------+-----------|
| 1        | For Those About To Rock | 1         | AC/DC       |
| 4        | Let There Be Rock       | 1         | AC/DC       |
+----------+-------------------------+-----------+-------------|

We joined the album and artist tables by matching the artist_id from the artist table to the artist_id in the album table.

When selecting columns with the same name from different tables, you must fully qualify the column name. Otherwise, SQL Server will not know which table to SELECT the column from.

To fully qualify a column, you have to prefix it with the relevant table name, followed by a period.

Inner Join Syntax

Here's a generic example to remind you of the syntax. When we select the relevant columns from the main table, table_A in this case, then select any additional column we want to join to, which in this case is table_B. Then we specify the join using the keywords INNER JOIN and ON, providing the necessary key columns from both the tables.

SELECT
    table_A.columnX,
    table_A.columnY,
    table_B.columnZ,
FROM table_A
INNER JOIN table_B ON table_B.foreign_key = table_A.primary_key;

Not Using a WHERE Clause

In this example, we don't specify a WHERE clause, so we return ALL combinations of ALL matches between the artist and album tables, again, based on the artist_id column, which is common to both.

SELECT
  album_id,
  title,
  album.artist_id,
  name AS artist_name
FROM album
INNER JOIN artist on artist.artist_id = album.artist_id;

+----------+---------------------------------------+-----------+
| album_id | title                   | artist_id | artist_name |
|----------+---------------------------------------+-----------|
| 1        | For Those About To Rock | 1         | AC/DC       |
| 4        | Let There Be Rock       | 1         | AC/DC       |
| 2        | Balls To The Wall       | 2         | Accept      |
| 3        | Restless and Wild       | 2         | Accept      |
+----------+---------------------------------------+-----------+

Multiple Inner Join

You can also join more than two tables using INNER JOINS. You simply provide an additional INNER JOIN for each combination of tables. In our code below, we join tables A and B, and also B and C.

SELECT
  table_A.columnX,
  table_A.columnY,
  table_B.columnZ table_C columnW
FROM table_A
INNER JOIN table_B ON table_B.foreign_key = table_A.primary_key
INNER JOIN table_C ON table_C.foreign_key = table_B.primary_key;

To learn more about joining tables, please see this video from our course Introduction to SQL Server.

This content is taken from DataCamp’s Introduction to SQL Server course by John MacKintosh.