Click here to Skip to main content
13,352,879 members (49,267 online)
Click here to Skip to main content
Add your own
alternative version

Stats

18.2K views
44 bookmarked
Posted 10 Oct 2016

Understanding JOINs in SQL Server

, 4 Dec 2016
Rate this:
Please Sign up or sign in to vote.
It's very important to know what sort of JOINs are available in SQL Server and where exactly we can use them, in order to produce accurate results.

During my work, I get a chance to review lots of T-SQL Procedures and Views and I often see that the SQL joins are misused in them. When I ask developers regarding this, it’s evident that most of the time it has been the case that they don’t have proper understanding of what each JOIN exactly does or how it behaves, ultimately causing the SQL Procedure or the View to return an unexpected result set. Therefore, I thought of writing this blog post.

When we require fetching details from multiple tables, the JOIN clause is there to the rescue. But in SQL Server, there are various types of JOINs which will cater to our requirement in different ways. So it’s very important to have a good understanding of these types of JOINs and their usage.

In SQL Server, the following types of JOINs are available:

  • INNER JOIN
  • OUTER JOIN
    • LEFT OUTER JOIN
    • RIGHT OUTER JOIN
    • FULL OUTER JOIN
  • CROSS JOIN
  • CROSS APPLY
  • OUTER APPLY

We will look into the aforementioned JOINs more closely. The scope of this article is to give a high-level idea on the aforementioned JOINs and the APPLY operator in SQL Server.

To illustrate the aforementioned JOINs, I will use the following sample tables:

  • SalesRep
  • SalesDetails
  • RepRating
  • Settings

We consider a case where we have 5 Sales Reps and the details will be saved in ‘RepDetails’ table and the sales transactions which they have done is recorded under ‘SalesDetails’ table. In the SalesDetails table, we have included few transactions which we don’t have a matching Sales Rep. Similarly, in the RepDetails table, there are a couple of sales reps for which we don’t have any sales information.

--== Create Tables ==--
CREATE TABLE RepDetails(
 RepId  INT
 ,RepName VARCHAR(30)
)

CREATE TABLE SalesDetails(
 RepId  INT
 ,SaleMonth VARCHAR(6)
 ,OrderNo VARCHAR(6)
 ,SaleValue MONEY
)

CREATE TABLE RepRating(
 RepId  INT
 ,Rate  INT
 ,YearMonth VARCHAR(6)
)

CREATE TABLE Settings(
 S_Id  INT
 ,S_Desc  VARCHAR(20)
 ,S_Value VARCHAR(20)
)

--== Populate Sample Data ==--
INSERT INTO RepDetails (
 [RepId]
 ,[RepName]
) VALUES 
 (1,'Eugene Thomas')
 ,(2,'John Wheeler')
 ,(3,'Curtis Bailey')
 ,(4,'Jeffrey Garrett')
 ,(5,'Rosemarie Hubbard')

INSERT INTO SalesDetails (
 [RepId]
 ,[SaleMonth]
 ,[OrderNo]
 ,[SaleValue]
) 
VALUES 
(7,'201607','XpyDy3',839)
,(1,'201607','NR0RTp',496)
,(4,'201607','4552T4',299)
,(6,'201607','GKhkyC',877)
,(4,'201606','iyK65Z',291)
,(6,'201606','NFCszW',446)
,(7,'201606','D238bN',135)
,(1,'201607','bERDXk',304)
,(7,'201608','nykZqB',935)
,(4,'201608','R7ea5v',352)
,(6,'201606','VVjIdo',407)
,(7,'201608','vtLT4z',977)
,(2,'201608','xnHTnO',416)
,(1,'201606','jFAJIm',674)
,(6,'201606','0Q011m',480)


INSERT INTO dbo.RepRating(
 RepId
 ,Rate
 ,YearMonth
)
VALUES
 (1,1,'201608')
 ,(3,2,'201608')
 ,(4,1,'201609')
 ,(2,2,'201609')

INSERT INTO dbo.Settings(
 S_Id
 ,S_Desc
 ,S_Value
)
VALUES
 (1,'LedgerMonth','201609')
 ,(2,'TaxRate','10%')

Note: During the illustration, I will refer to the table which is followed by the ‘FROM’ clause as the ‘Left Table’ and the table which is followed by the JOIN clause as the ‘Right Table’.

Inner Join / Join

When we join two or more tables using an INNER JOIN, it will only return us the results when records can only be found on both left and right tables which will satisfy the condition we supply.

image

This can be illustrated using a Venn diagram as follows:

image

SELECT *
FROM
 dbo.RepDetails AS RD
 JOIN dbo.SalesDetails AS SD
  ON SD.RepId = RD.RepId

image

Please note: We have sales reps having RepIds 1,2,3,4, & 5. But in SalesDetails table, we have sales details for RepIds 1,2,4,6 &7. So when these tables are joined the RepIds which reside on both tables, which are 1, 2, and 4 will return the details, ultimately giving us the aforementioned result set.

Left Outer Join / Left Join

In a LEFT OUTER JOIN, unlike the INNER JOIN, it will select all the records from the ‘Left’ table and based on the JOIN condition, it will select any matching records from the ‘Right’ table and return us the results. If there are no matching details on the ‘Right’ table, columns on related to those rows will return as ‘NULL’.

image

This can be shown using a Venn diagram as follows:

image

SELECT * 
FROM
 dbo.RepDetails AS RD
 LEFT JOIN dbo.SalesDetails AS SD
  ON SD.RepId = RD.RepId

image

Right Outer Join / Right Join

In a RIGHT OUTER JOIN, it will select all records from the ‘Right’ table and based on the JOIN condition, it will select any matching records from the left table and return. If there aren’t any matching records on the left table, it will return a ‘NULL’ value.

