Joining tables

Introduction to SQL Server

John MacKintosh

Instructor

Relational Databases

Primary Keys

Primary keys: Uniquely identify each row in a table

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

Primary key: artist_id

+----------+-------------------------+-----------+
| 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         |
+----------+-------------------------+-----------+

Primary key: album_id
What about artist_id?

Foreign keys

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         |
+----------+-------------------------+-----------+

artist_id: Foreign key to artist

Joining album and artist

artist table

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

AC/DC has artist_id = 1

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         |
+----------+-------------------------+-----------+

Rows 1 and 4 have artist_id = 1

Joining album and artist

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

Return album details from album table
Return corresponding artist details from artist table
Joined using artist_id column

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       |
+----------+-------------------------+-----------+-------------|

INNER JOIN syntax

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

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      |
+----------+---------------------------------------+-----------+

Returns all combinations of all matches between album and artist

Multiple INNER JOINS

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;

Let's join some tables!

Introduction to SQL Server