The Left Joins

Question

The Left Joins

Steve Jones - SSC Editor

SSC Guru

Points: 736478
More actions
July 24, 2023 at 12:00 am

#4223691

Comments posted to this topic are about the item The Left Joins

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Carlo Romagnano SSC-Insane Points: 22682 More actions · Answer 1

To select all rows from CustomerLeft change join precedence by parentheses:

SELECT *
 FROM dbo.CustomerLeft AS cl
 LEFT JOIN (dbo.CustomerContact AS cc 
 INNER JOIN dbo.EmailDomain AS ed ON ed.CustomerEmail = cc.CustomerEmail)
 ON (cc.CustomerID = cl.CustomerID)

drew.allen SSC Guru Points: 76996 More actions · Answer 2

Carlo Romagnano wrote:

To select all rows from CustomerLeft change join precedence by parentheses:

SELECT *
 FROM dbo.CustomerLeft AS cl
 LEFT JOIN (dbo.CustomerContact AS cc 
 INNER JOIN dbo.EmailDomain AS ed ON ed.CustomerEmail = cc.CustomerEmail)
 ON (cc.CustomerID = cl.CustomerID)

It's actually the order of the ON clauses that determines the JOIN order, so the parens are NOT required. They only help humans understand the order. The following will also show all records from CustomerLeft.

SELECT *
 FROM dbo.CustomerLeft AS cl
 LEFT JOIN dbo.CustomerContact AS cc 
 INNER JOIN dbo.EmailDomain AS ed
 ON ed.CustomerEmail = cc.CustomerEmail
 ON cc.CustomerID = cl.CustomerID

Here is an alternate format that might be easier for humans to parse.

SELECT *
 FROM dbo.CustomerLeft AS cl
 LEFT JOIN dbo.CustomerContact AS cc 
           INNER JOIN dbo.EmailDomain AS ed
            ON ed.CustomerEmail = cc.CustomerEmail
 ON cc.CustomerID = cl.CustomerID

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 3

drew.allen wrote:

Carlo Romagnano wrote:
To select all rows from CustomerLeft change join precedence by parentheses:
SELECT *
 FROM dbo.CustomerLeft AS cl
 LEFT JOIN (dbo.CustomerContact AS cc 
 INNER JOIN dbo.EmailDomain AS ed ON ed.CustomerEmail = cc.CustomerEmail)
 ON (cc.CustomerID = cl.CustomerID)
It's actually the order of the ON clauses that determines the JOIN order, so the parens are NOT required. They only help humans understand the order. The following will also show all records from CustomerLeft.
SELECT *
 FROM dbo.CustomerLeft AS cl
 LEFT JOIN dbo.CustomerContact AS cc 
 INNER JOIN dbo.EmailDomain AS ed
 ON ed.CustomerEmail = cc.CustomerEmail
 ON cc.CustomerID = cl.CustomerID
Here is an alternate format that might be easier for humans to parse.
SELECT *
 FROM dbo.CustomerLeft AS cl
 LEFT JOIN dbo.CustomerContact AS cc 
           INNER JOIN dbo.EmailDomain AS ed
            ON ed.CustomerEmail = cc.CustomerEmail
 ON cc.CustomerID = cl.CustomerID
Drew

I have never found the need to nest joins like that. I think this might be a more appropriate join:

SELECT *
  FROM dbo.CustomerLeft AS cl
  LEFT JOIN dbo.CustomerContact AS cc ON cc.CustomerID = cl.CustomerID
  LEFT JOIN dbo.EmailDomain AS ed ON ed.CustomerEmail = cc.CustomerEmail
;

The results are slightly different and contain more information

Screenshot 2023-07-31 143856