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

Using Outer Joins to Combine Data from Two Tables

In last section about inner joins, we have seen that inner join can return data from two or more tables based on one or more join columns of common values. With outer join, we are able to retrieve data that have NO common values in the join columns.

There two types of outer joins - left join and right join.

Left join means all data in the table on the left of the JOIN clause will be returned, including any unmatched rows that are not found in the table on the right of the JOIN clause.
Right join means all data in the table on the right of the JOIN clause will be returned, including any unmatched rows that are not found in the table on the left of the JOIN clause.
LEFT JOIN is equivalent to LEFT OUTER JOIN.
RIGHT JOIN is equivalent to RIGHT OUTER JOIN.

A practical illustration

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.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. 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.

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

/*
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.

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

/*
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.

/*
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.

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

/*
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.

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

/*
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.

/*
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.

Next tutorial: Join a Table to Itself
Back to Section Index: Querying Multiple Tables
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search