Left Outer Join / Right Outer Join

There are two kinds of OUTER joins in SQL, LEFT OUTER join and RIGHT OUTER join. The main difference between RIGHT OUTER join and LEFT OUTER join, as there name suggest, is the inclusion of non-matched rows. Sine INNER join only include matching rows, where the value of joining column is same, in the final result set, but OUTER join extends that functionality and also include unmatched rows in the final result. LEFT outer join includes unmatched rows from table written on the left of join predicate. On the other hand, RIGHT OUTER join, along with all matching rows, includes unmatched rows from the right side of the 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 the right-hand side table.

mysql> select e.eid,e.name,d.did,d.dname from employee e left outer join department d on d.did=e.did;
+------+--------+------+-----------+
| eid  | name   | did  | dname     |
+------+--------+------+-----------+
|    1 | gaurav |    1 | Finance   |
|    2 | gagan  |    5 | Marketing |
|    3 | deepa  | NULL | NULL      |
|    4 | neha   | NULL | NULL      |
+------+--------+------+-----------+
4 rows in set (0.00 sec)

mysql> select e.eid,e.name,d.did,d.dname from department d right outer join employee e on d.did=e.did;
+------+--------+------+-----------+
| eid  | name   | did  | dname     |
+------+--------+------+-----------+
|    1 | gaurav |    1 | Finance   |
|    2 | gagan  |    5 | Marketing |
|    3 | deepa  | NULL | NULL      |
|    4 | neha   | NULL | NULL      |
+------+--------+------+-----------+
4 rows in set (0.00 sec)

Note: We can exchange the left and right outer join predicate as we change the tables order in query.

mysql> select e.eid,e.name,d.did,d.dname from department d left outer join employee e on d.did=e.did;
+------+--------+------+-----------+
| eid  | name   | did  | dname     |
+------+--------+------+-----------+
|    1 | gaurav |    1 | Finance   |
|    2 | gagan  |    5 | Marketing |
| NULL | NULL   |    2 | HR        |
| NULL | NULL   |    3 | IT        |
| NULL | NULL   |    4 | Sales     |
+------+--------+------+-----------+
5 rows in set (0.00 sec)

mysql> select e.eid,e.name,d.did,d.dname from employee e right outer join department d on d.did=e.did;
+------+--------+------+-----------+
| eid  | name   | did  | dname     |
+------+--------+------+-----------+
|    1 | gaurav |    1 | Finance   |
|    2 | gagan  |    5 | Marketing |
| NULL | NULL   |    2 | HR        |
| NULL | NULL   |    3 | IT        |
| NULL | NULL   |    4 | Sales     |
+------+--------+------+-----------+
5 rows in set (0.00 sec)

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

Joins / Inner Join / Outer Join

A :: Employees
B :: Department

mysql> select * from employee;
+------+--------+------+
| eid  | name   | did  |
+------+--------+------+
|    1 | gaurav |    1 |
|    2 | gagan  |    5 |
|    3 | deepa  | NULL |
|    4 | neha   | NULL |
+------+--------+------+
4 rows in set (0.00 sec)

mysql> select * from department;
+------+-----------+
| did  | dname     |
+------+-----------+
|    1 | Finance   |
|    2 | HR        |
|    3 | IT        |
|    4 | Sales     |
|    5 | Marketing |
+------+-----------+
5 rows in set (0.00 sec)

Note: Explained below, all 7 cases (Clockwise)

1> Employees which been assigned to some department.
mysql> select e.eid,e.name,d.did,d.dname from employee e,department d where d.did=e.did;
+------+--------+------+-----------+
| eid  | name   | did  | dname     |
+------+--------+------+-----------+
|    1 | gaurav |    1 | Finance   |
|    2 | gagan  |    5 | Marketing |
+------+--------+------+-----------+
2 rows in set (0.00 sec)

2> List all departments which been assigned any employee or not.
mysql> select e.eid,e.name,d.did,d.dname from employee e right outer join department d on d.did=e.did;
+------+--------+------+-----------+
| eid  | name   | did  | dname     |
+------+--------+------+-----------+
|    1 | gaurav |    1 | Finance   |
|    2 | gagan  |    5 | Marketing |
| NULL | NULL   |    2 | HR        |
| NULL | NULL   |    3 | IT        |
| NULL | NULL   |    4 | Sales     |
+------+--------+------+-----------+
5 rows in set (0.00 sec)

3> List all departments which not been assigned any employee.
mysql> select e.eid,e.name,d.did,d.dname from employee e right outer join department d on d.did=e.did where e.eid is null;
+------+------+------+-------+
| eid  | name | did  | dname |
+------+------+------+-------+
| NULL | NULL |    2 | HR    |
| NULL | NULL |    3 | IT    |
| NULL | NULL |    4 | Sales |
+------+------+------+-------+
3 rows in set (0.00 sec)

4> List all departments which not been assigned any employee And List all employees which not been assigned any departments.
mysql> select e.eid,e.name,d.did,d.dname from employee e right outer join department d on d.did=e.did where e.eid is null union select e.eid,e.name,d.did,d.dname from employee e left outer join department d on d.did=e.did where d.did is null;
+------+-------+------+-------+
| eid  | name  | did  | dname |
+------+-------+------+-------+
| NULL | NULL  |    2 | HR    |
| NULL | NULL  |    3 | IT    |
| NULL | NULL  |    4 | Sales |
|    3 | deepa | NULL | NULL  |
|    4 | neha  | NULL | NULL  |
+------+-------+------+-------+
5 rows in set (0.00 sec)

5> List all employees and departments.
mysql> select e.eid,e.name,d.did,d.dname from employee e right outer join department d on d.did=e.did where e.eid is null union select e.eid,e.name,d.did,d.dname from employee e left outer join department d on d.did=e.did where d.did is null union select e.eid,e.name,d.did,d.dname from employee e inner join department d on d.did=e.did ;
+------+--------+------+-----------+
| eid  | name   | did  | dname     |
+------+--------+------+-----------+
| NULL | NULL   |    2 | HR        |
| NULL | NULL   |    3 | IT        |
| NULL | NULL   |    4 | Sales     |
|    3 | deepa  | NULL | NULL      |
|    4 | neha   | NULL | NULL      |
|    1 | gaurav |    1 | Finance   |
|    2 | gagan  |    5 | Marketing |
+------+--------+------+-----------+
7 rows in set (0.01 sec)

6> List all employees which not been assigned any departments.
mysql> select e.eid,e.name,d.did,d.dname from employee e left outer join department d on d.did=e.did where d.did is null;
+------+-------+------+-------+
| eid  | name  | did  | dname |
+------+-------+------+-------+
|    3 | deepa | NULL | NULL  |
|    4 | neha  | NULL | NULL  |
+------+-------+------+-------+
2 rows in set (0.00 sec)

7> List all employees which been assigned any departments or not.
mysql> select e.eid,e.name,d.did,d.dname from employee e left outer join department d on d.did=e.did;
+------+--------+------+-----------+
| eid  | name   | did  | dname     |
+------+--------+------+-----------+
|    1 | gaurav |    1 | Finance   |
|    2 | gagan  |    5 | Marketing |
|    3 | deepa  | NULL | NULL      |
|    4 | neha   | NULL | NULL      |
+------+--------+------+-----------+
4 rows in set (0.00 sec)

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