Joins in SQL Server
As the growing software/IT industry applications are developed in various programming languages like PHP, Java, Asp.net, Asp.net core, Asp.Net MVC, Javascript(KendoUI), Angular.Js, Node.js, Android, IOS etc., the database plays a major role in interacting with the User Interface (UI) of the application.
As these applications are developed in various programming languages, the UI of the application interacts with the database and fetches the records from the tables and displays them to the user when the user requests it.
Each programming language like PHP, Asp.net, or Java interacts with their own databases:
- Applications developed using PHP interact with MySql database.
- Applications developed using Java interact with Oracle database.
- Application developed using Asp.net interacts with Microsoft's Sql Server database. etc.
As these databases are RDBMS (Relational Database Management Systems) ,the data will be maintained in multiple tables and we can maintain/establish the connection/relationship between the tables by taking the primary key in the first table and the foreign key in the second table and so on.
To perform the connection/relationship between both the tables, each table must have a common column. Let's say we have a first table (Employee table) with the columns EmployeeId, EmployeeName and Employee Designation; and we have a second tabl (SalaryIncrement table) with the columns EmployeeId and IncrementSal. In these two tables Employee table and Salary Increment table we have a common column name, EmployeeId.
For EmployeeId in Employee table we make it the primary key and for EmployeeId in SalaryIncrement table we make it the foreign key.
From the above tables (Employee table and SalaryIncrement table), to know the employee name whose salary increased by 25 percent, we can retrieve the record by joining both the tables, so we need to use the joins concept.
JOINS
Joins are used for joining both the tables with the relationship as the primary key in the first table and the foreign key in the second table and retrieving the data from more than one table. We can perform joins on more than one table.
A common column must exist between both the tables. From the above example EmployeeId is the common column from the two tables. With a single Select query we can retrieve the records from more than one table.
Types of JOINS
Different types of Joins in Sql Server are
- Inner join
- Outer Join
- Left outer join
- Right outer join
- Full outer join.
- Cross join.
- Self join
- Equi join
- Non equi join.
Inner join
Inner join retrieves all the rows / Records from both the tables which have matching records in both tables. [OR] Inner join is used to retrieve the matching rows/records from more than one table by joining both the tables by using "Join clause" & "on clause".
![Inner join]()
From the above figure the highlighted orange color is the matching one from both the tables.
Query for creating tables and Inserting rows.
- create table Employee(EmployeeNo int, EmployeeName varchar(10), EmployeeSalary money)
- Insert into Employee(EmployeeNo, EmployeeName, EmployeeSalary)
- Values(1201, 'khaja', 10, 000), (1202, 'moiz', 20, 000), (1203, 'ajay', 15, 000), (1204, 'waseem', 25, 000)
- create table Department(DepartmentNo int, DepartmentName varchar(15), EmployeeNo int)
- Insert into Department(DepartmentNo, DepartmentName, EmployeeNo)
- values(100, 'IT', 1201), (200, 'Mathematics', 1202), (300, 'Physics', 1203), (400, 'Chemistry', 1205)
Employee Table
| EmployeeNo | EmployeeName |
EmployeeSalary |
| 1201 |
khaja |
10,000 |
| 1202 |
moiz |
20,000 |
| 1203 |
ajay |
15,000 |
| 1204 |
waseem |
25,000 |
Department Table
| DepartmentNo | DepartmentName |
EmployeeNo |
| 100 |
IT |
1201 |
| 200 |
Mathematics |
1202 |
| 300 |
Physics |
1203 |
| 400 |
Chemistry |
1205 |
Syntax for Inner Join
Select table1.column1,table1.column2, table1. column3.....,table2, column1,table2.column2,table3.column3,...............from table 1 inner join table 2 on table1.CommonColumn=table2.commonColumn.
Different ways of retrieving records from Employee Table and Department table.
Select Employee.EmployeeNo,Employee.EmployeeName,Employee.EmployeeSalary,Department.DepartmentNo,
Department.DepartmentName from Employee inner join Department on Employee.EmployeeNo=Department.EmployeeNo
(OR)
Select Employee.*,Department.DepartmentNo,Department.DepartmentName from Employee inner join Department on Employee.EmployeeNo=Department.EmployeeNo
Instead of retrieving the data with the help of table names we can use alias names like for employee table as (e) and department table as (d), we can retrieve the records by using their alias names as e and d as shown below.
Select e.EmployeeNo,e.EmployeeName,e.EmployeeSalary,d.DepartmentNo,d.DepartmentName from Employee e inner join Department d on e.EmployeeNo=d.EmployeeNo
(OR)
Select e.*,d.DepartmentNo,d.DepartmentName from Employee e inner join Department d on e.EmployeeNo=d.EmployeeNo
In the above Select statement , instead of inner join we can directly use join clause as joins are by default inner join.
Example
Select Employee.EmployeeNo,Employee.EmployeeName,Employee.EmployeeSalary,Department.DepartmentNo,
Department.DepartmentName from Employee join Department on Employee.EmployeeNo=Department.EmployeeNo
(OR)
Select Employee.*,Department.DepartmentNo,Department.DepartmentName from Employee join Department on Employee.EmployeeNo=Department.EmployeeNo
(OR)
Select e.EmployeeNo,e.EmployeeName,e.EmployeeSalary,d.DepartmentNo,d.DepartmentName from Employee e join Department d one.EmployeeNo=d.EmployeeNo
(OR)
Select e.*,d.DepartmentNo,d.DepartmentName from Employee e join Department d on e.EmployeeNo=d.EmployeeNo
The result of the above query is
| 1201 |
khaja |
10,000 |
IT |
| 1202 |
moiz |
20,000 |
Mathematics |
| 1203 |
ajay |
15,000 |
Physics |
The above result shows the records which are common in both the tables.
OUTER JOIN
- Left Join/Left outer join
Left outer join is used to join both the tables and retrieve all the rows/records from the left table and matching rows/records from the right table.
If the table on the right side doesn't contains the matching rows with the left table then it displays with the null values in the right table.
![]()
Query for Left outer join
Select e.EmployeeNo,e.EmployeeName,e.EmployeeSalary,d.DepartmentNo,d.DepartmentName from Employee e Left join Department d on e.EmployeeNo=Department.EmployeeNo
OR
Select e.*,d.DepartmentNo,d.DepartmentName from Employee e Left join Department d on e.EmployeeNo=Department.EmployeeNo
The result of the above query is
| 1201 |
khaja |
10,000 |
100 |
IT |
| 1202 |
moiz |
20,000 |
200 |
Mathematics |
| 1203 |
ajay |
15,000 |
300 |
Physics |
| 1204 |
waseem |
25,000 |
null |
null |
- Right outer join
It is quite opposite to left outer join. Right outer join is used to join both the tables and retrieves all the rows/records from the right table and retrieves the matching rows/records from the left table.
If the table on the left side doesn't contains the matching rows with the right table then it displays with the null values in the left table.
![]()
Query for right outer join
Select e.EmployeeNo,e.EmployeeName,e.EmployeeSalary,d.DepartmentNo,d.DepartmentName from Employee e right join Department d on e.EmployeeNo=Department.EmployeeNo
(OR)
Select e.*,d.DepartmentNo,d.DepartmentName from Employee e right join Department d
on e.EmployeeNo=Department.EmployeeNo
The result of the above query is
| 1201 |
khaja |
10,000 |
100 |
IT |
| 1202 |
moiz |
20,000 |
200 |
Mathematics |
| 1203 | ajay | 15,000 | 300 | Physics |
| null |
null |
null |
400 |
Chemistry |
- Full outer join
Full outer join is used to join both the tables and retrieve all the rows/records from both the tables.
If the table on the left side does not contain matching records with the right table then it displays null values in the left table, similarly if the table on the right side does not contain matching records with the left table then it displays null values in the right table.
![Full outer join]()
Query for Full outer join
Select e.* ,d.* from Employee e full join Department d on e.EmployeeNo=d.EmployeeNo.
(OR)
Select e.EmployeeNo,e.EmployeeName,e.EmployeeSalary,d.DepartmentNo,d.DepartmentName from Employee e full join Department d on e.EmployeeNo=d.EmployeeNo
The result of the above query is
| 1201 |
khaja |
10,000 |
100 |
IT |
| 1202 |
moiz |
20,000 |
200 |
Mathematics |
| 1203 |
ajay |
15,000 |
300 |
Physics |
| null |
null |
null |
null |
null |
Cross join
Cross join is nothing but a product/multiplication of both the tables. If the table on the left side contains 'x' rows/records and table on the right side contains 'y' rows/records then the product of both the tables are calculated and the result of both the tables are 'X (x) Y'.
In cross join each row/record of the left table compares/joins with each row of the right table and the result is calculated.
Example
If the table on the left side contains six rows/records and the table on the right side contains six rows then the product of both the tables' result is 36 rows.
| CustomerId | CustomerName |
CustomerAddress |
| 1200 |
khaja |
Hyderabad |
| 1201 |
moizuddin |
jaipur |
| 1202 |
ajay |
bihar |
| 1203 |
gagan |
raipur |
| ProductId | ProductName | ProductCost |
CustomerId |
| 100 |
Smartphone |
12,000 |
1200 |
| 200 |
Pendrive |
500 |
1201 |
| 300 |
HeadPhones |
1200 |
1202 |
| 400 |
Powerbank |
2500 |
1203 |
Query for Cross join
Select c.* ,p.* from Customers c cross join ProductOrders p on c.CustomerId =p.CustomerId
Select c.CustomerId,c.CustomerName,c.CustomerAddress,p.ProductId,p.ProductName,p.ProductCost from Customers c cross join ProductOrders p on c.CustomerId =p.CustomerId
The result of the query is
| 1200 |
khaja |
Hyderabad |
100 |
Smatphone |
12,000 |
| 1200 |
khaja |
Hyderabad |
101 |
Pendrive |
500 |
| 1200 | khaja | Hyderabad | 102 | Headphones | 1200 |
| 1200 |
khaja |
Hyderabad |
103 |
Powerbank |
2500 |
| 1201 |
Moizuddin |
Jaipur |
100 |
Smatphone |
12,000 |
| 1201 |
Moizuddin |
Jaipur |
101 |
Pendrive |
500 |
| 1201 |
Moizuddin |
Jaipur | 102 | Headphones | 1200 |
| 1201 |
Moizuddin |
Jaipur |
103 |
Powerbank |
2500 |
| 1202 |
Ajay |
Bihar |
100 |
Smatphone |
12,000 |
| 1202 |
Ajay |
Bihar |
101 |
Pendrive |
500 |
| 1202 |
Ajay |
Bihar | 102 | Headphones | 1200 |
| 1202 |
Ajay |
Bihar |
103 |
Powerbank |
2500 |
| 1203 |
Gagan |
Raipur |
100 |
Smatphone |
12,000 |
| 1203 |
Gagan |
Raipur |
101 |
Pendrive |
500 |
| 1203 |
Gagan |
Raipur | 102 | Headphones | 1200 |
| 1203 |
Gagan |
Raipur |
103 |
Powerbank |
2500 |
Self Join
The word self join means joining the single table, in such a way that we will consider a single table as two tables and join both the tables.
Whenever we want to work on self join we have to consider a single table (Example Employee table) as two tables and we will give an alias name for both the tables.
For the table on the left side we will give alias name as 'e' and the table on the right side we will give alias name as 'm'.
Employee1(e)
| EmployeeId | EmployeeName | EmployeeSalary |
mid |
| 1200 |
khaja |
12,000 |
null |
| 1201 |
moizuddin |
15,000 |
1201 |
| 1202 |
waseem |
25,000 |
1202 |
| 1203 |
ajay |
30,000 |
1203 |
| 1204 |
gagan |
45,000 |
1204 |
| 1205 |
ravi |
35,000 |
1205 |
Employee2(m)
| EmployeeId | EmployeeName | EmployeeSalary |
mid |
| 1200 |
khaja |
12,000 |
null |
| 1201 |
moizuddin |
15,000 |
1201 |
| 1202 |
waseem |
25,000 |
1202 |
| 1203 |
ajay |
30,000 |
1203 |
| 1204 |
gagan |
45,000 |
1204 |
| 1205 |
ravi |
35,000 |
1205 |
Query for Self join
Select e.EmployeeName,e.EmployeeSalary as 'Employee1' ,m.EmployeeName,m.EmployeeSalary as 'Employee2' from Employee1 e join Employee2 m on e.mid=m.EmployeeId
Equi Join
Equi join is used to join two tables and used to retrieve the rows/records from the two tables by using (=) operator. By considering the above tables; i.e. Employee table and Department table.
Query for Equi join
Select e.*,d. DepartmentNo,d. DepartmentName from Employee e, Department d where e.EmployeeNo=d.EmployeeNo
The output of the above query is the same as the inner join.
Non Equi Join
Non equi join is used to join two tables and used to retrieve the rows/records from the two tables without using (=) operator.
Thanks and I hope this article helps you.