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
Types of JOIN |
Description |
INNER JOIN or JOIN |
Returns result set which contains all the matching rows between the tables used. This JOIN is mainly used between the referential integrity columns in the tables. |
LEFT OUTER JOIN or LEFT JOIN |
Returns result set which contains all the matching rows and also the non matching row(s) from the table specified in the left side of the command. That is it will return all the row(s) from the table specified in the left side and only matching row(s) from the table specified in the right side of the JOIN condition. SQL server will display NULL value for non matching row(s) in right table column. |
RIGHT OUTER JOIN or RIGHT JOIN |
Returns result set which contains all the matching rows and also the non matching row(s) from the table specified in the right side of the command. That is it will return all the row(s) from the table specified in the right side and only matching row(s) from the table specified in the left side of the JOIN condition. SQL server will display NULL value for non matching row(s) in left table column. |
FULL OUTHER JOIN or FULL JOIN |
Returns result set which contains all the data irrespective of matching data.That is all the matching row(s) plus non matching row(s) from left and right tables specified. SQL server will display NULL value for non matching row(s). |
CROSS JOIN | Returns result set which contains a Cartesian product. Number of row(s) returned in the result set is the multiple of total role(s) in left and right table. Total row(s)=Total row(s) in left table X Total row(s) in right table Please be careful when you use this type of JOIN in larger table and make sure you add a WHERE clause. |
Self Join | In some situation where the data resides in the same table and you need to create a join with the same table, this is called Self Join. Self Join can use INNER or OUTER JOIN |
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]
Leave a Reply