image

This can be shown using a Venn diagram as follows:

image

SELECT * 
FROM
 dbo.SalesDetails AS SD
 RIGHT JOIN dbo.RepDetails AS RD
  ON SD.RepId = RD.RepId

Full Outer Join / Full Join

FULL OUTER JOIN is kind of a mix of both LEFT & RIGHT OUTER JOINs. It will return all rows from both ‘Left’ and ‘Right’ tables based on the JOIN condition. When the details aren’t matched, it will return a NULL value in those respective columns.

image

This can be shown using a Venn diagram as follows:

image

SELECT * 
FROM
 dbo.RepDetails AS RD
 FULL OUTER JOIN dbo.SalesDetails AS SD
  ON SD.RepId = RD.RepId

Cross Join

CROSS JOIN will return a result set with the number of rows equal to rows in ‘Left’ table multiplied by the number of rows in ‘Right’ table. Usually, this behaviour is present when there’s no condition provided in the WHERE condition. So each row in the left table is joined to each row in the right table. Usually, this behaviour is called ‘Cartesian Product’.

image

SELECT * 
FROM
 dbo.RepDetails AS RD
 CROSS JOIN dbo.Settings AS S

image

But when some condition is provided via the WHERE clause CROSS JOIN will behave like an INNER JOIN:

SELECT * 
FROM
 dbo.RepDetails AS RD
 CROSS JOIN dbo.Settings AS S
WHERE
 RD.RepId = S.S_Id

image

Note: In a CROSS JOIN, it’s not possible to refer to a value in the Left table along with the right table. Example: the following code will result in an error.

SELECT * 
FROM
 dbo.RepDetails AS RD
 CROSS JOIN (SELECT * FROM dbo.Settings AS S WHERE S.S_Id = RD.RepId ) AS ST

CROSS APPLY behaves like an INNER JOIN and OUTER APPLY behaves like an OUTER JOIN. But the main difference in APPLY compared to the JOIN is that the right side of the APPLY operator can reference columns in the table which is on the left side. This is not possible in a JOIN.

For example, suppose we need to fetch sales rep details along with the maximum sale record which they have done. So the following query is not possible since it is returning an error due to the aforementioned reason.

SELECT 
 *
FROM
 dbo.RepDetails AS RD
 JOIN(
  SELECT TOP 1 * 
  FROM 
   dbo.SalesDetails AS SD 
  WHERE 
   RD.RepId = SD.RepId 
  ORDER BY  
   SD.SaleValue DESC
 ) AS SData 
  ON 1=1

It will result in an error:

Msg 4104, Level 16, State 1, Line 78
The multi-part identifier "RD.RepId" could not be bound.

The way to achieve this is by using an APPLY.

Cross Apply

Considering the above requirement, we can use a CROSS APPLY in order to achieve the aforementioned.

SELECT 
 *
FROM
 dbo.RepDetails AS RD
 CROSS APPLY(
  SELECT TOP 1 * 
  FROM 
   dbo.SalesDetails AS SD 
  WHERE 
   RD.RepId = SD.RepId 
  ORDER BY  
   SD.SaleValue DESC
 ) AS SData 

image

Noticed the above sample, you can see that it returned three records. But if you inspect closely, the SalesRep table consists with five Reps. But CROSS APPLY has only returned the maximum sales value if there’s a matching record on the table right side to the APPLY operator. (Similar to an INNER JOIN)

Outer Apply

Using OUTER APPLY, we can achieved a similar result like CROSS APPLY, but the difference is that even though there aren’t any matching records in the table right side to the APPLY operator, still it will return all the rows from the left side table, will NULL values for the columns in the right side table. We will consider the same query that we used in the above example, but changing the APPLY to an OUTER APPLY.

SELECT 
 *
FROM
 dbo.RepDetails AS RD
 OUTER APPLY(
  SELECT TOP 1 *
  FROM 
   dbo.SalesDetails AS SD 
  WHERE 
   RD.RepId = SD.RepId 
  ORDER BY  
   SD.SaleValue DESC
 ) AS SData

image

There are other capabilities which are possible using the APPLY. This article explains these capabilities really well.

Hope this will help you to understand the JOIN and the APPLY operator in SQL Server and where it can be used precisely.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Manjuke Fernando
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.

My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.

My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.


Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports

You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralMy vote of 5 Pin
MarcusCole0920766-Dec-16 7:44
professionalMarcusCole0920766-Dec-16 7:44 
QuestionExcellent: 5+ Pin
Brian Stevens5-Dec-16 10:39
memberBrian Stevens5-Dec-16 10:39 
PraiseMy vote of 5 Pin
JohannQ5-Dec-16 9:50
memberJohannQ5-Dec-16 9:50 
PraiseGood example of Cross Apply and Outer Apply. Pin
Meyrick22-Nov-16 0:31
memberMeyrick22-Nov-16 0:31 
PraiseRe: Good example of Cross Apply and Outer Apply. Pin
Manjuke Fernando4-Dec-16 3:53
professionalManjuke Fernando4-Dec-16 3:53 
QuestionNice Pin
Ken of Kendoria12-Oct-16 6:51
professionalKen of Kendoria12-Oct-16 6:51 
QuestionMy vote of 10 Pin
Naeem Sardar11-Oct-16 3:19
memberNaeem Sardar11-Oct-16 3:19 
QuestionReally good Pin
Azagal10-Oct-16 16:31
memberAzagal10-Oct-16 16:31 
GeneralMy vote of 5 Pin
Member 1164858710-Oct-16 10:10
memberMember 1164858710-Oct-16 10:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.180111.1 | Last Updated 4 Dec 2016
Article Copyright 2016 by Manjuke Fernando
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid