SQL Server Joins

SQL Server joins are required to retrieve data from one or more tables to get a single result set. Joins are needed when Normalization enforced in design. When you normalize the data you be reducing redundancy in data by placing data in different tables and also you will be creating relationship between the tables. With the help of these relationship you will be retrieving the data using joins efficiently.

SQL Server joins are used in FROM clause of DML statements to retrieve data from one or more tables. When you use more tables in JOIN clause then locking is placed on all the tables used and there is a possibility for deadlock situation. So when you write a query dont use more tables, keep the transaction as short as possible. There are many types of JOINS available and the type of JOIN you are going to select should be based on your business logic. Types of JOINS are listed in the table below

LEFT and RIGHT OUTER JOIN are opposite in nature, so you can always flip flop the table name used in left and right side to achieve the result. Lets see a example for all the types of JOINs discussed above, before we stepping into a example lets create two tables using the script below. Im going to create two tables , first table has Manager details and second table has workforce details related to the manager. The relationship between these two tables are based on ID column in Manager table and ManagerID column in Workforce table

CREATE DATABASE [Joins]
GO
USE [Joins]
GO
CREATE TABLE [dbo].[Manager](
    [ID] [smallint] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [City] [varchar](50) NOT NULL,
    [Department] [varchar](50) NOT NULL,
    [Mobile] [smallint] NOT NULL,
 CONSTRAINT [PK_Manager] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkForce](
    [ManagerID] [smallint] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Department] [varchar](50) NOT NULL,
    [Phone] [smallint] NOT NULL,
    [Description] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [Manager] VALUES (1,'Sugesh','New York','DBA',12345)
INSERT INTO [Manager] VALUES (2,'Praveen','Chennai','DBA',12345)
INSERT INTO [Manager] VALUES (3,'Krishna','Chennai','DBA',12345)
GO
INSERT INTO [WorkForce] VALUES(1,'Lexx','SQL-DBA',2344,'DBA')
INSERT INTO [WorkForce] VALUES(2,'Deepak','SQL-DBA',2344,'DBA')
INSERT INTO [WorkForce] VALUES(2,'Vidhya','SQL-DBA',2344,'DBA')
INSERT INTO [WorkForce] VALUES(4,'Senthil','SQL-DBA',2344,'DBA')
GO

INNER JOIN or JOIN

This join return only matching row(s) from both the table. You can see from our example that ID 1 and 2 are matching in both the tables, so it will exclude all other row(s).

SELECT A.Name 'Manager Name',B.Name 'Workforce Name'
,B.Department FROM Manager A
INNER JOIN WorkForce B
ON A.ID=B.ManagerID

LEFT OUTER JOIN or LEFT JOIN

In this join the result set contains matching row(s) plus all the row(s) from the table specified in the left side. In our example, it will return matching row(s) plus all row(s) from Manager table (which is used in left side of the condition), so it will include ID 3 in output

SELECT A.Name 'Manager Name',B.Name 'Workforce Name'
,B.Department FROM Manager A
LEFT OUTER JOIN WorkForce B
ON A.ID=B.ManagerID

RIGHT OUTER JOIN or RIGHT JOIN

In this join the result set contains matching row(s) plus all the row(s) from the table specified in the right side. In our example, it will return matching row(s) plus all row(s) from Workforce table (which is used in right side of the condition), so it will include ManagerID 4 in output

SELECT A.Name 'Manager Name',B.Name 'Workforce Name'
,B.Department FROM Manager A
RIGHT OUTER JOIN WorkForce B
ON A.ID=B.ManagerID

FULL OUTHER JOIN or FULL JOIN

This join will return a result set which has all the data irrespective of matching row(s), i.e it has all the row(s) from both the tables. In this case the output has all the row(s) from Manager and Workforce table

SELECT A.Name 'Manager Name',B.Name 'Workforce Name'
,B.Department FROM Manager A
FULL OUTER JOIN WorkForce B
ON A.ID=B.ManagerID

CROSS JOIN

In this join the result set contains a Cartesian product, i.e all the row(s) in the left side is joined with all the row(s) in the right side, so we the total row(s) returned will be a multiple of row(s) in left table and row(s) in right table. In this example the total row(s) returned will be 12 ( 3 rows in Manager table and 4 rows in Workforce table). Please make a note that when you use this join in a large table make sure you add WHERE clause so that your performance will not be messed up.

SELECT A.Name 'Manager Name',B.Name 'Workforce Name'
,B.Department FROM Manager A
CROSS JOIN WorkForce B

SELF JOIN

Self join is nothing but joining the same table to retrieve the data. Lets take our example and populate all the data from both the tables to a single table, i.e de-normalized table, so that the table have both Manager and Workforce Details. You can use the script below to create the table called Manager_SelfJoin.

USE [Joins]
GO
CREATE TABLE [dbo].[Manager_SelfJoin](
    [EmpID] [smallint] NOT NULL,
    [ManagerID] [smallint] NOT NULL,
    [Manager] [bit] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [City] [varchar](50) NOT NULL,
    [Department] [varchar](50) NOT NULL,
    [Mobile] [smallint] NOT NULL)
GO
INSERT Manager_SelfJoin VALUES(1,0,1,'Sugesh','New York','DBA',12345)
INSERT Manager_SelfJoin VALUES(2,1,0,'Lexx','NZ','DBA',12345)
INSERT Manager_SelfJoin VALUES(3,0,1,'Praveen','Chennai','DBA',12345)
INSERT Manager_SelfJoin VALUES(4,3,0,'Deepak','Chennai','DBA',12345)
INSERT Manager_SelfJoin VALUES(5,3,0,'Vidhya Sagar','Chennai','DBA',12345)
INSERT Manager_SelfJoin VALUES(6,0,1,'Krishna','Chennai','DBA',12345)
INSERT Manager_SelfJoin VALUES(7,8,1,'Senthil','Chennai','DBA',12345)
GO

Now Ive re-written the query to use self join to get the similar data which we got in result set one.

SELECT A.Name 'Manager Name', B.Name 'Emp Name'
FROM Manager_SelfJoin A INNER JOIN Manager_SelfJoin B
ON A.EmpID=B.ManagerID

Ive came to the end of the article, we have seen all the types of JOINS used in SQL Server. Hope this article helped you to understand JOINS with an example. One you are played with the script above, just use the code below to drop the test database.

USE [master]
GO
DROP DATABASE [JOINS]