Day-19) joins in sql server with interview questions in hindi|SQL training
| SQL Tutorial for beginners| Intellipaat
Outer Join in SQL Server
The OUTER JOIN in SQL Server returns matched data rows as well as unmatched data rows from both the tables involved in the join. Outer join in SQL Server is again classified into three types as follows.
1) Left outer join
2) Right outer join
3) Full outer join
Left Outer Join in SQL Server
The LEFT OUTER JOIN in SQL Server is used to retrieve all the matching rows from both the tables involved in the join as well as non-matching rows from the left side table. In this case, the un-matching data will take a null value.
The question that should come to your mind is, which is the left table and which is the right table? The answer is, the table mentioned to the left of the LEFT OUTER JOIN keyword is the left table, and the table mentioned to the right of the LEFT OUTER JOIN keyword is the right table. The following diagram is the pictorial representation of SQL Server Left Outer Join.
Example:
SELECT Id as EmployeeID, Name, Department, City, Title as Project, ClientId
FROM Employee
LEFT OUTER JOIN Projects
ON Employee.Id = Projects.EmployeeId;
Right Outer Join in SQL Server
The RIGHT OUTER JOIN in SQL Server is used to retrieve all the matching rows from both the tables involved in the join as well as non-matching rows from the right-side table. In this case, the un-matching data will take NULL values.
Example:
SELECT Id as EmployeeID, Name, Department, City, Title as Project, ClientId
FROM Employee
RIGHT OUTER JOIN Projects
ON Employee.Id = Projects.EmployeeId;
Full Outer Join in SQL Server
The Full Outer Join in SQL Server is used to retrieve all the matching records as well as all the non-matching records from both the tables involved in the JOIN. The Un-matching data in such cases will take the NULL values. The following diagram shows the pictorial representation of Full Outer Join in SQL Server.
Example:
SELECT Id as EmployeeID, Name, Department, City, Title as Project, ClientId
FROM Employee
FULL OUTER JOIN Projects
ON Employee.Id = Projects.EmployeeId;