Difference between LEFT and RIGHT OUTER Joins in SQL - MySQL Join example

There are two kinds of OUTER joins in SQL, LEFT OUTER join and RIGHT OUTER join. Main
difference between RIGHT OUTER join and LEFT OUTER join, as
there name suggest, is inclusion of non matched rows. Sine INNER join only
include matching rows, where value of joining column is same, in final result
set, but OUTER join extends that functionality and also
include unmatched rows in final result. LEFT outer join
includes unmatched rows from table written on left of join predicate. On the
other hand RIGHT OUTER join,
along with all matching rows, includes unmatched rows from right side of table.
In short result of LEFT outer join is INNER JOIN +
unmatched rows from LEFT table and RIGHT OUTER join is INNER JOIN +
unmatched rows from right hand side table. Similar to difference between INNER join and OUTER join,
difference between LEFT and RIGHT OUTER JOIN can be better
understand by a simple example, which we will see in next section. By the way
joins are very popular in SQL interviews, and along with classic questions like
finding second highest salary of employee,
Inner join vs outer join or left outer join vs right outer join is commonly
asked.

LEFT and RIGHT OUTER Join Example in SQL

In order to understand difference between LEFT and RIGHT outer
join, we will use once again use classical Employee and Department relationship.
In this example, both of these table are connected using dept_id, which
means both have same set of data in that column, let's see data on these two
table.

mysql> select * from employee;

+--------+----------+---------+--------+

| emp_id | emp_name | dept_id | salary |

+--------+----------+---------+--------+

|    103 |
Jack     |       1 |  
1400 |

|    104 |
John     |       2 |  
1450 |

|    108 |
Alan     |       3 |  
1150 |

|    107 |
Ram      |    NULL |   
600 |

+--------+----------+---------+--------+

4 rows in set (0.00 sec)

mysql> select * from department;

+---------+-----------+

| dept_id | dept_name |

+---------+-----------+

|       1 |
Sales     |

|       2 |
Finance   |

|       3 |
Accounts  |

|       4 |
Marketing |

+---------+-----------+

4 rows in set (0.00 sec)

If you look closely, there is one row in employee table which contains NULL, for which
there is no entry in department table. Similarly department table
contains a department (row) Marketing ,for which
there is no employee in employee table. When we do a LEFT or RIGHT outer join
it includes unmatched rows from left or right table. In this case LEFT OUTER JOIN should
include employee with NULL as department and RIGHT OUTER
JOIN should include Marketing department. Here is example of LEFT and RIGHT OUTER Join in
MySQL database :

mysql> select e.emp_id, e.emp_name, d.dept_name
from employee e LEFT JOIN department d on e.dept_id=d.dept_id;

+--------+----------+-----------+

| emp_id | emp_name | dept_name |

+--------+----------+-----------+

|    103 |
Jack     | Sales     |

|    104 |
John     | Finance   |

|    108 |
Alan     | Accounts  |

|    107 |
Ram      | NULL      |

+--------+----------+-----------+

4 rows in set (0.01 sec)

As I said unmatched rows, i.e. row with dept_id as NULL has
included in final result and dept_name for that
row is NULL, as there is no corresponding row for NULL dept_id in department table. But
note that Marketing department is not included in this
result. Now, let's see example of RIGHT OUTER JOIN in MySQL,
this should include Marketing department but leave out employee
with NULL dept_id.

mysql> select e.emp_id, e.emp_name, d.dept_name
from employee e RIGHT JOIN department d on e.dept_id=d.dept_id;

+--------+----------+-----------+

| emp_id | emp_name | dept_name |

+--------+----------+-----------+

|    103 |
Jack     | Sales     |

|    104 |
John     | Finance   |

|    108 |
Alan     | Accounts  |

|   NULL |
NULL     | Marketing |

+--------+----------+-----------+

4 rows in set (0.00 sec)

As I said, final result set has Marketing department
and emp_id, emp_name is NULL in that
row because there is no employee with dept_id=4 in
employee table.

Difference between LEFT and RIGHT OUTER JOIN
in SQL

In short, following are some notable difference between LEFT and RIGHT
outer join in SQL :

1) LEFT OUTER join includes unmatched rows from left table while RIGHT
OUTER join includes unmatched rows from right side of table.

2) Result of LEFT OUTER join can be seen as INNER JOIN + unmatched rows
of left able while result of RIGHT OUTER join is equal to INNER JOIN +
unmatched rows from right side table.

3) In ANSI SQL, left outer join is written as LEFT JOIN while right
outer join is written as RIGHT JOIN in select sql statements.

4) In Transact-SQL syntax left outer join is written as *= and right
outer join is written as =*, Sybase database supports both syntax and you can
write join queries in both ANSI and T-SQL syntax.

That's all on difference between LEFT and RIGHT OUTER JOIN in SQL.
We have seen example of RIGHT and LEFT join in MySQL database but since we have
used ANSI syntax of OUTER joins, it's for other databases e.g. Oracle, Sybase,
SQL Server and PostgreSQL as well. JOIN is a one of the most important and
common concept in SQL and you should be good enough to figure out which rows
will be included as a result of JOIN statement before actually running that SELECT query against any
table. Some time erroneous JOIN query can bring loads of data and potentially
may hang your database so beware of it.

Source:http://javarevisited.blogspot.com/2013/05/difference-between-left-and-right-outer-join-sql-mysql.html