SQL Server - Joins

In Structured Query Language (SQL), a JOIN is a method of linking two data tables in a single query, allowing the database to return a set that contains data from both tables at once, or using data from one table to be used as a Filter on the second table. There are several types of JOINs defined within the ANSI SQL standard.

Here explains how to use JOINS, both INNER and OUTER JOINS, in SQL Server (Transact-SQL) with syntax, visual illustrations, and examples.
SQL Server (Transact-SQL) JOINS are used to retrieve data from multiple tables.

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

There are many types of join.

• Inner Join

1. Equi-join
2. Natural Join

• Outer Join

1. Left outer Join
2. Right outer join
3. Full outer join

• Cross Join
• Self Join

Using the Code
Join is very useful to fetching records from multiple tables with reference to common column between them.

To understand join with example, we have to create two tables in SQL Server database.

1. Employee

create table Employee(

id int identity(1,1) primary key,
Username varchar(50),
FirstName varchar(50),
LastName varchar(50),
DepartID int

) 

2. Departments

create table Departments( 
id int identity(1,1) primary key,
DepartmentName varchar(50)

) 
Now fill Employee table with demo records like that.

Fill Department table also like this....

Equi-Join
Equi join is a special type of join in which we use only equality operator. Hence, when you make a query for join using equality operator, then that join query comes under Equi join.
Equi join has only (=) operator in join condition.
Equi join can be inner join, left outer join, right outer join.
Check the query for equi-join:

SELECT * FROM Employee e1 JOIN Departments e2 ON e1.DepartID = e2.id

2) Outer Join

Outer join returns all the rows of both tables whether it has matched or not.

We have three types of outer join:

Left outer join
Right outer join
Full outer join

a) Left Outer join
Left join displays all the rows from first table and matched rows from second table like that..

 SELECT * FROM Employee e1 LEFT OUTER JOIN Departments e2
ON e1.DepartID = e2.id 
Result:

b) Right outer join
Right outer join displays all the rows of second table and matched rows from first table like that.

SELECT * FROM Employee e1 RIGHT OUTER JOIN Departments e2
ON e1.DepartID = e2.id
Result:

3) Full outer join
Full outer join returns all the rows from both tables whether it has been matched or not.

 SELECT * FROM Employee e1 FULL OUTER JOIN Departments e2
ON e1.DepartID = e2.id 

Result:

3) 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 like this.

SELECT * FROM Employee cross join Departments e2
You can write a query like this also:

SELECT * FROM Employee , Departments e2

4) Self Join
Joining the table itself called self join. Self join is used to retrieve the records having some relation or similarity with other records in the same table. Here, we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.

SELECT e1.Username,e1.FirstName,e1.LastName from Employee e1 _<br />inner join Employee e2 on e1.id=e2.DepartID

Here, I have retrieved data in which id and DepartID of employee table has been matched:

Points of Interest
Here, I have taken one example of self join in this scenario where manager name can be retrieved by managerid with reference of employee id from one table.

Here, I have created one table employees like that:

If I have to retrieve manager name from manager id, then it can be possible by Self join:

select e1.empName as ManagerName,e2.empName as EmpName _<br />from employees e1 inner join employees e2 on e1.id=e2.managerid

Result;