Intro to SQL and Relational Databases: Joins
-
Overview
- Definition
- Inner Joins
- Outer Joins
- Left Joins
- Right Joins
-
Relationships
-
Joins
Viewing the data in one table can be useful, but often we’ll want to see mutiple tables’ data together. To do this we use a JOIN.
The Join clause will have two parts
- The table to join to
- The fields to compare
-
Joins - One to One
Let’s do our first join to see the number of years each teacher has worked here.
SELECT t.first_name, t.last_name, tm.years FROM
zipcode.teachers t
JOIN zipcode.teacher_meta tm
ON t.id = tm.teacher_id;
| first_name | last_name | years | |:———–|:———-|——:| | John | Smith | 4 | | Tabitha | Schultz | 4 | | Jane | Herman | 11 |
-
Joins - One to Many
Next lets add a join to see which teachers wrote each assignment. This time, since some teachers may have written more than one assignment, we may see some duplication in the results.
-
Joins - One to Many
SELECT t.first_name, t.last_name, a.name, a.URL
FROM zipcode.teachers t
JOIN zipcode.assignments a
ON t.id = a.teacher_id;
| first_name | last_name | name | URL |
|---|---|---|---|
| Jane | Herman | Pokemon Lab | https://github.com/Zipcoder/PokemonSqlLab |
| Jane | Herman | Poll App | https://github.com/Zipcoder/CR-MacroLabs-Spring-QuickPollApplication |
| Tabitha | Schultz | Sum or Product | https://github.com/Zipcoder/ZCW-MicroLabs-JavaFundamentals-SumOrProduct |
-
Joins - Many to Many
Lastly lets see which assignments have been given to each student.
For this relationship we must first join the pivot table, then join the destination table.
-
Joins - Many to Many
SELECT s.name, a.name
FROM zipcode.students s
JOIN zipcode.assignment_student a_s
ON a_s.student_id = s.id
JOIN zipcode.assignments a
ON a_s.assignment_id = a.id;
| name | name | |:———————|:————| | Linnell McLanachan | Pokemon Lab | | Lorianna Henrion | Pokemon Lab | | Corena Edgeson | Pokemon Lab | | Archaimbaud Lougheid | Pokemon Lab | | Dun Pettet | Pokemon Lab | | Hymie Parrington | Pokemon Lab | | Linnell McLanachan | Poll App |
-
Joins - Example data sets
For all of the examples to follow, we’ll be assuming a dataset such as follows:
A table called Students with the columns Name and id, and a table called Instruments with the columns id, part, and student_id.
-
Joins - Example data sets (continued)
They each contain the following data, respectively:
Students
| id | name |
|---|---|
| 1 | Alvin |
| 2 | Bertha |
| 3 | Carla |
| 4 | Daniela |
| 5 | Edward |
| 6 | Francis |
-
Joins - Example data sets (continued)
Instruments
| id | part | student_id |
|---|---|---|
| 1 | Bass | 2 |
| 2 | Guitar | 4 |
| 3 | Drums | 3 |
| 4 | Tambourine | 6 |
| 5 | Vocal | null |
-
Joins - INNER JOIN
-
Joins - INNER JOIN
INNER JOIN retrieves records from the Left table and Right table, where the JOIN condition is satisfied in both tables.
INNER JOIN syntax is as follows:
SELECT [columns, etc)
FROM [left table]
INNER JOIN [right table]
ON [matching condition]
-
Joins - INNER JOIN (continued)
For the query
SELECT S.id, S.name,I.part
FROM Students S
INNER JOIN Instruments I
ON S.id = I.student_id
The Result would be:
| id | name | part |
|---|---|---|
| 2 | Bertha | Bass |
| 3 | Carla | Drums |
| 4 | Daniela | Guitar |
| 6 | Francis | Tambourine |
-
Joins - OUTER
OUTER joins return all rows from both the Left Table and the Right Table that satisfy the join’s matching condition, and all the rows that don’t satisfy.
-
Joins - LEFT JOIN
-
Joins - LEFT JOIN
LEFT JOIN returns all the rows from the Left table matched rows that meet the matching condition. THose that don’t match return as null.
SELECT [columns, etc)
FROM [left table]
LEFT JOIN [right table]
ON [matching condition]
-
Joins - LEFT JOIN (continued)
For the query
SELECT S.id, S.name,I.part
FROM Students S
LEFT JOIN Instruments I
ON S.id = I.student_id
The Result would be:
| id | name | part |
|---|---|---|
| 2 | Bertha | Bass |
| 3 | Carla | Drums |
| 4 | Daniela | Guitar |
| 6 | Francis | Tambourine |
| 1 | Alvin | null |
| 5 | Edward | null |
-
Joins - LEFT OUTER JOIN
Behaves similar to LEFT JOIN.
-
Joins - RIGHT JOIN
-
Joins - RIGHT JOIN
Similar to LEFT JOIN, only this time returning all the records from the Right table, with null for all of the Left table data that doesn’t meet the matching condition.
SELECT [columns, etc)
FROM [left table]
RIGHT JOIN [right table]
ON [matching condition]
-
Joins - RIGHT JOIN (continued)
SELECT S.id, S.name,I.part
FROM Students S
RIGHT JOIN Instruments I
ON [matching condition]
The Result would be:
| id | name | part |
|---|---|---|
| 2 | Bertha | Bass |
| 4 | Daniela | Guitar |
| 3 | Carla | Drums |
| 6 | Francis | Tambourine |
| null | null | Vocals |
-
Joins - RIGHT OUTER JOIN
Behaves similar to RIGHT JOIN
-
Joins - OUTER JOIN (revisited)
-
Joins - OUTER JOIN (revisited)
A FULL OUTER JOIN is acheived in MySQL through a UNION of a LEFT OUTER JOIN and a RIGHT OUTER JOIN.
Syntax:
SELECT [columns, etc)
FROM [left table]
LEFT OUTER JOIN [right table]
ON [matching condition]
UNION
SELECT [columns, etc)
FROM [left table]
RIGHT OUTER JOIN [right table]
ON [matching condition]
-
Joins - OUTER JOIN (revisited)
For the query
SELECT s.id, s.name, i.part FROM students s
LEFT JOIN instruments i ON s.id = i.student_id
UNION
SELECT s.id, s.name, i.part FROM students s
RIGHT JOIN instruments i ON s.id = i.student_id
The Result would be:
| id | name | part |
|---|---|---|
| 2 | Bertha | Bass |
| 3 | Carla | Drums |
| 4 | Daniela | Guitar |
| 6 | Francis | Tambourine |
| 1 | Alvin | NULL |
| 5 | Edward | NULL |
| NULL | NULL | Vocal |
-
Joins: in Visual Form
