SQL
What is Normalization? 1NF, 2NF, 3NF, BCNF Database Example
What is Normalization? Normalization is a database design technique that reduces data redundancy and...
MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS
- Details
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
| id | first_name | last_name | movie_id |
|---|---|---|---|
| 1 | Adam | Smith | 1 |
| 2 | Ravi | Kumar | 2 |
| 3 | Susan | Davidson | 5 |
| 4 | Jenny | Adrianna | 8 |
| 6 | Lee | Pong | 10 |
| id | title | category |
|---|---|---|
| 1 | ASSASSIN'S CREED: EMBERS | Animations |
| 2 | Real Steel(2012) | Animations |
| 3 | Alvin and the Chipmunks | Animations |
| 4 | The Adventures of Tin Tin | Animations |
| 5 | Safe (2012) | Action |
| 6 | Safe House(2012) | Action |
| 7 | GIA | 18+ |
| 8 | Deadline 2009 | 18+ |
| 9 | The Dirty Picture | 18+ |
| 10 | Marley and me | Romance |
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.
SELECT * FROM `movies` CROSS JOIN `members`
Executing the above script in MySQL workbench gives us the following results.
id title id first_name last_name movie_id 1 ASSASSIN'S CREED: EMBERS Animations 1 Adam Smith 1 1 ASSASSIN'S CREED: EMBERS Animations 2 Ravi Kumar 2 1 ASSASSIN'S CREED: EMBERS Animations 3 Susan Davidson 5 1 ASSASSIN'S CREED: EMBERS Animations 4 Jenny Adrianna 8 1 ASSASSIN'S CREED: EMBERS Animations 6 Lee Pong 10 2 Real Steel(2012) Animations 1 Adam Smith 1 2 Real Steel(2012) Animations 2 Ravi Kumar 2 2 Real Steel(2012) Animations 3 Susan Davidson 5 2 Real Steel(2012) Animations 4 Jenny Adrianna 8 2 Real Steel(2012) Animations 6 Lee Pong 10 3 Alvin and the Chipmunks Animations 1 Adam Smith 1 3 Alvin and the Chipmunks Animations 2 Ravi Kumar 2 3 Alvin and the Chipmunks Animations 3 Susan Davidson 5 3 Alvin and the Chipmunks Animations 4 Jenny Adrianna 8 3 Alvin and the Chipmunks Animations 6 Lee Pong 10 4 The Adventures of Tin Tin Animations 1 Adam Smith 1 4 The Adventures of Tin Tin Animations 2 Ravi Kumar 2 4 The Adventures of Tin Tin Animations 3 Susan Davidson 5 4 The Adventures of Tin Tin Animations 4 Jenny Adrianna 8 4 The Adventures of Tin Tin Animations 6 Lee Pong 10 5 Safe (2012) Action 1 Adam Smith 1 5 Safe (2012) Action 2 Ravi Kumar 2 5 Safe (2012) Action 3 Susan Davidson 5 5 Safe (2012) Action 4 Jenny Adrianna 8 5 Safe (2012) Action 6 Lee Pong 10 6 Safe House(2012) Action 1 Adam Smith 1 6 Safe House(2012) Action 2 Ravi Kumar 2 6 Safe House(2012) Action 3 Susan Davidson 5 6 Safe House(2012) Action 4 Jenny Adrianna 8 6 Safe House(2012) Action 6 Lee Pong 10 7 GIA 18+ 1 Adam Smith 1 7 GIA 18+ 2 Ravi Kumar 2 7 GIA 18+ 3 Susan Davidson 5 7 GIA 18+ 4 Jenny Adrianna 8 7 GIA 18+ 6 Lee Pong 10 8 Deadline(2009) 18+ 1 Adam Smith 1 8 Deadline(2009) 18+ 2 Ravi Kumar 2 8 Deadline(2009) 18+ 3 Susan Davidson 5 8 Deadline(2009) 18+ 4 Jenny Adrianna 8 8 Deadline(2009) 18+ 6 Lee Pong 10 9 The Dirty Picture 18+ 1 Adam Smith 1 9 The Dirty Picture 18+ 2 Ravi Kumar 2 9 The Dirty Picture 18+ 3 Susan Davidson 5 9 The Dirty Picture 18+ 4 Jenny Adrianna 8 9 The Dirty Picture 18+ 6 Lee Pong 10 10 Marley and me Romance 1 Adam Smith 1 10 Marley and me Romance 2 Ravi Kumar 2 10 Marley and me Romance 3 Susan Davidson 5 10 Marley and me Romance 4 Jenny Adrianna 8 10 Marley and me Romance 6 Lee Pong 10
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`
Executing the above script give
first_name last_name title Adam Smith ASSASSIN'S CREED: EMBERS Ravi Kumar Real Steel(2012) Susan Davidson Safe (2012) Jenny Adrianna Deadline(2009) Lee Pong Marley 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.
