Types of Joins in SQL Server

Introduction

This article will teach us about various types of Joins in SQL Server. We use the SQL JOIN clause to combine rows from two or more database tables based on a common field.

Joins in SQL Server

Join means combining fields from two or more tables by having common values in each relational database table. SQL INNER JOIN is the most common and a simple join used mostly. Find a detailed article about Joins in SQL Server here: Joins in SQL Server

Types of Join in SQL Server

There are four types of joins in SQL Server.

Inner join returns all rows when at least one match exists in both tables.

Example 1. I have two tables, Students and Employees.

Example 2. Here I'm using the where clause.

Outer Join

It is categorized into three types.

Left Join returns all rows from the left table and the matched rows from the right table. The result will be NULL on the right side when no match exists.

Syntax

SELECT 
  column_name(s) 
FROM 
  table1 
  LEFT OUTER JOIN table2               ON table1.column_name = table2.column_name;

Example 1. The output will have NULL values when there is no match to the right table. Here's the image,

Example 2. In the following example, Left Table is Persons, and the right table is Trainee1; now all the left table values are combined to the matched values of the right side table. When the left table is not compared to the right side table values, the result is NULL.

Right Join

Return all rows from the right table and the matched rows from the left table.

Syntax

SELECT 
  column_name(s) 
FROM 
  table1 
  RIGHT JOIN table2                   
ON table1.column_name = table2.column_name;

Example. We have a left table, Persons, and a Right table, Trainee1; now, in the right outer join, the right table columns will combine to the matched values of the left table.

Full Join

Return all rows from both tables; when there is a match in one of the tables, it acts as a combination of both left and right join.

Syntax 

SELECT 
  column_name(s) 
FROM 
  table1 FULL 
  OUTER JOIN table2                    
ON table1.column_name = table2.column_name;

Example

Self-Join useful

A self-join helps join a table to itself; Self Join means that each row of the table is combined with itself and with every other row of the table that we have.

Syntax

SELECT 
  a.column_name, 
  b.column_name...
FROM 
  table1 a, 
  table1 b               
WHERE 
  a.common_filed = b.common_field;

Example

Cross Join

It returns the Cartesian product of rows from tables in the join. In other words, it will produce rows combining each row from the first table with each from the second table. 

Example. I have two tables, Students and employees. The Cross Join result will be 45(5*9).

Conclusion

This article taught us about different types of Joins in SQL Server. Reading more about Joins in SQL Server Please go through this: Joins in SQL Server.