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