Joins in sql server | Inner Join,Left Join,Right Join,Full Outer Join,Cross Join and Self Join

Learn what is sql server joins and different type of joins:- Inner Join,Left Outer Join,Right Outer Join,Full Outer Join,Cross Join and Self Join. Microsoft SQL Server Online Training For Beginners With Advance Concepts

https://sekhar.thinkific.com/courses/...

Learn at Udemy

https://www.udemy.com/microsoft-sql-s...

Follow Us On

  / way2achieve  

How to install sql server 2017
   • How to install sql server 2017 - SQL ...  

Database

   • How to Create Database in SQL Server ...  


A JOIN clause in sql server is used to combine rows from two or more tables, based on a related column between them.

Different type of joins in SQL Server 2017

Inner Join
Returns records that have matching values in both tables.

SELECT E.EmployeeId,E.EmployeeName,E.DepartmentId,D.DepartmentName FROM Employee E INNER JOIN
Department D ON E.DepartmentId=D.DepartmentId;

Left Outer Join
Return all records from the left table, and the matched records from the right table.On the right table, the matching data is returned in addition to NULL values where a record exists in the left table, but not in the right table.

SELECT E.EmployeeId,E.EmployeeName,E.DepartmentId,D.DepartmentName FROM Employee E LEFT OUTER JOIN
Department D ON E.DepartmentId=D.DepartmentId;


Right Outer Join
Return all records from the right table, and the matched records from the left table. On the left table, the matching data is returned in addition to NULL values where a record exists in the right table but not in the left table.

SELECT E.EmployeeId,E.EmployeeName,E.DepartmentId,D.DepartmentName FROM Employee E RIGHT OUTER JOIN
Department D ON E.DepartmentId=D.DepartmentId;

Full Outer Join
Return all records when there is a match in either left or right table.

SELECT E.EmployeeId,E.EmployeeName,E.DepartmentId,D.DepartmentName FROM Employee E FULL OUTER JOIN
Department D ON E.DepartmentId=D.DepartmentId;

Cross Join
A cross join that produces Cartesian product of the tables that are involved in the join. The size of a Cartesian product is the number of the rows in the first table multiplied by the number of rows in the second table.

SELECT E.EmployeeId,E.EmployeeName,E.DepartmentId,D.DepartmentName FROM Employee E CROSS JOIN
Department D;


Self Join
In this circumstance, the same table is specified twice with two different aliases in order to match the data within the same table.

SELECT E.EmployeeId,E.EmployeeName,E.DepartmentId,E.ManagerId,D.EmployeeName ManagerName FROM Employee E JOIN
Employee D ON E.ManagerId=D.EmployeeId;

   • Видео