Here in this tutorial I am going explain all sorts of sql join those are Mysql left right inner and outer join for retrieving data from two table .Generally sql query fetches data from one table if you would like fetch data from more table with a single query so please follow the below description.lets start all join quey on by one.In below description you will be also getting different between left outer join and right outer join.Also the different between left join and inner join etc..
Suppose I have two tables 1)student 2)teacher
So here teacher_id from teacher table is link to teacher_id of student table.That means we do join between these two table by the help of teacher_id.
So lets see our tables with their data:
select * from student;
+------------+--------------+------------+ | student_id | student_name | teacher_id | +------------+--------------+------------+ | 11 | Jyotiranjan | 1 | | 12 | Loppy | 1 | | 13 | Subhalaxmi | 2 | | 14 | Sanjaya | 2 | | 15 | Abhiram | 3 | | 16 | Sanjukta | 3 | | 17 | Ramakanta | 4 | | 18 | Supriya | 4 | | 19 | Sunil | 5 | | 20 | Sujata | 5 | | 21 | Susanta | 0 | | 22 | Prabhudatta | 0 | | 23 | Riya | 0 | | 24 | Dhanjaya | 0 | | 25 | Ramahari | 0 | +------------+--------------+------------+ 15 rows in set (0.00 sec)
select * from teacher;
+------------+--------------+ | teacher_id | teacher_name | +------------+--------------+ | 1 | Jyotiprakash | | 2 | Diptikanta | | 3 | Gangadhara | | 4 | Chiranjiv | | 5 | Samsundar | | 6 | Upendra | | 7 | Tilotama | +------------+--------------+ 7 rows in set (0.00 sec)
Now lets see our query with join:
INNER JOIN:
INNER JOIN gives result of all the matching records from both tables.
Example:
SELECT * FROM `student` as s INNER JOIN `teacher` as t ON `s`.`teacher_id` = `t`.`teacher_id`;
+------------+--------------+------------+------------+--------------+ | student_id | student_name | teacher_id | teacher_id | teacher_name | +------------+--------------+------------+------------+--------------+ | 11 | Jyotiranjan | 1 | 1 | Jyotiprakash | | 12 | Loppy | 1 | 1 | Jyotiprakash | | 13 | Subhalaxmi | 2 | 2 | Diptikanta | | 14 | Sanjaya | 2 | 2 | Diptikanta | | 15 | Abhiram | 3 | 3 | Gangadhara | | 16 | Sanjukta | 3 | 3 | Gangadhara | | 17 | Ramakanta | 4 | 4 | Chiranjiv | | 18 | Supriya | 4 | 4 | Chiranjiv | | 19 | Sunil | 5 | 5 | Samsundar | | 20 | Sujata | 5 | 5 | Samsundar | +------------+--------------+------------+------------+--------------+ 10 rows in set (0.00 sec)
LEFT OUTER JOIN:
LEFT OUTER JOIN is little different from INNER JOIN because it gives extra data(unmatched data) only from left table and set all other data to NULL.
Example:
SELECT * FROM `student` as s LEFT OUTER JOIN `teacher` as t ON `s`.`teacher_id` = `t`.`teacher_id`;
+------------+--------------+------------+------------+--------------+ | student_id | student_name | teacher_id | teacher_id | teacher_name | +------------+--------------+------------+------------+--------------+ | 11 | Jyotiranjan | 1 | 1 | Jyotiprakash | | 12 | Loppy | 1 | 1 | Jyotiprakash | | 13 | Subhalaxmi | 2 | 2 | Diptikanta | | 14 | Sanjaya | 2 | 2 | Diptikanta | | 15 | Abhiram | 3 | 3 | Gangadhara | | 16 | Sanjukta | 3 | 3 | Gangadhara | | 17 | Ramakanta | 4 | 4 | Chiranjiv | | 18 | Supriya | 4 | 4 | Chiranjiv | | 19 | Sunil | 5 | 5 | Samsundar | | 20 | Sujata | 5 | 5 | Samsundar | | 21 | Susanta | 0 | NULL | NULL | | 22 | Prabhudatta | 0 | NULL | NULL | | 23 | Riya | 0 | NULL | NULL | | 24 | Dhanjaya | 0 | NULL | NULL | | 25 | Ramahari | 0 | NULL | NULL | +------------+--------------+------------+------------+--------------+ 15 rows in set (0.00 sec)
You can see the above out-put I can explain quickly that is on the above result from student_id 21-25 all have teacher_id is 0 which is not matching with teacher_id of teacher table still that showing in the result beacuse of LEFT OUTER JOIN as it is fetched only unmatched data from left table and you can see all other values are set as NULL as it is not fetching the unmatched data from right table.
RIGHT OUTER JOIN:
Its just opposite LEFT OUTER JOIN because it gives extra data(unmatched data) only from right table and set all other data to NULL
Example:
SELECT * FROM `student` as s RIGHT OUTER JOIN `teacher` as t ON `s`.`teacher_id ` = `t`.`teacher_id`;
+------------+--------------+------------+------------+--------------+ | student_id | student_name | teacher_id | teacher_id | teacher_name | +------------+--------------+------------+------------+--------------+ | 11 | Jyotiranjan | 1 | 1 | Jyotiprakash | | 12 | Loppy | 1 | 1 | Jyotiprakash | | 13 | Subhalaxmi | 2 | 2 | Diptikanta | | 14 | Sanjaya | 2 | 2 | Diptikanta | | 15 | Abhiram | 3 | 3 | Gangadhara | | 16 | Sanjukta | 3 | 3 | Gangadhara | | 17 | Ramakanta | 4 | 4 | Chiranjiv | | 18 | Supriya | 4 | 4 | Chiranjiv | | 19 | Sunil | 5 | 5 | Samsundar | | 20 | Sujata | 5 | 5 | Samsundar | | NULL | NULL | NULL | 6 | Upendra | | NULL | NULL | NULL | 7 | Tilotama | +------------+--------------+------------+------------+--------------+ 12 rows in set (0.00 sec)
You can see the above out-put I can explain quickly that is on the above result from teacher_id 6-7 all have student_id is NULL which is not matching with teacher_id of teacher table still that showing in the result beacuse of RIGHT OUTER JOIN as it is fetched only unmatched data from right table and you can see all other values are set as NULL as it is not fetching the unmatched data from left table.
FULL OUTER JOIN:
If you would like to fetch data from bothe table whether it match or not. I will just fetch all.
Example:
SELECT '*' FROM `student` as s FULL OUTER JOIN `teacher` as t ON `s`.`teacher_id` = `t`.`teacher_id`;
Some data bases doesnot support FULL OUTER JOIN then you have to use the logic of UNION.
SELECT * FROM `student` LEFT JOIN `teacher` ON `student`.`teacher_id` = `teacher`.`teacher_id`
UNION
SELECT * FROM `student` RIGHT OUTER JOIN `teacher` ON `student`.`teacher_id` = `teacher`.`teacher_id` WHERE `student`.`teacher_id` IS NULL;
+------------+--------------+------------+------------+--------------+
| student_id | student_name | teacher_id | teacher_id | teacher_name |
+------------+--------------+------------+------------+--------------+
| 11 | Jyotiranjan | 1 | 1 | Jyotiprakash |
| 12 | Loppy | 1 | 1 | Jyotiprakash |
| 13 | Subhalaxmi | 2 | 2 | Diptikanta |
| 14 | Sanjaya | 2 | 2 | Diptikanta |
| 15 | Abhiram | 3 | 3 | Gangadhara |
| 16 | Sanjukta | 3 | 3 | Gangadhara |
| 17 | Ramakanta | 4 | 4 | Chiranjiv |
| 18 | Supriya | 4 | 4 | Chiranjiv |
| 19 | Sunil | 5 | 5 | Samsundar |
| 20 | Sujata | 5 | 5 | Samsundar |
| 21 | Susanta | 0 | NULL | NULL |
| 22 | Prabhudatta | 0 | NULL | NULL |
| 23 | Riya | 0 | NULL | NULL |
| 24 | Dhanjaya | 0 | NULL | NULL |
| 25 | Ramahari | 0 | NULL | NULL |
| NULL | NULL | NULL | 6 | Upendra |
| NULL | NULL | NULL | 7 | Tilotama |
+------------+--------------+------------+------------+--------------+
17 rows in set (0.01 sec)
