LEFT JOIN without predicate , still works but what is that?

Question

Post reply

LEFT JOIN without predicate , still works but what is that?

Viewing 6 posts - 16 through 20 (of 20 total)

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

mario17 SSCertifiable Points: 6079 More actions · Answer 1

mario17

SSCertifiable

Points: 6079

August 22, 2014 at 10:06 am

#1740313

Thansk, Chris

I also bought a base.

Best

Mario

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

ChrisM@Work (8/22/2014)
Here's something I wrote some time ago (inspired by something Jack Corbett wrote years ago) which explains why you have two joins followed by two ON clauses:
The order of tables in the FROM clause is usually irrelevant because SQL Server will join the tables in whatever order results in the lowest-cost plan. However, the order of ON clauses matters...and can be manipulated to do some funky stuff.
“You have a query which joins two tables, say Customers and Orders. It’s an outer join because you want all customers whether or not they’ve ever placed an order – but the order can’t be empty, it must actually have some lines.”
Create a customer table with three customers, Peter, Simon and Chris
CREATE TABLE #Customers (CustomerID INT IDENTITY(1,1), CustomerName VARCHAR(20))
INSERT INTO #Customers (CustomerName) VALUES ('Peter'), ('Simon'), ('Chris')
Three orders for Peter, two orders for Simon and none for Chris (boo hoo)
CREATE TABLE #Orders (OrderID INT IDENTITY(1,1), CustomerID INT)
INSERT INTO #Orders (CustomerID) VALUES (1),(1),(1),(2),(2)
Only one of those orders has any items on it – Peter’s first order
CREATE TABLE #Orderlines (OrderlineID INT IDENTITY(1,1), OrderID INT, PartName VARCHAR(20))
INSERT INTO #Orderlines (OrderID, PartName) VALUES (1, 'Peter01'), (1, 'Peter02')
Then you write the obvious query:
-- Query 1
SELECT c.*, o.*, ol.*
FROM #Customers c
LEFT JOIN #Orders o ON o.CustomerID = c.CustomerID
INNER JOIN #Orderlines ol ON ol.OrderID = o.OrderID
The result set isn’t what you might expect – there are only two rows, corresponding to Peter’s two items. The result set looks as if SQL Server has changed the outer join to an inner join and the execution plan confirms it. Most TSQL coders know this and will usually begin testing a query where both child tables are outer joined:
-- Query 2
SELECT c.*, o.*, ol.*
FROM #Customers c
LEFT JOIN #Orders o ON o.CustomerID = c.CustomerID
LEFT JOIN #Orderlines ol ON ol.OrderID = o.OrderID
-Which returns too many rows and is tricky to filter. So they switch to this:
-- Query 3
SELECT c.*, o.*
FROM #Customers c
LEFT JOIN (
SELECT o.*, ol.OrderlineID, ol.PartName
FROM #Orders o
INNER JOIN #Orderlines ol ON ol.OrderID = o.OrderID
) o
ON o.CustomerID = c.CustomerID
-Which generates the correct result set as you would expect, and the execution plan confirms an inner join and an outer join.
You could also bracket your joins, but it’s so counterintuitive and tricky to maintain that I’m not even going to provide an example. If you must have a look, use Google – then forget what you’ve seen. There is another way, and that is to change the order of the ON clauses, so that Orders and Orderlines are inner joined before the product is joined to the Customer table:
-- Query 4
SELECT c.*, o.*, ol.*
FROM #Customers c
LEFT JOIN #Orders o
INNER JOIN #Orderlines ol
ON ol.OrderID = o.OrderID
ON o.CustomerID = c.CustomerID
This also generates the correct result set, confirmed by the execution plan. It’s easy to see what’s going on and the plan is only trivially different from query 3 – it’s slightly cheaper.

I just made a slight mod to query #1 and get the same results set and execution plan as query #4

SELECT c.*, o.*, ol.*

FROM #Customers c

LEFT JOIN (#Orders o

INNER JOIN #Orderlines ol ON ol.OrderID = o.OrderID)

ON o.CustomerID = c.CustomerID;

mario17 SSCertifiable Points: 6079 More actions · Answer 3

mario17

SSCertifiable

Points: 6079

August 22, 2014 at 10:54 am

#1740335

you actually got Q#4.:-)

sqldriver SSChampion Points: 10440 More actions · Answer 4

Lynn Pettis (8/22/2014)
I just made a slight mod to query #1 and get the same results set and execution plan as query #4
SELECT c.*, o.*, ol.*
FROM #Customers c
LEFT JOIN (#Orders o
INNER JOIN #Orderlines ol ON ol.OrderID = o.OrderID)
ON o.CustomerID = c.CustomerID;

I was going to mention the non-select join syntax that I saw here as also being similarly cool to the ONs after JOINs example posted, because it was another thing that I would never think of as a possibility.

SELECT xx

FROM A

JOIN (

B JOIN C ON xx

) ON xx

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

mario17 (8/22/2014)
you actually got Q#4.:-)

But I understand what my query is doing easier than Chris's Q4. The parens make the order of the joins visually apparent. The other way just isn't intuitive for me.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Lynn Pettis (8/22/2014)
mario17 (8/22/2014)
you actually got Q#4.:-)
But I understand what my query is doing easier than Chris's Q4. The parens make the order of the joins visually apparent. The other way just isn't intuitive for me.

That's a pretty common feeling about that syntax. I just hate parens. When I do code that way I usually put in a comment along the lines of:

/* The order of the ON's enforces a the order of the JOIN's */

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question