Note: FULL JOIN doesn't support to Mysql so we can use UNION or UNION ALL select ed.empid, ed.emp_name, ed.emp_Join, edesg.designation from emp_data ed FULL JOIN emp_dseg edesg ON ed.empid = edesg.empid; mysql> select * from emp_data; +-------+------------+------------+ | empid | emp_name | emp_Join | +-------+------------+------------+ | 1 | Ajay | 2015-01-11 | | 2 | Anji | 2015-01-05 | | 3 | Sabhapathi | 2015-11-11 | | 4 | Bhasker | 2015-04-11 | | 5 | Raju | 2014-01-10 | | 6 | Mahipal | 2015-01-15 | | 7 | Manoj | 2015-09-14 | | 8 | Ragvendra | 2015-06-05 | | 9 | Sundheer | 2015-01-09 | | 10 | Mahi | 2015-01-1 | | 11 | Vijay | 2015-01-05 | | 12 | Sandip | 2015-01-1 | +-------+------------+------------+ 12 rows in set (0.00 sec) mysql> select * from emp_dseg; +-------+----------------------+ | empid | designation | +-------+----------------------+ | 1 | Dev | | 2 | QA | | 3 | Senior Soft Engineer | | 4 | Dev | | 5 | Qa Engineer | | 6 | Dev | | 7 | Dev | | 8 | Senior Test Engineer | | 9 | Test Eng. Enineer | | 13 | IT-Support | | 14 | DBA | +-------+----------------------+ 11 rows in set (0.00 sec) mysql> select ed.empid, ed.emp_name, ed.emp_Join, edesg.designation from emp_data ed, emp_dseg edesg where ed.empid = edesg.empid; +-------+------------+------------+----------------------+ | empid | emp_name | emp_Join | designation | +-------+------------+------------+----------------------+ | 1 | Ajay | 2015-01-11 | Dev | | 2 | Anji | 2015-01-05 | QA | | 3 | Sabhapathi | 2015-11-11 | Senior Soft Engineer | | 4 | Bhasker | 2015-04-11 | Dev | | 5 | Raju | 2014-01-10 | Qa Engineer | | 6 | Mahipal | 2015-01-15 | Dev | | 7 | Manoj | 2015-09-14 | Dev | | 8 | Ragvendra | 2015-06-05 | Senior Test Engineer | | 9 | Sundheer | 2015-01-09 | Test Eng. Enineer | +-------+------------+------------+----------------------+ 9 rows in set (0.00 sec) mysql> select ed.empid, ed.emp_name, ed.emp_Join, edesg.designation from emp_data ed INNER JOIN emp_dseg edesg ON ed.empid = edesg.empid; +-------+------------+------------+----------------------+ | empid | emp_name | emp_Join | designation | +-------+------------+------------+----------------------+ | 1 | Ajay | 2015-01-11 | Dev | | 2 | Anji | 2015-01-05 | QA | | 3 | Sabhapathi | 2015-11-11 | Senior Soft Engineer | | 4 | Bhasker | 2015-04-11 | Dev | | 5 | Raju | 2014-01-10 | Qa Engineer | | 6 | Mahipal | 2015-01-15 | Dev | | 7 | Manoj | 2015-09-14 | Dev | | 8 | Ragvendra | 2015-06-05 | Senior Test Engineer | | 9 | Sundheer | 2015-01-09 | Test Eng. Enineer | +-------+------------+------------+----------------------+ 9 rows in set (0.00 sec) mysql> select ed.empid, ed.emp_name, ed.emp_Join, edesg.designation from emp_data ed RIGHT JOIN emp_dseg edesg ON ed.empid = edesg.empid; +-------+------------+------------+----------------------+ | empid | emp_name | emp_Join | designation | +-------+------------+------------+----------------------+ | 1 | Ajay | 2015-01-11 | Dev | | 2 | Anji | 2015-01-05 | QA | | 3 | Sabhapathi | 2015-11-11 | Senior Soft Engineer | | 4 | Bhasker | 2015-04-11 | Dev | | 5 | Raju | 2014-01-10 | Qa Engineer | | 6 | Mahipal | 2015-01-15 | Dev | | 7 | Manoj | 2015-09-14 | Dev | | 8 | Ragvendra | 2015-06-05 | Senior Test Engineer | | 9 | Sundheer | 2015-01-09 | Test Eng. Enineer | | NULL | NULL | NULL | IT-Support | | NULL | NULL | NULL | DBA | +-------+------------+------------+----------------------+ 11 rows in set (0.00 sec) mysql> select ed.empid, ed.emp_name, ed.emp_Join, edesg.designation from emp_data ed LEFT JOIN emp_dseg edesg ON ed.empid = edesg.empid; +-------+------------+------------+----------------------+ | empid | emp_name | emp_Join | designation | +-------+------------+------------+----------------------+ | 1 | Ajay | 2015-01-11 | Dev | | 2 | Anji | 2015-01-05 | QA | | 3 | Sabhapathi | 2015-11-11 | Senior Soft Engineer | | 4 | Bhasker | 2015-04-11 | Dev | | 5 | Raju | 2014-01-10 | Qa Engineer | | 6 | Mahipal | 2015-01-15 | Dev | | 7 | Manoj | 2015-09-14 | Dev | | 8 | Ragvendra | 2015-06-05 | Senior Test Engineer | | 9 | Sundheer | 2015-01-09 | Test Eng. Enineer | | 10 | Mahi | 2015-01-1 | NULL | | 11 | Vijay | 2015-01-05 | NULL | | 12 | Sandip | 2015-01-1 | NULL | +-------+------------+------------+----------------------+ 12 rows in set (0.00 sec) mysql> select ed.empid, ed.emp_name, ed.emp_Join, edesg.designation from emp_data ed RIGHT JOIN emp_dseg edesg ON ed.empid = edesg.empid UNION select ed.empid, ed.emp_name, ed.emp_Join, edesg.designation from emp_data ed LEFT JOIN emp_dseg edesg ON ed.empid = edesg.empid +-------+------------+------------+----------------------+ | empid | emp_name | emp_Join | designation | +-------+------------+------------+----------------------+ | 1 | Ajay | 2015-01-11 | Dev | | 2 | Anji | 2015-01-05 | QA | | 3 | Sabhapathi | 2015-11-11 | Senior Soft Engineer | | 4 | Bhasker | 2015-04-11 | Dev | | 5 | Raju | 2014-01-10 | Qa Engineer | | 6 | Mahipal | 2015-01-15 | Dev | | 7 | Manoj | 2015-09-14 | Dev | | 8 | Ragvendra | 2015-06-05 | Senior Test Engineer | | 9 | Sundheer | 2015-01-09 | Test Eng. Enineer | | NULL | NULL | NULL | IT-Support | | NULL | NULL | NULL | DBA | | 10 | Mahi | 2015-01-1 | NULL | | 11 | Vijay | 2015-01-05 | NULL | | 12 | Sandip | 2015-01-1 | NULL | +-------+------------+------------+----------------------+ 14 rows in set (0.00 sec) mysql> select ed.empid, ed.emp_name, ed.emp_Join, edesg.designation from emp_data ed RIGHT JOIN emp_dseg edesg ON ed.empid = edesg.empid UNION ALL select ed.empid, ed.emp_name, ed.emp_Join, edesg.designation from emp_data ed LEFT JOIN emp_dseg edesg ON ed.empid = edesg.empid +-------+------------+------------+----------------------+ | empid | emp_name | emp_Join | designation | +-------+------------+------------+----------------------+ | 1 | Ajay | 2015-01-11 | Dev | | 2 | Anji | 2015-01-05 | QA | | 3 | Sabhapathi | 2015-11-11 | Senior Soft Engineer | | 4 | Bhasker | 2015-04-11 | Dev | | 5 | Raju | 2014-01-10 | Qa Engineer | | 6 | Mahipal | 2015-01-15 | Dev | | 7 | Manoj | 2015-09-14 | Dev | | 8 | Ragvendra | 2015-06-05 | Senior Test Engineer | | 9 | Sundheer | 2015-01-09 | Test Eng. Enineer | | NULL | NULL | NULL | IT-Support | | NULL | NULL | NULL | DBA | | 1 | Ajay | 2015-01-11 | Dev | | 2 | Anji | 2015-01-05 | QA | | 3 | Sabhapathi | 2015-11-11 | Senior Soft Engineer | | 4 | Bhasker | 2015-04-11 | Dev | | 5 | Raju | 2014-01-10 | Qa Engineer | | 6 | Mahipal | 2015-01-15 | Dev | | 7 | Manoj | 2015-09-14 | Dev | | 8 | Ragvendra | 2015-06-05 | Senior Test Engineer | | 9 | Sundheer | 2015-01-09 | Test Eng. Enineer | | 10 | Mahi | 2015-01-1 | NULL | | 11 | Vijay | 2015-01-05 | NULL | | 12 | Sandip | 2015-01-1 | NULL | +-------+------------+------------+----------------------+ 23 rows in set (0.00 sec) INNER JOIN gets all records from one table that have some related or matching records from second table LEFT JOIN gets all records from the LEFT Side linked table but if you have selected some columns from the RIGHT table, if there is no related records, these columns will contain NULL RIGHT JOIN is like the above but gets all records in the RIGHT table FULL JOIN gets all records from both tables and puts NULL in the columns where related records do not exist in the opposite table
Comments
Post a Comment