Skip to main content

Joins in SQL Tutorial

This tutorial will explain how to join tables together using primary and foreign keys in an SQL Server.
Oct 2020  · 5 min read

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.

Relational database management system

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.

Intermediate SQL Queries

Beginner
4 hours
1,485,246
Master the basics of querying tables in relational databases such as MySQL, SQL Server, and PostgreSQL.
See DetailsRight Arrow
Start Course

Intermediate SQL Server

Beginner
4 hours
46,030
In this course, you will use T-SQL, the flavor of SQL used in Microsoft's SQL Server for data analysis.

Data Manipulation in SQL

Beginner
4 hours
156,074
Master the complex SQL queries necessary to answer a wide variety of data science questions and prepare robust data sets for analysis in PostgreSQL.
See all coursesRight Arrow
Related
Data Science Concept Vector Image

How to Become a Data Scientist in 8 Steps

Find out everything you need to know about becoming a data scientist, and find out whether it’s the right career for you!
Jose Jorge Rodriguez Salgado's photo

Jose Jorge Rodriguez Salgado

12 min

What is SQL Used For? 7 Top SQL Uses

Discover the uses of SQL in industries and specific jobs. Plus, learn why the SQL language is so versatile and in demand.
Natassha Selvaraj's photo

Natassha Selvaraj

11 min

DC Data in Soccer Infographic.png

How Data Science is Changing Soccer

With the Fifa 2022 World Cup upon us, learn about the most widely used data science use-cases in soccer.
Richie Cotton's photo

Richie Cotton

SQL Window Functions Cheat Sheet

With this SQL Window Functions cheat sheet, you'll have a handy reference guide to the various types of window functions in SQL.
DataCamp Team's photo

DataCamp Team

10 min

COALESCE SQL Function

COALESCE() is one of the handiest functions in SQL. Read this tutorial to learn how to master it.
Travis Tang 's photo

Travis Tang

FORMAT() SQL FUNCTION

FORMAT() is one of the most commonly used functions in SQL. Learn its main applications in this tutorial.
Travis Tang 's photo

Travis Tang

See MoreSee More