In Focus

Joins in SQL Server 2012

In this article, I describe Joins, Inner Join, Outer Join, Cross Join, Self Join in SQL Server 2012.

 

Introduction


In this article I describe joins, types of joins, Inner Joins, Left Outer Joins, Right Outer Joins, Full Outer Joins, Cross Joins and Self Joins with examples.

Joins


Joins are used to relate one or more tables in SQL Server. Joins are a part of a SQL Statement that retrieves rows from a table or tables according to specified conditions.

Types of Joins

 

  1. Inner Join
  2. Outer Join
  3. Cross Join
  4. Self Join

First we create two tables on which we apply the joins.

Creation of the first table is as

  1. create table emp(empId int, empName varchar(15))

Insertion of data into the table

  1. insert into emp  
  2. select 1,'deepak'union all  
  3. select 2,'Arora'union all  
  4. Select 3,'raj'union all  
  5. select 4,'Mahi'union all  
  6. select 5,'daljeet'union all  
  7. select 6,'kiran'  

Output

  1. select * from emp 

join-in-sql-emp.jpg

Creation of second table

  1. create table emp_add(empId int, empAdd varchar(25))  

insertion of data

  1. select 1,'Lakser'union all  
  2. select 2,'haridwar'union all  
  3. select 3,'usa'union all  
  4. select 7,'canada'union all  
  5. select 8,'punjab'union all  
  6. select 9,'Chandigarh'  
Output
  1. select * from emp_add

join-in-sql-emp-add.jpg

Inner Join


It return all the Rows that satisfy the join Condition. Inner join produces records that match in Tables.

 

  1. select e.empId,e.empName,e1.empAdd from emp e inner join emp_add e1 on e.empId=e1.empId   

Output

Inner Join in SQL Server

Outer Join


There are three types of Outer Join

 

  1. Left Outer Join
  2. Right Outer Join
  3. Full Outer Join

 

Left Outer Join


The result of the Left Outer Join contains all the records of the left table and if any record of the left table does not match the right table than it returns Null for the right side table.

 

  1. select e.empId,e.empName,e1.empAdd from emp e left outer join emp_add e1 on e.empId=e1.empId  
Output

Left Outer Join in SQL Server

Right Outer Join


The result of the Right Outer Join contains all the records of the right table and if any record of the right table does not match the Left table than it returns Null for the left side table.

 

  1. select e.empId,e.empName,e1.empAdd from emp e right outer join emp_add e1 on e.empId=e1.empId   

Output

Right Outer Join in SQL Server

Full Outer Join


A Full Outer Join fetches all records of both tables; where the record does not match, it returns Null.

 

  1. select e.empId,e.empName,e1.empAdd from emp e full outer join emp_add e1 on e.empId=e1.empId  
Output

Full Outer Join in SQL Server

Cross Join


This join is a Cartesian join. The result of a Cross Join contains records that are the multiplication of the records from both tables.

 

  1. select e.empId,e.empName,e1.empAdd from emp e cross join emp_add e1  
Output

Cross Join in SQL Server

Here I create a table to explain self join

  1. create table emp_mngr(empName varchar(15),mngrName varchar(15))  
Insertion of data

  1. insert into emp_mngr  
  2. select 'ravi','Gaurav'union all  
  3. select'Gaurav','tom'union all  
  4. select 'sem','singh'union all  
  5. select 'singh','arora  

Output

  1. select * from emp_mngr

join-in-sql-emp_mngr.jpg

 

Self Join


In the Self Join a table is joined to itself. A Self Join can be an Inner Join or Outer Join. In the given example we find the employees that are the manager of other employees.

 

  1. select e.empName,e.mngrName from emp_mngr e inner join emp_mngr e1 on e.empName=e1.mngrName  
Output

Self Join in SQL Server

Summary


In this article, I described joins in SQL Server. I hope this article has helped you in understanding this topic. Please share it. If you know more about this, your feedback and constructive contributions are welcome.