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 two tables based on some conditions. Joins are one of the most important concepts of SQL Server. We can retrieve data from multiple tables using Joins.
Classification Of Joins in SQL
When we retrieve the data from multiple tables with one keyword condition then this is called ANSI format join.
When we retrieve data from multiple tables based on where keyword condition then it is called NON-ANSI format Joins.
Note
Inner Join is the default Join in Joins.
Syntax of Joins
Select */<List of column> from Tablename1 <Join>Tablename2 on/where (Joins Condition)
Let us understand Joins with an example.
Create Two Tables in SQL
Create Table customer(Id int,Name varchar(50),Mobile int,CustomerId int)
Create Table company(Companyid int, CompanyName varchar(50),Salary decimal(6,2))
Insert some data into Tables in SQL
insert into customer values(1,'A',89898989,101)
insert into customer values(2,'B',78787877,102)
insert into customer values(3,'C',98323232,103)
insert into company values(101,'C1 Tech',5000)
insert into company values(102,'Cone Tech',1000)
insert into company values(104,'Netvision Tech',3500)
insert into company values(105,'S Tech',1500)
Equi Join in SQL
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
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 names in two levels,
- Column-level alias name
- 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 in SQL
Inner Join is used for retrieving data from multiple Tables. When we use inner Join we should use a common column name and the datatype is also the same in the Table.
Example
select * from customer c inner join company com on c.CustomerId=com.Companyid
Result
Outer Join in SQL
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 unmatch data from the tables at the same time.
Type of Outer Join in SQL
Left Outer Join in SQL
It retrieves matching data from multiple tables and also unmatches data from the left-hand side table only.
Right Outer Join in SQL
It retrieves matching data from multiple tables and also unmatches data from the right-hand side table only.
Example of Left Outer Join
Write a query to display matching data from the customer, and company and unmatch data from the customer table.
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 the customer and company and unmatch data from the company table.
select * from customer c right outer join company com on c.CustomerId=com.Companyid
Cross Join in SQL
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
select * from customer cross join company
![]()
Non-Equi Join in SQL
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:
create table employee (id int, name varchar(50),salary int ,Mid int)
create table salary_Range(sno int,Lowsal int,Highsal int)
insert into employee values(1,'A',2500,101)
insert into employee values(2,'B',3200,108)
insert into employee values(3,'C',400,103)
insert into employee values(4,'D',8200,109)
insert into employee values(5,'E',1100,101)
INSERT INTO salary_Range VALUES(1,1500,2800)
INSERT INTO salary_Range VALUES(2,3500,5200)
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.
select * from employee,salary_Range where (salary>Lowsal)and (salary<Highsal)
Result
Self Join SQL
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 in SQL
Natural Join is used for avoiding duplicate columns from a Resultset.
Example
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
Select */<Column name> from <Table1> <Join Key><Table2> On <condition> <Join key> <Table3> on <Condition>
Create a Table
create table Reg(Regno int,Regdate datetime,cid int)
insert into Reg values(1,'1-3-2017',101)
insert into Reg values(1,'12-4-2017',102)
insert into Reg values(1,'9-6-2017',108)
Example
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.