How to Use Outer Joins to Combine Data from Three Tables in MySQL

Another Example for Outer Joins (three tables)

In this tutorial, we continue left and right join study with an example of three tables joined. The three tables have been properly normalized in relational format with IDs acting as surrogate key and foreign key, so it looks more like real world database tables.

To better show you how out joins work, I created this scenario. You live in a healthy life style. You have a list of 10 veggies you'd like to eat and want to track what veggies you eat on each weekday. You want to know (1) what veggies you ate or didn't eat last week. (2) which weekday you didn't eat any veggies.

Here are three tables I created based on scenario described above - wkday, veggie, eating.

Table #1: wkday

Table wkday contains 7 rows of data - Monday to Sunday.

Weekday table.

Use script below to create wkday table:

CREATE TABLE `wkday` (
  `id` tinyint(3) unsigned NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Use script below to populate wkday table with data:

insert into `wkday`(`id`,`name`) 
values (1,'Monday'),(2,'Tuesday'),(3,'Wednesday'),
(4,'Thursday'),(5,'Friday'),(6,'Saturday'),(7,'Sunday');

Table #2: veggie

Table veggie contains 10 vegetable names.

Vegetable table.

Use script below to create veggie table:

CREATE TABLE `veggie` (
  `id` tinyint(3) unsigned NOT NULL,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Use script below to populate veggie table with data:

insert into `veggie`(`id`,`name`) 
values (1,'Potato'),(2,'Carrot'),(3,'Broccoli'),
(4,'Cauliflower'),(5,'Celery'),(6,'Spinach'),
(7,'Tomato'),(8,'Pumpkins'),(9,'Eggplant'),(10,'Cucumbers');

Table #3: eating

Table eating contains data about what veggies you ate last week.

In this table, column wkday_id is foreign key column which points back to the id column in wkday table. Column veggie_id is foreign key column which points back to the id column in veggie table. Column id is primary key of eating table.

Eating table.

Use script below to create wkday table:

CREATE TABLE `eating` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `wkday_id` tinyint(3) unsigned NOT NULL,
  `veggie_id` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `FK_eating_veggie` (`veggie_id`),
  KEY `FK_eating_wkday` (`wkday_id`),
  CONSTRAINT `FK_eating_wkday` FOREIGN KEY (`wkday_id`) REFERENCES `wkday` (`id`),
  CONSTRAINT `FK_eating_veggie` FOREIGN KEY (`veggie_id`) REFERENCES `veggie` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Use script below to populate wkday table with data:

insert into `eating`(`id`,`wkday_id`,`veggie_id`) 
values (1,1,3),(2,1,2),(3,2,4),(4,4,6),(5,4,9),
(6,4,3),(7,5,1),(8,6,10),(9,6,2);

Note that, from this tutorial onwards, all practice queries will be written in lower-case. The purpose is to show you the different styles of writing SQL scripts. Personally I prefer lower-case as I can type faster without having to worry about changing to upper-case for SQL keywords. SQLyog displays the keywords in colors anyway.

There are two useful short-cut keys in SQLyog to change a selection to uppercase or lowercase:

Make selection uppercase: Crlt+Shift+U
Make selection lowercase: Crlt+Shift+L

Practice #1: Return all the weekdays on which you either ate or didn't eat any veggies.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query finds out what happened in last week:
 
1. The weekdays on which you ate veggies 
2. The weekdays on which you didn't each any veggies.
 
The wkday table left joins eating table on foreign 
key column wkday_id in eating table and primary 
key column id in wkday table.
 
The query returns all data from wkday table, plus some
unmatched weekday data when the weekday id is not found 
in eating table. When no matched record is found, eating
table returns value NULL for the weekday.
*/
select wd.name as wkday_name, 
       wd.id as id_in_wkday_table,
       e.wkday_id as wkday_id_in_eating_table,
       e.veggie_id as veggie_id
from wkday as wd
left join eating as e on e.wkday_id=wd.id;

In the result displayed below, if the weekday id is found in eating table, it's displayed in third column. If it's not found, NULL is returned. Wednesday (id 3) and Sunday (id 7) have no matching data in eating table so NULL values are returned from eating table in third column. The fourth column is veggie_id column in eating table where NULL values are displayed. This is because each row in eating table has a corresponding wkday_id and veggie_id. If the weekday is not found, no veggies were eaten on that day.

Query result set - 11 rows returned:
Using left outer join to get all rows on the left table.

Simplified result

The query above shows only IDs which to us are not very user friendly. Below is a query for simplified result which only shows the weekdays and veggies eaten on that day. If no veggie is eaten, NULL is displayed.

select wd.name as wkday_name, 
       v.name as veggie_eaten
from wkday as wd
left join eating as e on e.wkday_id=wd.id
left join veggie v on v.id=e.veggie_id;

Query result set - 11 rows returned:
Using left outer join to get all rows on the left table.

Practice #2: Return the weekdays on which you didn't eat any veggies.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query is similar to Practice #1 except that it only 
returns the weekdays on which you didn't each any veggies.
 
Note that the weekdays you didn't eat veggies are identified
in the where clause by checking if wkday_id column IS NULL in
eating table.
*/
select wd.name as wkday_name, 
       wd.id as id_in_wkday_table,
       e.wkday_id as wkday_id_in_eating_table,
       e.veggie_id as veggie_id
from wkday as wd
left join eating as e on e.wkday_id=wd.id
where e.wkday_id is null;

Query result set - 2 rows returned:
Using left join to get the unmatched rows.

Simplified result

select wd.name as wkday_name, 
       v.name as veggie_eaten
from wkday as wd
left join eating as e on e.wkday_id=wd.id
left join veggie v on v.id=e.veggie_id
where v.id is null;

Query result set - 2 rows returned:
Using left join to get the unmatched rows.

Practice #3: Use right join to get the same result as Practice #2.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query returns the exact result as Practice #2
but uses right join instead of left join.
*/
select wd.name as wkday_name, 
       wd.id as id_in_wkday_table,
       e.wkday_id as wkday_id_in_eating_table,
       e.veggie_id as veggie_id
from eating as e 
right join wkday as wd on e.wkday_id=wd.id
where e.wkday_id is null;

Query result set - 2 rows returned:
Using right join to get the unmatched rows.

Practice #4: Return the weekdays on which you ate some veggies.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
The following two queries return the same result
which displays the weekdays on which veggies were eaten.
 
Query 1 uses outer join and Query 2 uses inner join.
 
Inner join is recommended because outer join is much 
slower operation than inner join.
*/
 
-- Query 1: Left outer join
select wd.name as wkday_name, 
       wd.id as id_in_wkday_table,
       e.wkday_id as wkday_id_in_eating_table,
       e.veggie_id as veggie_id
from wkday as wd
left join eating as e on e.wkday_id=wd.id
where e.wkday_id is not null;
 
-- Query 2: Inner join
select wd.name as wkday_name, 
       wd.id as id_in_wkday_table,
       e.wkday_id as wkday_id_in_eating_table,
       e.veggie_id as veggie_id
from wkday as wd
inner join eating as e on e.wkday_id=wd.id;

Query result set - 9 rows returned:
Using left join to get the matched rows.

Simplified result

The simplified result shows the weekdays and veggies eaten on that day, no IDs in the result.

select wd.name as wkday_name, 
       v.name as veggie_eaten      
from wkday as wd
left join eating as e on e.wkday_id=wd.id
left join veggie v on v.id=e.veggie_id
where e.wkday_id is not null;

Query result set - 9 rows returned:
Using left join to get the matched rows.

Practice #5: Return all the veggies you either ate or didn't eat.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query finds out what happened in last week:
 
1. The veggies you ate.
2. The veggies you didn't eat.
 
The veggie table left joins eating table on foreign 
key column veggie_id in eating table and primary 
key column id in veggie table.
 
The query returns all data from veggie table, plus some
unmatched veggie data when the veggie id is not found 
in eating table. When no matched record is found, eating
table returns value NULL for the veggie.
*/
select v.name as veggie_name, 
       v.id as id_in_veggie_tbl,
       e.veggie_id as veggie_id_in_eating_tbl,
       e.wkday_id as wkday_id_in_eating_tbl
from veggie as v
left join eating as e 
on e.veggie_id=v.id
order by v.name;

Query result set - 12 rows returned:
Using left outer join to get all rows on the left table.

Simplified result

select v.name as veggie_name,
       wd.name as wkday_name 
from veggie as v
left join eating as e on e.veggie_id=v.id 
left join wkday as wd on e.wkday_id=wd.id
order by v.name;

Query result set - 12 rows returned:
Using left join to get the matched rows.

Practice #6: Return the veggies you didn't eat.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query is similar to Practice #5 except that it only 
returns the veggies you didn't eat last week.
 
Note that the veggies you didn't eat are identified in the 
where clause by checking if veggie_id column IS NULL in
eating table.
*/
select v.name as veggie_name, 
       v.id as id_in_veggie_tbl,
       e.veggie_id as veggie_id_in_eating_tbl,
       e.wkday_id as wkday_id_in_eating_tbl
from veggie as v
left join eating as e 
on e.veggie_id=v.id
where e.veggie_id is null
order by v.name;

Query result set - 3 rows returned:
Using left join to get the unmatched rows.

Practice #7: Use right join to get the same result as Practice #6.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query returns the exact result as Practice #6
but here we use right join instead of left join.
*/
select v.name as veggie_name, 
       v.id as id_in_veggie_tbl,
       e.veggie_id as veggie_id_in_eating_tbl,
       e.wkday_id as wkday_id_in_eating_tbl
from eating as e 
right join veggie as v
on e.veggie_id=v.id
where e.veggie_id is null
order by v.name;

Query result set - 3 rows returned:
Using right join to get the unmatched rows.

Happy Coding!

Other tutorials in this category

1. Using Inner Joins to Combine Data from Two Tables

2. Using Outer Joins to Combine Data from Two Tables

3. Using Self Joins to Combine Data from the Same Table

4. SQL Set Operators - a Visual Guide to UNION, UNION ALL, MIMUS/EXCEPT, INTERSECT

5. Using UNION to Append Result Sets

6. Create working tables to demonstrate how to mimic set operators MINUS, EXCEPT, INTERSECT in MySQL

7. How to do MINUS/EXCEPT and INTERSECT in MySQL

8. How to Simulate Full Join in MySQL - Part 1: return both matched and unmatched rows

9. How to Simulate Full Join in MySQL - Part 2: return unmatched rows only

10. How to Simulate Full Join in MySQL - Part 3: use UNION to simulate FULL JOIN

11. Use Cross Join to Combine Data in MySQL

12. Differences Between Join and Union

Back to Tutorial Index Page

	Custom Search