An Easy Way To Understand Joins In SQL Server

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:

1. Applications developed using PHP interact with MySql database.
2. Applications developed using Java interact with Oracle database.
3. 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

1. Inner join
2. Outer Join
   
   
   1. Left outer join
   2. Right outer join
   3. Full outer join.
      
      
3. Cross join.
4. Self join
5. Equi join
6. 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".


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

1. 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

   
   
2. 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

   
   
3. 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.
   
   
   
   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.