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 & 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 & name. The artist_id column acts as a primary key for this table, it is an integer column, & each value is different.

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 & has the same name as the artist_id column in the artist table. What does this mean?

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
  • album table

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 & 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.

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.

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.

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 & B, and also B & C.


Data Scientist & Machine Learning Engineer