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

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.

  1. create table Employee(EmployeeNo int, EmployeeName varchar(10), EmployeeSalary money)  
  2. Insert into Employee(EmployeeNo, EmployeeName, EmployeeSalary)  
  3. Values(1201, 'khaja', 10, 000), (1202, 'moiz', 20, 000), (1203, 'ajay', 15, 000), (1204, 'waseem', 25, 000)  
  1. create table Department(DepartmentNo int, DepartmentName varchar(15), EmployeeNo int)  
  2. Insert into Department(DepartmentNo, DepartmentName, EmployeeNo)  
  3. 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

DepartmentNoDepartmentName 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,000300 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.

    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 102Headphones 1200
1200 khaja Hyderabad 103 Powerbank 2500
1201 Moizuddin Jaipur 100 Smatphone 12,000
1201 Moizuddin Jaipur 101 Pendrive 500
1201 Moizuddin Jaipur 102Headphones 1200
1201 Moizuddin Jaipur 103 Powerbank 2500
1202 Ajay Bihar 100 Smatphone 12,000
1202 Ajay Bihar 101 Pendrive 500
1202 Ajay Bihar 102Headphones 1200
1202 Ajay Bihar 103 Powerbank 2500
1203 Gagan Raipur 100 Smatphone 12,000
1203 Gagan Raipur 101 Pendrive 500
1203 Gagan Raipur 102Headphones 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.


Similar Articles