There are two types of outer joins - left join and right join, and total 4 variants of the left and right join.

1. 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, see Practice #1.

   Basic syntax:

   select t1.* from Table1 as t1
   left join Table2 as t2 on t1.column1=t2.column1;

   

2. Here is an extension of the left join. When a column in the RIGHT table contains NULLs, the left join query will only return unmatched rows that are not found in the table on the right of the JOIN clause, see Practice #2.

   Basic syntax:

   select t1.* from Table1 as t1
   left join Table2 as t2 on t1.column1=t2.column1 where t2.column1 is null;

   

3. 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, see Practice #3.

   Basic syntax:

   select t2.* from Table1 as t1
   right join Table2 as t2 on t1.column1=t2.column1;

   

4. Here is an extension of the right join. When a column in the LEFT table contains NULLs, the right join query will only return unmatched rows that are not found in the table on the left of the JOIN clause, see Practice #4.

   Basic syntax:

   select t2.* from Table1 as t1
   right join Table2 as t2 on t1.column1=t2.column1 where t1.column1 is null;

   

5. LEFT JOIN is equivalent to LEFT OUTER JOIN.

6. RIGHT JOIN is equivalent to RIGHT OUTER JOIN.

7. It's a good practice to always stick to only left join or right join in your SQL script to avoid confusion and mistakes by accident. To write right join as left join, just swap the table names to make right table be the left table. You'll find it much easier to understand and maintain your SQL code if you only use, for example, left join in your SQL script.

The Practice

Create two tables to demonstrate the JOIN concept and syntax.

You eat pizza and/or pasta during the week. The queries below will work out:

(1) The days you eat pizza only.
(2) The days you eat both pizza and pasta.
(1) The days you eat pasta only.

Pizza table:

CREATE TABLE pizza (  
  `name` varchar(10) NOT NULL,
  `food` varchar(10) NOT NULL
);
 
insert into pizza(name,food) values 
('Monday','Pizza'),('Tuesday','Pizza'),('Wednesday','Pizza'),('Thursday','Pizza');

Pizza table data:

Pasta table:

CREATE TABLE pasta (  
  `name` varchar(10) NOT NULL,
  `food` varchar(10) NOT NULL
);
 
insert into pasta(name,food) values 
('Wednesday','Pasta'),('Thursday','Pasta'),('Friday','Pasta'),
('Saturday','Pasta'),('Sunday','Pasta');

Pasta table data:

Practice #1: Return the days on which you eat pizza only or both pizza and pasta.

In this query, Pizza table LEFT joins with Pasta table on common week days. It returns both matched and unmatched records. When matched, you eat both pizza and pasta on the day. If unmatched, you only eat pizza. Note that the unmatched rows are from the left table which is Pizza table.

select t1.name, t1.food as food1, t2.food as food2 
from pizza as t1
left join pasta as t2 on t1.name=t2.name
order by t1.name;

Query result set - 4 rows returned:

Practice #2: Return the days on which you only eat pizza.

In this query, Pizza table LEFT joins Pasta table on common week days. It returns only the unmatched records which are the days you eat pizza only. This is achieved by restricting to only NULL values in the Table2's Name column.

select t1.name, t1.food as food1, t2.food as food2 
from pizza as t1
left join pasta as t2 on t1.name=t2.name
where t2.name is null;

Query result set - 2 rows returned:

Practice #3: Return the days on which you eat pasta only or both pizza and pasta.

In this query, Pizza table RIGHT joins Pasta table on common week days. It returns both matched and unmatched records. When matched, you eat both pasta and pizza on the day. If unmatched, you only eat pasta. Note that the unmatched rows are from the right table which is Pasta table.

select t2.name, t2.food as food1, t1.food as food2 
from pizza as t1
right join pasta as t2 on t1.name=t2.name;

Query result set - 5 rows returned:

Practice #4: Return the days on which you only eat pasta.

In this query, Pizza table RIGHT joins Pasta table on common week days. It returns only the unmatched records which are the days you eat pasta only. This is achieved by restricting to only NULL values in Table1's Name column.

select t2.name, t2.food as food2, t1.food as food1 
from pizza as t1
right join pasta as t2 on t1.name=t2.name
where t1.name is null;

Query result set - 5 rows returned:

Happy Coding!

Other tutorials in this category

1. Using Inner Joins to Combine Data from Two Tables

2. Another Example for Outer Joins (three 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