RELATIONSHIPS
AND JOINS
Finally.
So Far We've Been Working With Very Simple Data
But that's about to change
Real World Data Is Messy and Interrelated
Books
Authors
Customers
Orders
Genres
Reviews
Versions
So Where Do We Start?
Relationship Basics
This is not marriage counseling
- One to One Relationship
- One to Many Relationship
- Many to Many Relationship
Books
Authors
Customers
Orders
Genres
Reviews
Versions
Books
Authors
Customers
Orders
Genres
Reviews
Versions
Books
Authors
Customers
Orders
Genres
Reviews
Versions
1:MANY
The Most Common Relationship
CUSTOMERS & ORDERS
- A customer's first and last name
- A customer's email
- The date of the purchase
- The price of the order
We Want To Store...
first_name | last_name | order_date | amount | |
---|---|---|---|---|
Boy | George | george@gmail.com | '2016/02/10' | 99.99 |
Boy | George | george@gmail.com | '2017/11/11' | 35.50 |
George | Michael | gm@gmail.com | '2014/12/12' | 800.67 |
George | Michael | gm@gmail.com | '2015/01/03' | 12.50 |
David | Bowie | david@gmail.com | NULL | NULL |
Blue | Steele | blue@gmail.com | NULL | NULL |
We Could Use One Table...
NOT A GOOD IDEA
Customers
- customer_id
- first_name
- last_name
Orders
- order_id
- order_date
- amount
- customer_id
customer_id | first_name | last_name | |
---|---|---|---|
1 | Boy | George | george@gmail.com |
2 | George | Michael | gm@gmail.com |
3 | David | Bowie | david@gmail.com |
4 | Blue | Steele | blue@gmail.com |
order_id | order_date | amount | customer_id |
---|---|---|---|
1 | '2016/02/10' | 99.99 | 1 |
2 | '2017/11/11' | 35.50 | 1 |
3 | '2014/12/12' | 800.67 | 2 |
4 | '2015/01/03' | 12.50 | 2 |
CUSTOMERS
ORDERS
PRIMARY KEY
customer_id | first_name | last_name | |
---|---|---|---|
1 | Boy | George | george@gmail.com |
2 | George | Michael | gm@gmail.com |
3 | David | Bowie | david@gmail.com |
4 | Blue | Steele | blue@gmail.com |
order_id | order_date | amount | customer_id |
---|---|---|---|
1 | '2016/02/10' | 99.99 | 1 |
2 | '2017/11/11' | 35.50 | 1 |
3 | '2014/12/12' | 800.67 | 2 |
4 | '2015/01/03' | 12.50 | 2 |
CUSTOMERS
ORDERS
FOREIGNKEY
id | first_name | last_name | |
---|---|---|---|
1 | Boy | George | george@gmail.com |
2 | George | Michael | gm@gmail.com |
3 | David | Bowie | david@gmail.com |
4 | Blue | Steele | blue@gmail.com |
id | order_date | amount | customer_id |
---|---|---|---|
1 | '2016/02/10' | 99.99 | 1 |
2 | '2017/11/11' | 35.50 | 1 |
3 | '2014/12/12' | 800.67 | 2 |
4 | '2015/01/03' | 12.50 | 2 |
CUSTOMERS
ORDERS
Let's Get Coding!
I'm Sorry This Diagram Is So Ugly
INNER JOIN
A
B
Select all records from A and B where the join condition is met
SELECT * FROM customers
JOIN orders
ON customers.id = orders.customer_id;
INNER JOIN
Customers
SELECT * FROM customers
JOIN orders
ON customers.id = orders.customer_id;
Orders
LEFT JOIN
A
B
Select everything from A, along with any matching records in B
RIGHT JOIN
Select everything from B, along with any matching records in A
B
A
YOUR
TURN
STUDENTS
- id
- first_name
PAPERS
- title
- grade
- student_id
WRITE THIS SCHEMA
INSERT INTO students (first_name) VALUES
('Caleb'), ('Samantha'), ('Raj'), ('Carlos'), ('Lisa');
INSERT INTO papers (student_id, title, grade ) VALUES
(1, 'My First Book Report', 60),
(1, 'My Second Book Report', 75),
(2, 'Russian Lit Through The Ages', 94),
(2, 'De Montaigne and The Art of The Essay', 98),
(4, 'Borges and Magical Realism', 89);
INSERT THIS DATA
(COPY AND PASTE IT)
+------------+---------------------------------------+-------+
| first_name | title | grade |
+------------+---------------------------------------+-------+
| Samantha | De Montaigne and The Art of The Essay | 98 |
| Samantha | Russian Lit Through The Ages | 94 |
| Carlos | Borges and Magical Realism | 89 |
| Caleb | My Second Book Report | 75 |
| Caleb | My First Book Report | 60 |
+------------+---------------------------------------+-------+
PRINT THIS
+------------+---------------------------------------+-------+
| first_name | title | grade |
+------------+---------------------------------------+-------+
| Caleb | My First Book Report | 60 |
| Caleb | My Second Book Report | 75 |
| Samantha | Russian Lit Through The Ages | 94 |
| Samantha | De Montaigne and The Art of The Essay | 98 |
| Raj | NULL | NULL |
| Carlos | Borges and Magical Realism | 89 |
| Lisa | NULL | NULL |
+------------+---------------------------------------+-------+
PRINT THIS
+------------+---------------------------------------+-------+
| first_name | title | grade |
+------------+---------------------------------------+-------+
| Caleb | My First Book Report | 60 |
| Caleb | My Second Book Report | 75 |
| Samantha | Russian Lit Through The Ages | 94 |
| Samantha | De Montaigne and The Art of The Essay | 98 |
| Raj | MISSING | 0 |
| Carlos | Borges and Magical Realism | 89 |
| Lisa | MISSING | 0 |
+------------+---------------------------------------+-------+
PRINT THIS
+------------+---------+
| first_name | average |
+------------+---------+
| Samantha | 96.0000 |
| Carlos | 89.0000 |
| Caleb | 67.5000 |
| Raj | 0 |
| Lisa | 0 |
+------------+---------+
PRINT THIS
+------------+---------+----------------+
| first_name | average | passing_status |
+------------+---------+----------------+
| Samantha | 96.0000 | PASSING |
| Carlos | 89.0000 | PASSING |
| Caleb | 67.5000 | FAILING |
| Raj | 0 | FAILING |
| Lisa | 0 | FAILING |
+------------+---------+----------------+
PRINT THIS
MANY:MANY
The Tiny Bit Trickier Relationship
Books <-> Authors
Blog Post <-> Tags
Students <-> Classes
Some Examples
Imagine we're building a tv show reviewing application
Series Data
Reviewers Data
Reviews Data
id |
---|
first_name |
last_name |
id |
---|
title |
released_year |
genre |
id |
---|
rating |
series_id |
reviewer_id |
Reviewers
Series
Reviews
id | first_name | last_name |
---|---|---|
1 | Blue | Steele |
2 | Wyatt | Earp |
id | title | released_year | genre |
---|---|---|---|
1 | Archer | 2009 | Animation |
2 | Fargo | 2014 | Drama |
Reviewers
Series
id | rating | reviewer_id | series_id |
---|---|---|---|
1 | 8.9 | 1 | 2 |
2 | 9.5 | 2 | 2 |
Reviews
INSERT INTO series (title, released_year, genre) VALUES
('Archer', 2009, 'Animation'),
('Arrested Development', 2003, 'Comedy'),
("Bob's Burgers", 2011, 'Animation'),
('Bojack Horseman', 2014, 'Animation'),
("Breaking Bad", 2008, 'Drama'),
('Curb Your Enthusiasm', 2000, 'Comedy'),
("Fargo", 2014, 'Drama'),
('Freaks and Geeks', 1999, 'Comedy'),
('General Hospital', 1963, 'Drama'),
('Halt and Catch Fire', 2014, 'Drama'),
('Malcolm In The Middle', 2000, 'Comedy'),
('Pushing Daisies', 2007, 'Comedy'),
('Seinfeld', 1989, 'Comedy'),
('Stranger Things', 2016, 'Drama');
INSERT SERIES
INSERT INTO reviewers (first_name, last_name) VALUES
('Thomas', 'Stoneman'),
('Wyatt', 'Skaggs'),
('Kimbra', 'Masters'),
('Domingo', 'Cortes'),
('Colt', 'Steele'),
('Pinkie', 'Petit'),
('Marlon', 'Crafford');
INSERT REVIEWERS
INSERT INTO reviews(series_id, reviewer_id, rating) VALUES
(1,1,8.0),(1,2,7.5),(1,3,8.5),(1,4,7.7),(1,5,8.9),
(2,1,8.1),(2,4,6.0),(2,3,8.0),(2,6,8.4),(2,5,9.9),
(3,1,7.0),(3,6,7.5),(3,4,8.0),(3,3,7.1),(3,5,8.0),
(4,1,7.5),(4,3,7.8),(4,4,8.3),(4,2,7.6),(4,5,8.5),
(5,1,9.5),(5,3,9.0),(5,4,9.1),(5,2,9.3),(5,5,9.9),
(6,2,6.5),(6,3,7.8),(6,4,8.8),(6,2,8.4),(6,5,9.1),
(7,2,9.1),(7,5,9.7),
(8,4,8.5),(8,2,7.8),(8,6,8.8),(8,5,9.3),
(9,2,5.5),(9,3,6.8),(9,4,5.8),(9,6,4.3),(9,5,4.5),
(10,5,9.9),
(13,3,8.0),(13,4,7.2),
(14,2,8.5),(14,3,8.9),(14,4,8.9);
INSERT REVIEWS
NOW LET'S DO STUFF
+----------------------+--------+
| title | rating |
+----------------------+--------+
| Archer | 8.0 |
| Archer | 7.5 |
| Archer | 8.5 |
| Archer | 7.7 |
| Archer | 8.9 |
| Arrested Development | 8.1 |
| Arrested Development | 6.0 |
| Arrested Development | 8.0 |
| Arrested Development | 8.4 |
| Arrested Development | 9.9 |
| Bob's Burgers | 7.0 |
| Bob's Burgers | 7.5 |
| Bob's Burgers | 8.0 |
| Bob's Burgers | 7.1 |
| Bob's Burgers | 8.0 |
+----------------------+--------+
+----------------------+------------+
| title | avg_rating |
+----------------------+------------+
| General Hospital | 5.38000 |
| Bob's Burgers | 7.52000 |
| Seinfeld | 7.60000 |
| Bojack Horseman | 7.94000 |
| Arrested Development | 8.08000 |
| Curb Your Enthusiasm | 8.12000 |
| Archer | 8.12000 |
| Freaks and Geeks | 8.60000 |
| Stranger Things | 8.76667 |
| Breaking Bad | 9.36000 |
| Fargo | 9.40000 |
| Halt and Catch Fire | 9.90000 |
+----------------------+------------+
+------------+-----------+--------+
| first_name | last_name | rating |
+------------+-----------+--------+
| Thomas | Stoneman | 8.0 |
| Thomas | Stoneman | 8.1 |
| Thomas | Stoneman | 7.0 |
| Thomas | Stoneman | 7.5 |
| Thomas | Stoneman | 9.5 |
| Wyatt | Skaggs | 7.5 |
| Wyatt | Skaggs | 7.6 |
| Wyatt | Skaggs | 9.3 |
| Wyatt | Skaggs | 6.5 |
| Wyatt | Skaggs | 8.4 |
| Wyatt | Skaggs | 9.1 |
| Wyatt | Skaggs | 7.8 |
| Wyatt | Skaggs | 5.5 |
| Wyatt | Skaggs | 8.5 |
| Kimbra | Masters | 8.5 |
| Kimbra | Masters | 8.0 |
| Kimbra | Masters | 7.1 |
| Kimbra | Masters | 7.8 |
| Kimbra | Masters | 9.0 |
| Kimbra | Masters | 7.8 |
+------------+-----------+--------+
+-----------------------+
| unreviewed_series |
+-----------------------+
| Malcolm In The Middle |
| Pushing Daisies |
+-----------------------+
+-----------+------------+
| genre | avg_rating |
+-----------+------------+
| Animation | 7.86000 |
| Comedy | 8.16250 |
| Drama | 8.04375 |
+-----------+------------+
+------------+-----------+-------+-----+-----+---------+----------+
| first_name | last_name | COUNT | MIN | MAX | AVG | STATUS |
+------------+-----------+-------+-----+-----+---------+----------+
| Thomas | Stoneman | 5 | 7.0 | 9.5 | 8.02000 | ACTIVE |
| Wyatt | Skaggs | 9 | 5.5 | 9.3 | 7.80000 | ACTIVE |
| Kimbra | Masters | 9 | 6.8 | 9.0 | 7.98889 | ACTIVE |
| Domingo | Cortes | 10 | 5.8 | 9.1 | 7.83000 | ACTIVE |
| Colt | Steele | 10 | 4.5 | 9.9 | 8.77000 | ACTIVE |
| Pinkie | Petit | 4 | 4.3 | 8.8 | 7.25000 | ACTIVE |
| Marlon | Crafford | 0 | 0.0 | 0.0 | 0.00000 | INACTIVE |
+------------+-----------+-------+-----+-----+---------+----------+
+----------------------+--------+-----------------+
| title | rating | reviewer |
+----------------------+--------+-----------------+
| Archer | 8.0 | Thomas Stoneman |
| Archer | 7.7 | Domingo Cortes |
| Archer | 8.5 | Kimbra Masters |
| Archer | 7.5 | Wyatt Skaggs |
| Archer | 8.9 | Colt Steele |
| Arrested Development | 8.4 | Pinkie Petit |
| Arrested Development | 9.9 | Colt Steele |
| Arrested Development | 8.1 | Thomas Stoneman |
| Arrested Development | 6.0 | Domingo Cortes |
| Arrested Development | 8.0 | Kimbra Masters |
| Bob's Burgers | 7.0 | Thomas Stoneman |
| Bob's Burgers | 8.0 | Domingo Cortes |
| Bob's Burgers | 7.1 | Kimbra Masters |
| Bob's Burgers | 7.5 | Pinkie Petit |
| Bob's Burgers | 8.0 | Colt Steele |
+----------------------+--------+-----------------+
MySQL
VIEWS
GROUP BY HAVING
GROUP BY ROLL UP
SQL
MODES
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
Viewing Modes
SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';
SETTING Modes
Copy of MySQL 2.0: Relationships and Joins
By Colt Steele
Copy of MySQL 2.0: Relationships and Joins
- 5,156