Joins In SQL Server 2017

Introduction

 

In this article, I am going to explain joins and types of joins with examples. Here we will be using SQL Server 2017 or you can use SQL Server 2008 or above.

 

Definition

 

Joins are used to fetch/retrieve data from two or more related tables from a database. In general, tables are related to each other using foreign key constraints.

 

Types of Joins

 

There are four types of joins in SQL Server.

• Inner Join
• Outer Join
• Cross Join
• Self Join

Again, Outer Joins are divided into three types.

• Left Join or Left Outer Join
• Right Join or Right Outer Join
• Full Join or Full Outer Join

Now, I am going to explain different types of joins with examples and the differences between them.

 

Prerequisites

 

SQL Server 2017 or you can use SQL server 2008 or above version.

 

Now, first, we will create a Database and two tables to apply the joins for understanding.

 

Creating Database and Two Tables

 

Step 1 - Create a Database

 

Open your SQL Server and use the following script to create the “sameer” Database.

1. Create database sameer

Now, select the script query then press F5 or click on Execute button to execute the above script.

 

You should see a message, “Command(s) completed successfully.” This means your new database has been created.

 

Step 2 - Create the first table

 

Open your SQL Server and use the following script to create table “tbl_Department”.

1. create table tbl_Department  
2. (  
3.    DeptId int primary key not null identity(1,1),  
4.    DeptName nvarchar(50),  
5.    DeptHead nvarchar(50),  
6.    Location nvarchar(100)  
7. )  

Execute the above query to create “tbl_Department “.

 

You should see a message, “Command(s) completed successfully.”

 

Now, data is inserted into the table.

1. Insert into tbl_Department values ( 'IT', 'Sameer', 'Mumbai')  
2. Insert into tbl_Department values ( 'Payroll', 'Akhil', 'Odisha')  
3. Insert into tbl_Department values ( 'HR', 'Ram', 'Pune')  
4. Insert into tbl_Department values ( 'Timesheet', 'Kannan', 'chennai')  

Execute the above query, you should see a message, “Command(s) completed successfully.”

 

Now retrieve all data from the “tbl_Department” table.

1. select * from tbl_Department  

output

 

 

Step 3 - Create a second table

 

Open your SQL Server and use the following script to create table “tbl_Employee”.

1. Create table tbl_Employee  
2. (  
3.      EmpID int primary key not null identity(1,1),  
4.      Name nvarchar(50),  
5.      Gender nvarchar(50),  
6.      country nvarchar(20),  
7.      Salary int,  
8.      DepartmentId int foreign key references tbl_Department(DeptId)  
9. )  

Execute the above query to create “tbl_Employee “.

 

You should see a message, “Command(s) completed successfully.”

 

Now, data is inserted into the table.

1. Insert into tbl_Employee values ( 'Jitu', 'Male','India',4000, 1)  
2. Insert into tbl_Employee values ( 'Rani', 'Female','India', 5000, 3)  
3. Insert into tbl_Employee values ( 'Rohit', 'Male','India', 5500, 1)  
4. Insert into tbl_Employee values ( 'Dibas', 'Male','India', 6500, 2)  
5. Insert into tbl_Employee values ( 'Gajendra', 'Male','India', 3800, 2)  
6. Insert into tbl_Employee values ( 'Raja', 'Male','India', 9000, 1)  
7. Insert into tbl_Employee values ( 'Jeni', 'Female','India', 5800, 3)  
8. Insert into tbl_Employee values ( 'Chandin', 'Female','India', 8500, 1)  
9. Insert into tbl_Employee values ( 'pintu', 'Male','India', 9500, NULL)  
10. Insert into tbl_Employee values ( 'Subrat', 'Male','India', 9800, NULL)  

Execute the above query, you should see a message, “Command(s) completed successfully.”

 

Now retrieve all data from the “tbl_Employee” table.

1. select * from tbl_Employee  

output

 

 

General Formula for Joins,

1. SELECT      ColumnList (whatever column you want to display)   
2. FROM           LeftTableName   
3. JOIN_TYPE  RightTableName   
4. ON                 JoinCondition  

INNER JOIN

 

Inner join returns only the matching rows between both the tables; non-matching rows are eliminated.

 

Example

 

Write a query to retrieve Name, Gender, Country, Salary, and DeptName from tbl_Employee and tbl_Department table.

 

INNER JOIN Query

1. SELECT Name, Gender,country, Salary, DeptName   
2. FROM tbl_Employee  
3. INNER JOIN tbl_Department  
4. ON tbl_Employee.DepartmentId = tbl_Department.DeptId  

OR

1. SELECT Name, Gender,country, Salary, DeptName   
2. FROM tbl_Employee  
3.  JOIN tbl_Department  
4. ON tbl_Employee.DepartmentId = tbl_Department.DeptId  

Note

JOIN or INNER JOIN is the same. It's always better to use INNER JOIN.

 

