Answer»
Inner Join: This join generates datasets that contain matching records in both tables (left and right). By using an inner join, only the rows that match between each of the tables are returned; all non-matching rows are removed.
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 perform INNER JOIN on these two tables using a SELECT statement, as shown below:
SELECT Emp_Name, Emp_No, Emp_Profile, Emp_Country, Emp_Join_Date
FROM Tb1_Employee INNER JOIN Tb2_Employment
ON Tb1_Employee.Emp_ID=Tb2_Employment.Emp_ID;Output:
| Emp_Name | Emp_No | Emp_Profile | Emp_Country | Emp_Join_Date |
|---|
| Ashish Kaktan | 9450425345 | Content Writer | Germany | 2021-04-20 | | Shantanu Khandelwal | 9020330023 | Data Analyst | India | 2022-12-11 | | Khanak Desai | 8451004522 | Software Engineer | India | 2022-01-03 |
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 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_Country
FROM Tb1_Employee LEFT JOIN Tb2_Employment
ON Tb1_Employee.Emp_ID=Tb2_Employment.Emp_ID;Output:
| Emp_Name | Emp_No | Emp_Profile | Emp_Country |
|---|
| Ashish Kaktan | 9450425345 | Content Writer | Germany | | Raj Choudhary | 8462309621 | Null | Null | | Vivek Oberoi | 7512309034 | Null | Null | | Shantanu Khandelwal | 9020330023 | Data Analyst | India | | Khanak Desai | 8451004522 | Software Engineer | India |
|
