Joining tables
I N T RO D U C T I O N TO SQ L SE RV ER
Etibar Vazirov
CS instructor at UFAZ
Referring to John MacKintosh’s slides
Relational Databases
INTRODUCTION TO SQL SERVER
Primary Keys
Primar y keys: Uniquely identif y each row in a table
+ + +
| artist_id | name |
| + |
| 1 | AC/DC |
| 2 | Accept |
| 3 | Aerosmith |
| 4 | Alanis Morissette |
| 5 | Alice In Chains |
+ + +
Primar y key: artist_id
INTRODUCTION TO SQL SERVER
+ + + +
| 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 |
+ + + +
Primar y key: album_id
What abo ut artist_id ?
INTRODUCTION TO SQL SERVER
Foreign keys
artist table album table
+ + + + + + +
| artist_id | name | | album_id | title | artist_id |
| + | | + + |
| 1 | AC/DC | | 1 | For Those About To Rock | 1 |
| 2 | Accept | | 2 | Balls to the Wall | 2 |
| 3 | Aerosmith | | 3 | Restless and Wild | 2 |
| 4 | Alanis Morissette | | 4 | Let There Be Rock | 1 |
| 5 | Alice In Chains | | 5 | Big Ones | 3 |
+ + + + + + +
artist_id : Foreign key to artist
INTRODUCTION TO SQL SERVER
Joining album and artist
artist table album table
+ + + + + + +
| artist_id | name | | album_id | title | artist_id |
| + | | + + |
| 1 | AC/DC | | 1 | For Those About To Rock | 1 |
| 2 | Accept | | 2 | Balls to the Wall | 2 |
| 3 | Aerosmith | | 3 | Restless and Wild | 2 |
| 4 | Alanis Morissette | | 4 | Let There Be Rock | 1 |
| 5 | Alice In Chains | | 5 | Big Ones | 3 |
+ + + + + + +
AC / DC has artist_id = 1 Rows 1 and 4 hav e artist_id = 1
INTRODUCTION TO SQL SERVER
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 |
+ + + + |
Ret urn alb um details from album table
Return corresponding artist details from artist table
Joined using artist_id col u mn
INTRODUCTION TO SQL SERVER
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 |
+ + + + |
INTRODUCTION TO SQL SERVER
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;
INTRODUCTION TO SQL SERVER
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 |
+ + + +
Ret urns all combinat ions of all matches bet ween album and artist
INTRODUCTION TO SQL SERVER
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;
INTRODUCTION TO SQL SERVER
Let's join some
tables!
I N T RO D U C T I O N TO SQ L SERV ER
Mix n match - LEFT
& RIGHT joins
I N T RO D U C T I O N TO SQ L SERV ER
Etibar
John Vazirov
MacKintosh
CS instructor at UFAZ
Instr u ctor
Referring to John MacKintosh’s slides
The rationale for LEFT and RIGHT joins
Wh y do we need LEFT and RIGHT joins?
One table ma y not ha v e an e x act match in another :
C u stomer order histor y for marketing campaign
Prod u ct list and returns histor y
Patients admitted b u t not y et discharged
INTRODUCTION TO SQL SERVER
The rationale for LEFT and RIGHT joins
Wh y do we need LEFT and RIGHT joins?
One table ma y not ha v e an e x act match in another :
C u stomer order histor y for marketing campaign
Prod u ct list and returns histor y
Patients admitted but not yet discharged
INTRODUCTION TO SQL SERVER
Admissions table Discharges table
+------------+----------+ +------------+------------+
| Patient_ID | Admitted | | Patient_ID | Discharged |
|------------+----------| |------------+------------|
| 1 | 1 | | 1 | 1 |
| 2 | 1 | | 3 | 1 |
| 3 | 1 | | 4 | 1 |
| 4 | 1 | +------------+------------+
| 5 | 1 |
+ + +
INNER JOIN: LEFT JOIN:
+ + + | + + + |
| Patient_ID | Admitted | Discharged | | Patient_ID | Admitted | Discharged |
| + | | | + | |
| 1 | 1 | 1 | | 1 | 1 | 1 |
| 3 | 1 | 1 | | 2 | 1 | NULL |
| 4 | 1 | 1 | | 3 | 1 | 1 |
+------------+----------+------------+ | 4 | 1 | 1 |
| 5 | 1 | NULL |
+ + + +
INTRODUCTION TO SQL SERVER
LEFT JOIN SYNTAX
SELECT
Admitted.Patient_ID,
Admitted,
Discharged
FROM Admitted
LEFT JOIN Discharged ON Discharged.Patient_ID = Admitted.Patient_ID;
INTRODUCTION TO SQL SERVER
SELECT
Admitted.Patient_ID,
Admitted,
Discharged
FROM Admitted
LEFT JOIN Discharged ON Discharged.Patient_ID = Admitted.Patient_ID;
+ + + |
| Patient_ID | Admitted | Discharged |
| + | |
| 1 | 1 | 1 |
| 2 | 1 | NULL |
| 3 | 1 | 1 |
| 4 | 1 | 1 |
| 5 | 1 | NULL |
+ + + +
INTRODUCTION TO SQL SERVER
RIGHT JOIN
SELECT
Admitted.Patient_ID,
Admitted,
Discharged
FROM Discharged
RIGHT JOIN Admitted ON Admitted.Patient_ID = Discharged.Patient_ID;
INTRODUCTION TO SQL SERVER
RIGHT JOIN results
SELECT
Admitted.Patient_ID,
Admitted,
Discharged
FROM Discharged
RIGHT JOIN Admitted ON Admitted.Patient_ID = Discharged.Patient_ID;
+ + + |
| Patient_ID | Admitted | Discharged |
| + | |
| 1 | 1 | 1 |
| 2 | 1 | NULL |
| 3 | 1 | 1 |
| 4 | 1 | 1 |
| 5 | 1 | NULL |
+ + + +
INTRODUCTION TO SQL SERVER
Summary
INNER JOIN : Only ret urns matching ro ws
LEFT JOIN (or RIGHT JOIN ): All rows from the main table plus matches from the joining
table
NULL : Displa yed if no match is fo und
LEFT JOIN and RIGHT JOIN can be interchangeable
INTRODUCTION TO SQL SERVER
INTRODUCTION TO SQL SERVER
Let's Practice!
I N T RO D U C T I O N TO SQ L SE RV ER
UNION & UNION
ALL
I N T RO D U C T I O N TO SQ L SERV ER
Etibar
John Vazirov
MacKintosh
CS instructor at UFAZ
Instr u ctor
Referring to John MacKintosh’s slides
SELECT SELECT
album_id, album_id,
title, title,
artist_id artist_id
FROM album FROM album
WHERE artist_id IN (1, 3) WHERE artist_id IN (1, 4, 5)
+ + + | + + + |
| album_id | title | artist_id | | album_id | title | artist_id |
| + | | | + | |
| 1 | For Those About To Rock | 1 | | 1 | For Those About To Rock | 1 |
| 4 | Let There Be Rock | 1 | | 4 | Let There Be Rock | 1 |
| 5 | Big Ones | 3 | | 6 | Jagged Little Pill | 4 |
+------------+----------+---------------------------+ | 7 | Facelift | 5 |
+ + + +
INTRODUCTION TO SQL SERVER
Combining results
SELECT +------------+-------------------------+------------|
album_id, | album_id | title | artist_id |
title, |------------+-------------------------|------------|
artist_id | 1 | For Those About To Rock | 1 |
FROM album | 4 | Let There Be Rock | 1 |
WHERE artist_id IN (1, 3) | 5 | Big Ones | 3 |
UNION | 6 | Jagged Little Pill | 4 |
SELECT | 7 | Facelift | 5 |
album_id, +------------+-------------------------+------------+
title,
artist_id
D u plicate rows are e x cl uded
FROM album
WHERE artist_id IN (1, 4, 5);
INTRODUCTION TO SQL SERVER
UNION ALL
SELECT +------------+-------------------------+------------|
album_id, | album_id | title | artist_id |
title, |------------+-------------------------|------------|
artist_id | 1 | For Those About To Rock | 1 |
FROM album | 4 | Let There Be Rock | 1 |
WHERE artist_id IN (1, 3) | 5 | Big Ones | 3 |
UNION ALL | 1 | For Those About To Rock | 1 |
SELECT | 4 | Let There Be Rock | 1 |
album_id, | 6 | Jagged Little Pill | 4 |
title, | 7 | Facelift | 5 |
artist_id +------------+-------------------------+------------+
FROM album
WHERE artist_id IN (1, 4, 5);
Includes d u plicate rows
INTRODUCTION TO SQL SERVER
Creating new column names for final results
SELECT +------------+-------------------------+------------|
album_id AS ALBUM_ID, | ALBUM_ID | ALBUM_TITLE | ARTIST_ID |
title AS ALBUM_TITLE, |------------+-------------------------|------------|
artist_id AS ARTIST_ID | 1 | For Those About To Rock | 1 |
FROM album | 4 | Let There Be Rock | 1 |
WHERE artist_id IN(1, 3) | 5 | Big Ones | 3 |
UNION ALL | 1 | For Those About To Rock | 1 |
SELECT | 4 | Let There Be Rock | 1 |
album_id AS ALBUM_ID, | 6 | Jagged Little Pill | 4 |
title AS ALBUM_TITLE, | 7 | Facelift | 5 |
artist_id AS ARTIST_ID +------------+-------------------------+------------+
FROM album
WHERE artist_id IN(1, 4, 5)
INTRODUCTION TO SQL SERVER
Summary
UNION or UNION ALL : Combines queries from the same table or different tables
If combining data from different tables :
Select the same n u mber of col umns in the same order
Col umns should ha v e the same data t y pes
If source tables ha v e different col u mn names
Alias the col u mn names
UNION : Discards d u plicates (slower to run)
UNION ALL : Includes d u plicates (faster to run)
INTRODUCTION TO SQL SERVER
Let's practice!
I N T RO D U C T I O N TO SQ L SE RV ER
