What are Joins?
Joins enable us to retrieve columns from more than one table at a time as one result set. The relationship between the tables is not mandatory.
- There are multiple types of joins
- Inner Join
- Left Join
- Right Join
- Outer Join
- Cross Join
- Self Join
Observe Closely the two table
SELECT * FROM Student SELECT * FROM Courses
Output:

Let’s retrieve data from these two tables using joins
Inner Join in SQL Server
Inner join retrieves only the rows that matched in both tables or the join condition.
SELECT Course.*,std.* FROM Student Course INNER JOIN Courses std ON Course.CourseID = std.CourseID
Output:

We can also use WHERE Clause in the query such as
SELECT Course.*,std.* FROM Student Course INNER JOIN Courses std ON Course.CourseID = std.CourseID where std.CourseID<3
Left Outer Join in SQL Server
Left Join is used to retrieve all rows from the first table and only matched rows from the second table
SELECT Course.*,std.* FROM Student Course LEFT JOIN Courses std ON Course.CourseID = std.CourseID
Output:

Right Outer Join in SQL Server
Right Join is used to retrieve all rows from the second table and only matched rows from the first table. Right Join is the opposite of left join.
SELECT Course.*,std.* FROM Student Course RIGHT JOIN Courses std ON Course.CourseID = std.CourseID
Output:

Full Outer Join in SQL Server
Full outer Join retrieves rows all rows from both tables either matched or not matched. Full Outer Join is helpful in finding unmatched rows where the relationship is not applied between tables.
SELECT Course.*,std.* FROM Student Course FULL OUTER JOIN Courses std ON Course.CourseID = std.CourseID
Output:

Cross Join in SQL Server
Cross Joins retrieves the cross/cartesian product of the table rows. Where each item of the second table is displayed with each item of the first table.
SELECT Course.*,std.* FROM Student Course CROSS JOIN Courses std
Self Join in SQL Server
Self Join query is applied on a single table that references a record within itself. Observe the following table. It contains Employee data as well as Manager ID which is Employee ID.

Example 1: Find Employee vs Managers
SELECT e1.Name EmployeeName, e2.name AS ManagerName FROM Employee e1 INNER JOIN Employee e2 ON e1.ManagerID = e2.EmployeeID
Output:

Example 2: Find the top manager
SELECT e1.Name EmployeeName, ISNULL(e2.name, 'Top Manager') AS ManagerName FROM Employee e1 LEFT JOIN Employee e2 ON e1.ManagerID = e2.EmployeeID GO
Output:

Support us by sharing this post