Joins In SQL Server

In this article, we will learn about Joins in SQL Server. I will explain all SQL Joins with an example.

Introduction

Joins are used to fetch data from two or more than two tables based on some conditions. Joins is one of the most important concepts of SQL Server. We can retrieve data from multiple tables using Joins.

Classification Of Joins

 
When we retrieve the data from multiple tables with on keyword condition then this is called as ANSI format joins.

When we retrieve data from multiple tables based on where keyword condition then it is called as NON-ANSI format Joins.

Note
Inner Join is the default Join in Joins.

Syntax of Joins

  1. Select */<List of columnfrom Tablename1 <Join>Tablename2 on/where (Joins Condition)  
Let us understand Joins with an example.

Create Two Tables

  1. Create Table customer(Id int,Name varchar(50),Mobile int,CustomerId int)  
  2.   
  3. Create Table company(Companyid int, CompanyName varchar(50),Salary decimal(6,2))  

Insert some data into these Tables,

  1. insert into customer values(1,'A',89898989,101)  
  2. insert into customer values(2,'B',78787877,102)  
  3. insert into customer values(3,'C',98323232,103)  
  4.   
  5. insert into company values(101,'C1 Tech',5000)  
  6. insert into company values(102,'Cone Tech',1000)  
  7. insert into company values(104,'Netvision Tech',3500)  
  8. insert into company values(105,'S Tech',1500)  

Equi Join

When we retrieve the data from multiple tables based on an equality condition then this is called an Equi Join.

This Join process supports only one operator equal Operator (=)

When we use Equi Join we should maintain a common column name and that column should contain the same data type.

Example 
  1. select c.id,c.name,c.mobile,c.CustomerId,com.Companyid,com.CompanyName,com.Salary from customer c, company com where c.CustomerId=com.Companyid  

Result

 

Alias Name

Alias Name is a duplicate or Alternative Name.

We can define alias name in two levels,

  1. Column-level alias name
  2. Table-level alias name

When we create a duplicate name for a column then it is called column level alias name.

Syntax <Column Name> AS <column alias Name> 

When we create the alternative name for the tables in the database then it is called table level alias name.

Syntax: <Table Name> AS <Table alias Name>

Note

Alias names are mostly implemented in Joins.

Inner Join

Inner Join is used for retrieving data from multiple Tables. When we use inner Join we should use a common column name and datatype is also same in the Table.

Example 
  1. select * from customer c inner join company com on c.CustomerId=com.Companyid  

Result

 

Outer Join

Outer Join is an extension of the Inner Join.

In Inner Join mechanisms, the user will get matching data from the Tables and leave unmatching data from the Tables.

To overcome the drawback we use the Outer Join. By using outer we can retrieve matching data and also unmatching data from the tables at the same time.

Type of Outer Join

 

Left Outer Join

It retrieves matching data from multiple tables and also unmatching data from the left-hand side table only.

Right Outer Join

It retrieves matching data from multiple tables and also unmatching data from the right-hand side table only.

Example of Left Outer Join

Write a query to display matching data from the customer, company and unmatching data from the customer table.

  1. select * from customer c left outer join company com on c.CustomerId=com.Companyid  
Result

 

Example of Right Outer Join

Write a query to display matching data from customer and company and unmatching data from company table.

  1. select * from customer c right outer join company com on c.CustomerId=com.Companyid  
 

Cross Join

When we Join the two table's information without any condition  it is known as Cross Join

In the Cross Join mechanism, each record of a first Table is joined with each record of the second Table.

For example, if the first Table contains A number of records and the second Table contains B number of records then we will get the cross product A*B records.

Example
  1. select * from customer cross join company  

 

Non-Equi Join

When we retrieve the data from multiple tables based on any condition except an Equality condition, it is known as Non-Equi Join.

When we implement Non-Equi Join there is no requirement to maintain a common column in the Table.

It supports all operators. To understand this let us create two tables and insert values:

  1. create table employee (id intname varchar(50),salary int ,Mid int)  
  2.   
  3. create table salary_Range(sno int,Lowsal int,Highsal int)  
  4.   
  5. insert into employee values(1,'A',2500,101)  
  6. insert into employee values(2,'B',3200,108)  
  7. insert into employee values(3,'C',400,103)  
  8. insert into employee values(4,'D',8200,109)  
  9. insert into employee values(5,'E',1100,101)  
  10.   
  11. INSERT INTO salary_Range VALUES(1,1500,2800)  
  12. INSERT INTO salary_Range VALUES(2,3500,5200)  
  13. INSERT INTO salary_Range VALUES(3,6500,8500)  

Example: Write a Query to retrieve employee details whose salary is greater than the lowest and less than the highest salary.

  1. select * from employee,salary_Range where (salary>Lowsal)and (salary<Highsal)  

Result

 

Self Join

A Table joining by itself is known as a Self Join.

Self Join can be implemented when any 2 columns have some relationship within the same Table.

It can be worked on a single Table only.

When we use Self Join on a Table then we should create alias names for the Table.

Without alias names, we cannot implement Self Join.

A Table contains any number of alias names.

Natural Join

Natural Join is used for avoiding the duplicate columns from a Resultset.

Example
  1. select id,name,mobile,customerid,companyname ,salary from customer c ,company com where c.customerid=com.companyid  
Result

 

Syntax to Join more than two tables
  1. Select */<Column namefrom <Table1> <Join Key><Table2> On <condition> <Join key> <Table3> on <Condition>  
Create a Table
  1. create table Reg(Regno int,Regdate datetime,cid int)  
  2.   
  3. insert into Reg values(1,'1-3-2017',101)  
  4. insert into Reg values(1,'12-4-2017',102)  
  5. insert into Reg values(1,'9-6-2017',108)  
Example
  1. select * from customer c ,company com,Reg r where c.customerid=com.companyid and com.companyid=r.cid  
Result

 

Summary

In this article I have explained Join in SQL with examples. This article is very helpful for beginners.