Different Types of Joins in Database

Cross JOIN:

Cross JOIN is a simplest form of JOINs which matches each row from one database table to all rows of another. In other words it gives us combinations of each row of first table with all records in second table. Suppose we want to get all member records against all the movie records, we can use the script shown below to get our desired results.

SELECT * FROM `movies` CROSS JOIN `members`

INNER JOIN:

The inner JOIN is used to return rows from both tables that satisfy the given condition. Suppose , you want to get list of members who have rented movies together with titles of movies rented by them. You can simply use an INNER JOIN for that, which returns rows from both tables that satisfy with given conditions.

SELECT members.`first_name` , members.`last_name` , movies.`title` FROM members ,movies WHERE movies.`id` = members.`movie_id`

LEFT JOIN:

Assume now you want to get titles of all movies together with names of members who have rented them. It is clear that some movies have not being rented by any one. We can simply use LEFT JOIN for the purpose.

The LEFT JOIN returns all the rows from the table on the left even if no matching rows have been found in the table on the right. Where no matches have been found in the table on the right, NULL is returned.

Here, we have joined the Employee table to itself to find out Employee names that have the same office location and Department

RIGHT JOIN:

RIGHT JOIN is obviously the opposite of LEFT JOIN. The RIGHT JOIN returns all the columns from the table on the right even if no matching rows have been found in the table on the left. Where no matches have been found in the table on the left, NULL is returned.

In our example, lets assume that you need to get names of members and movies rented by them. Now we have a new member who has not rented any movie yet.

SELECT A.`first_name` , A.`last_name`, B.`title` FROM `members` AS A RIGHT JOIN `movies` AS B ON B.`id` = A.`movie_id`

Left Outer Join:

The FULL OUTER JOIN keyword returns all records of both tables right and left either the records match or not.

Self Join:

SELF JOIN is just like any other INNER or OUTER Join, with the only exceptions that both the tables i.e. the Joining and Joined table are the same.

We generally use SELF Joins when we want to find out the relationship between rows of the same table. For example, if a table has both Employee id and Supervisor Employee id, we can join the table against itself if we want to find out the managers name against the Employees name.

Please note that for SELF JOIN you should use table aliases so that Join conditions can be specified referring to the correct table.

SELECT e1.name as Employee1, e2.name as Employee2 from Employee e1 inner join Employee e2 on e1.office_id = e2.office_id where e1.Department_id = e2.Department_id

Here, we have joined the Employee table to itself to find out Employee names that have the same office location and Department.