Answer»
Left Join: It returns datasets that have matching records in both tables (left and right) plus non-matching rows from the left table. By using a left join, all the records in the left table plus the matching records in the right table are returned.
Example: Let's take a look at two tables. Here’s the Tb1_Employee table.
| Emp_ID | Emp_Name | Emp_No |
|---|
| 101 | Ashish Kaktan | 9450425345 | | 102 | Raj Choudhary | 8462309621 | | 103 | Vivek Oberoi | 7512309034 | | 104 | Shantanu Khandelwal | 9020330023 | | 105 | Khanak Desai | 8451004522 |
Here's the Tb2_Employment table.
| Emp_ID | Emp_Profile | Emp_Country | Emp_Join_Date |
|---|
| 101 | Content Writer | Germany | 2021-04-20 | | 104 | Data Analyst | India | 2022-12-11 | | 105 | Software Engineer | India | 2022-01-03 | | 108 | Development Executive | Europe | 2023-02-15 | | 109 | Marketing Manager | Mexico | 2020-05-23 |
Let’s now perform LEFT JOIN on these two tables using a SELECT statement, as shown below:
SELECT Tb1_Employee.Emp_Name, Tb1_Employee.Emp_No, Tb2_Employment.Emp_Profile, Tb2_Employment.Emp_Join_Date
FROM Tb1_Employee LEFT JOIN Tb2_Employment
ON Tb1_Employee.Emp_ID=Tb2_Employment.Emp_ID;Output:
| Emp_Name | Emp_No | Emp_Profile | Emp_Join_Date |
|---|
| Ashish Kaktan | 9450425345 | Content Writer | 2021-04-20 | | Raj Choudhary | 8462309621 | Null | Null | | Vivek Oberoi | 7512309034 | Null | Null | | Shantanu Khandelwal | 9020330023 | Data Analyst | 2023-02-15 | | Khanak Desai | 8451004522 | Software Engineer | 2020-05-23 |
Right Join: It returns datasets that have matching records in both tables (left and right) plus non-matching rows from the right table. By using a left join, all the records in the right table plus the matching records in the left table are returned.
Example: Let’s now perform RIGHT JOIN on these two tables using a SELECT statement, as shown below:
SELECT Tb1_Employee.Emp_Name, Tb1_Employee.Emp_No, Tb2_Employment.Emp_Profile, Tb2_Employment.Emp_Join_Date
FROM Tb1_Employee RIGHT JOIN Tb2_Employment
ON Tb1_Employee.Emp_ID=Tb2_Employment.Emp_ID;Output:
| Emp_Name | Emp_No | Emp_Profile | Emp_Join_Date |
|---|
| Ashish Kaktan | 9450425345 | Content Writer | 2021-04-20 | | Shantanu Khandelwal | 9020330023 | Data Analyst | 2022-12-11 | | Khanak Desai | 8451004522 | Software Engineer | 2022-01-03 | | Null | Null | Development Executive | 2023-02-15 | | Null | Null | Marketing Manager | 2020-05-23 |
|
