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
foreign keys to join tables.
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 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?
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.
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
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
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
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.