OR

1. select emp.Name,emp.Gender,emp.country,emp.Salary,dept.DeptName   
2.     from tbl_Employee emp   
3.     inner join tbl_Department dept  
4.     on emp.DepartmentId=dept.DeptId  

OutPut

 

 

LEFT JOIN or LEFT OUTER JOIN

 

Left Join or Left Outer Join returns only the matching rows between both the tables, plus non-matching rows from the left table.

 

Example

 

Write a query to retrieve Name, Gender, Country, Salary, and DeptName from tbl_Employee and tbl_Department table.

 

LEFT JOIN or LEFT OUTER JOINQuery,

1. SELECT Name, Gender,country, Salary, DeptName   
2. FROM tbl_Employee  
3. LEFT OUTER JOIN tbl_Department  
4. ON tbl_Employee.DepartmentId = tbl_Department.DeptId  

OR

1. SELECT Name, Gender,country, Salary, DeptName   
2. FROM tbl_Employee  
3. LEFT JOIN tbl_Department  
4. ON tbl_Employee.DepartmentId = tbl_Department.DeptId  

Note

You can use, LEFT JOIN or LEFT OUTER JOIN. OUTER keyword is optional.

 

OR

1. select emp.Name,emp.Gender,emp.country,emp.Salary,dept.DeptName   
2.     from tbl_Employee emp   
3.     LEFT JOIN tbl_Department dept  
4.     on emp.DepartmentId=dept.DeptId  

OutPut

 

 

RIGHT JOIN or RIGHT OUTER JOIN

 

Right Join or Right Outer Join returns only the matching rows between both the tables, plus non-matching rows from the right table.

 

Example

 

Write a query to retrieve Name, Gender, Country, Salary, and DeptName from tbl_Employee and tbl_Department table.

 

RIGHT JOIN or RIGHT OUTER JOIN Query,

1. SELECT Name, Gender,country, Salary, DeptName   
2. FROM tbl_Employee  
3. RIGHT JOIN tbl_Department  
4. ON tbl_Employee.DepartmentId = tbl_Department.DeptId  

OR

1. SELECT Name, Gender,country, Salary, DeptName   
2. FROM tbl_Employee  
3. RIGHT OUTER JOIN tbl_Department  
4. ON tbl_Employee.DepartmentId = tbl_Department.DeptId  

Note

You can use, RIGHT JOIN or RIGHT OUTER JOIN. OUTER keyword is optional.

OR

1. select emp.Name,emp.Gender,emp.country,emp.Salary,dept.DeptName   
2.     from tbl_Employee emp   
3.     RIGHT JOIN tbl_Department dept  
4.     on emp.DepartmentId=dept.DeptId  

OutPut

 

 

FULL JOIN or FULL OUTER JOIN

 

Full Join or Full Outer Join returns all rows from both the tables (left & right tables), including non-matching rows from both the tables.

 

Example

 

Write a query to retrieve Name, Gender, Country, Salary, and DeptName from tbl_Employee and tbl_Department table.

 

FULL JOIN or FULL OUTER JOIN Query,

1. SELECT Name, Gender, country, Salary, DeptName   
2. FROM tbl_Employee  
3. FULL OUTER JOIN tbl_Department  
4. ON tbl_Employee.DepartmentId = tbl_Department.DeptId  

OR

1. SELECT Name, Gender, country, Salary, DeptName   
2. FROM tbl_Employee  
3. FULL JOIN tbl_Department  
4. ON tbl_Employee.DepartmentId = tbl_Department.DeptId  

Note

You can use, FULL JOIN or FULL OUTER JOIN. The OUTER keyword is optional.

 

OR

1. select emp.Name,emp.Gender,emp.country,emp.Salary,dept.DeptName   
2.     from tbl_Employee emp   
3.     FULL JOIN tbl_Department dept  
4.     on emp.DepartmentId=dept.DeptId  

OutPut

 

 

CROSS JOIN

CROSS JOIN, produces the Cartesian product of the 2 tables.

 

For example, in the tbl_Employee table, we have 10 rows and in the tbl_Department table, we have 4 rows. So, a cross join between these 2 tables produces 40 rows. Cross Join shouldn't have an ON clause. 

 

Example

 

Write a query, to retrieve Name, Gender, Country, Salary, and DeptName from tbl_Employee and tbl_Department table.

CROSS JOIN Query

1. SELECT Name, Gender,country, Salary, DeptName   
2. FROM tbl_Employee  
3. CROSS JOIN tbl_Department  

OR

1. select emp.Name,emp.Gender,emp.country,emp.Salary,dept.DeptName   
2.     from tbl_Employee emp   
3.     cross JOIN tbl_Department dept  

OutPut

 

 

Conclusion

 

In this article, I explained joins in SQL Server with examples. I hope this article has helped you to understand this topic. Post your valuable feedback in the comments section.