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