MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS

What are JOINS?

Joins help retrieving data from two or more database tables. 
The tables are mutually related using primary and foreign keys.

Note: JOIN is the most misunderstood topic amongst SQL leaners. For sake of simplicity and ease of understanding , we will be using a new Database to practice sample.  As shown below

idfirst_namelast_namemovie_id
1AdamSmith1
2RaviKumar2
3SusanDavidson5
4JennyAdrianna8
6LeePong10
idtitlecategory
1ASSASSIN'S CREED: EMBERSAnimations
2Real Steel(2012)Animations
3Alvin and the ChipmunksAnimations
4The Adventures of Tin TinAnimations
5Safe (2012)Action
6Safe House(2012)Action
7GIA18+
8Deadline 200918+
9The Dirty Picture18+
10Marley and meRomance
 

Types of joins

 

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.

MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS

SELECT * FROM `movies` CROSS JOIN `members`

Executing the above script in MySQL workbench gives us the following results.

 

idtitle idfirst_namelast_namemovie_id
1ASSASSIN'S CREED: EMBERSAnimations1AdamSmith1
1ASSASSIN'S CREED: EMBERSAnimations2RaviKumar2
1ASSASSIN'S CREED: EMBERSAnimations3SusanDavidson5
1ASSASSIN'S CREED: EMBERSAnimations4JennyAdrianna8
1ASSASSIN'S CREED: EMBERSAnimations6LeePong10
2Real Steel(2012)Animations1AdamSmith1
2Real Steel(2012)Animations2RaviKumar2
2Real Steel(2012)Animations3SusanDavidson5
2Real Steel(2012)Animations4JennyAdrianna8
2Real Steel(2012)Animations6LeePong10
3Alvin and the ChipmunksAnimations1AdamSmith1
3Alvin and the ChipmunksAnimations2RaviKumar2
3Alvin and the ChipmunksAnimations3SusanDavidson5
3Alvin and the ChipmunksAnimations4JennyAdrianna8
3Alvin and the ChipmunksAnimations6LeePong10
4The Adventures of Tin TinAnimations1AdamSmith1
4The Adventures of Tin TinAnimations2RaviKumar2
4The Adventures of Tin TinAnimations3SusanDavidson5
4The Adventures of Tin TinAnimations4JennyAdrianna8
4The Adventures of Tin TinAnimations6LeePong10
5Safe (2012)Action1AdamSmith1
5Safe (2012)Action2RaviKumar2
5Safe (2012)Action3SusanDavidson5
5Safe (2012)Action4JennyAdrianna8
5Safe (2012)Action6LeePong10
6Safe House(2012)Action1AdamSmith1
6Safe House(2012)Action2RaviKumar2
6Safe House(2012)Action3SusanDavidson5
6Safe House(2012)Action4JennyAdrianna8
6Safe House(2012)Action6LeePong10
7GIA18+1AdamSmith1
7GIA18+2RaviKumar2
7GIA18+3SusanDavidson5
7GIA18+4JennyAdrianna8
7GIA18+6LeePong10
8Deadline(2009)18+1AdamSmith1
8Deadline(2009)18+2RaviKumar2
8Deadline(2009)18+3SusanDavidson5
8Deadline(2009)18+4JennyAdrianna8
8Deadline(2009)18+6LeePong10
9The Dirty Picture18+1AdamSmith1
9The Dirty Picture18+2RaviKumar2
9The Dirty Picture18+3SusanDavidson5
9The Dirty Picture18+4JennyAdrianna8
9The Dirty Picture18+6LeePong10
10Marley and meRomance1AdamSmith1
10Marley and meRomance2RaviKumar2
10Marley and meRomance3SusanDavidson5
10Marley and meRomance4JennyAdrianna8
10Marley and meRomance6LeePong10

 

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.

MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS

 

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

Executing the above script give

first_namelast_nametitle
AdamSmithASSASSIN'S CREED: EMBERS
RaviKumarReal Steel(2012)
SusanDavidsonSafe (2012)
JennyAdriannaDeadline(2009)
LeePongMarley and me

Note the above results script can also be written as follows to achieve the same results.

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

Outer JOINs

MySQL Outer JOINs return all records matching from both tables .

It can detect records having no match in joined table. It returns NULL values for records of joined table if no match is found.

Sounds Confusing ? Let's look into an example -

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.

MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS

 

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.

 

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

Executing the above script in MySQL workbench gives.You can see that in the returned result which is listed below that for movies which are not rented, member name fields are having NULL values. That means no matching member found members table for that particular movie.

titlefirst_namelast_name
ASSASSIN'S CREED: EMBERSAdamSmith
Real Steel(2012)RaviKumar
Safe (2012)SusanDavidson
Deadline(2009)JennyAdrianna
Marley and meLeePong
Alvin and the ChipmunksNULLNULL
The Adventures of Tin TinNULLNULL
Safe House(2012)NULLNULL
GIANULLNULL
The Dirty PictureNULLNULL
Note: Null is returned for non-matching rows on right

 

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,  let's 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

MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS

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

 

Executing the above script in MySQL workbench gives the following results.

titlefirst_namelast_name
AdamSmithASSASSIN'S CREED: EMBERS
RaviKumarReal Steel(2012)
SusanDavidsonSafe (2012)
JennyAdriannaDeadline(2009)
LeePongMarley and me
NULLNULLAlvin and the Chipmunks
NULLNULLThe Adventures of Tin Tin
NULLNULLSafe House(2012)
NULLNULLGIA
NULLNULLThe Dirty Picture
Note: Null is returned for non-matching rows on left

"ON" and "USING" clauses

In above JOIN query examples, we have used ON clause to match the records between table.

USING clause can also be used for the same purpose. The difference with USING is it needs to have identical names for matched columns in both tables.

 

In "movies" table so far we used its primary key with the name "id". We referred to same in  "members" table with the name "movie_id".

 

Let's rename "movies" tables "id" field to have the name "movie_id". We do this in order to have identical matched field names.

ALTER TABLE `movies` CHANGE `id` `movie_id` INT( 11 ) NOT NULL AUTO_INCREMENT;

Next let's use USING with above LEFT JOIN example.

SELECT A.`title` , B.`first_name` , B.`last_name`
FROM `movies` AS A
LEFT JOIN `members` AS B
USING ( `movie_id` )

Apart from using ON and USING with JOINs you can use many other MySQL clauses like GROUP BY, WHERE and even functions like SUM, AVG, etc.

 

Why should we use joins?

Now you may think, why we use JOINs when we can do the same task running queries. Especially if you have some experience in database programming you know we can run queries one by one, use output of each in successive queries. Of course, that is possible. But using JOINs, you can get the work done by using only a one query with any search parameters. On the other hand MySQL can achieve better performance with JOINs as it can use Indexing. Simply use of single JOIN query instead running multiple queries do reduce server overhead. Using multiple queries instead that leads more data transfers between MySQL and applications (software). Further it requires more data manipulations in application end also.

It is clear that we can achieve better MySQL and application performances by use of JOINs.

 

Summary

  • JOINS allow us to combine data from more than one table into a single result set.
  • JOINS have better performance compared to sub queries
  • INNER JOINS only return rows that meet the given criteria.
  • OUTER JOINS can also return rows where no matches have been found. The unmatched rows are returned with the NULL keyword.
  • The major JOIN types include Inner, Left Outer, Right Outer, Cross JOINS etc.
  • The frequently used clause in JOIN operations is "ON". "USING" clause requires that matching columns be of the same name.
  • JOINS can also be used in other clauses such as GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS etc.