August 21, 2014 at 10:41 am
Hi,
Dealing with some sp code where I have left join without predicate and it still works !!!!! Comments says : Optimization (?).
I surely thing it should not be the case, but maybe I'm wrong ?? Appreciate you comments, is it possible, sane?
Select C1, C2, C3
FROM TableA p
LEFT JOIN main.TableC c c.CustID = p.CustID
LEFT JOIN ( supr.Stats gs -- <@>>< nothing here but check next line
INNER JOIN supr.Abbr ab on ab.LogID = gs.LogID )
on gs.LogID = p.LogID.
LEFT JOIN dbo.TableLog l l.LogID = c.LogID
Best
Mario
August 21, 2014 at 12:06 pm
I was unable to left join without a predicate, even if the predicate was a dummy (1=1).
What version of SQL are you using?
August 21, 2014 at 12:15 pm
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
I heard something about default predicates by same column name, but in my case same columns names exists in multiple table which take part in this query, also you can see that gs predicated is on next line, so from a side it looks like "sub JOIN". I just want to break the code...
This piece came also from very reputable person, and after his tuning the whole piece running faster...
I'll try to compose test code with AdventrureWorks and see how it works.
Thanks
M
August 21, 2014 at 1:02 pm
I can't get the code to parse as it either. Something must be missing.
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
August 21, 2014 at 1:07 pm
<<What it's saying is that IF the Left join row exists , INNER join to that other table.
Otherwise, treat it like a regular LEFT join and put NULL in the JOINED columns.>>
Just got feedback from one top notch pro like above on that, so it's OK. It's definetely works and works very fast.
Sorry Jack, didn't get what good or worse in this case?
Thanks
M
August 21, 2014 at 1:18 pm
mario17 (8/21/2014)
<<What it's saying is that IF the Left join row exists , INNER join to that other table.Otherwise, treat it like a regular LEFT join and put NULL in the JOINED columns.>>
Just got feedback from one top notch pro like above on that, so it's OK. It's definetely works and works very fast.
Sorry Jack, didn't get what good or worse in this case?
Thanks
M
The example you posted doesn't compile. I get:
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'LogID'.
When trying to parse the query. I've also tried this:
SELECT
*
FROM
sys.tables AS T
LEFT JOIN sys.columns AS C
JOIN sys.index_columns AS IC
ON C.column_id = IC.column_id
To make sure the issue wasn't related to non-existent objects and I still get the same error. So I'm not sure how the code as presented could work.
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
August 21, 2014 at 1:37 pm
Thanks all, it's fact that this code works.
I'll try to do homework on demo db to break it completely down.
Mario
August 21, 2014 at 2:44 pm
mario17 (8/21/2014)
Thanks all, it's fact that this code works.I'll try to do homework on demo db to break it completely down.
Mario
Would be interesting to see if you can recreate a working left join that looks like that unless its actually quoted and sent to a linked server that I'm not interested in 😛
LOL just kidding, send that too!
August 21, 2014 at 4:02 pm
Bingo !!
Yes it works, I didn't put one extra ON in my very first post, now it edited. and below is handy sample I made for all to try.
It claimed to be ANSI standard. I also put extra parentheses for readability. Plans are different, but Time is pretty much is the same..
WITH TestData AS
( SELECT 1 As testID
UNION ALL
SELECT td.testID + 1
FROM TestData td
WHERE td.testID < 32767 )
SELECT testID, 'This is #t1' AS C1 INTO #t1 FROM testData OPTION (MAXRECURSION 32767) -- drop table #t1, #t22, #T333
SELECT testID, 'This is #t22' AS C1 INTO #t22 FROM #t1 ; DELETE FROM #t22 WHERE testID IN (3,4,5);
SELECT testID, 'This is #t333' AS C1 INTO #t333 FROM #t1 -- select top 10 * from #t122
--------------------------------------------------
select
#t1.testID, #t1.C1 , #t22.C1, #t333.C1
FROM #t1
LEFT JOIN (#t22
INNER JOIN #t333 ON #t333.testID = #t22.testID)
ON #t1.testID = #t22.testID
-- SET STATISTICS TIME ON
/* vs. traditional
select
#t1.testID, #t1.C1 , #t22.C1, #t333.C1
FROM #t1
LEFT JOIN #t22 ON #t22.testID = #t1.testID
left JOIN #t333 ON #t333.testID = #t22.testID */
August 22, 2014 at 1:52 am
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.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 22, 2014 at 5:25 am
mario17 (8/21/2014)
Hi,Dealing with some sp code where I have left join without predicate and it still works !!!!! Comments says : Optimization (?).
I surely thing it should not be the case, but maybe I'm wrong ?? Appreciate you comments, is it possible, sane?
Select C1, C2, C3
FROM TableA p
LEFT JOIN main.TableC c c.CustID = p.CustID
LEFT JOIN ( supr.Stats gs -- <@>>< nothing here but check next line
INNER JOIN supr.Abbr ab on ab.LogID = gs.LogID )
on gs.LogID = p.LogID
LEFT JOIN dbo.TableLog l l.LogID = c.LogID
The predicate is there. See the bolded portion of your code.
It's an odder form of joins, you can put all the joins first and then all the join predicates. It has an effect on the plan and possibly results, so don't do it without a reason.
All joins have to have a predicate though (other than CROSS), you can't get away without one.
p.s. The code as posted didn't parse because there was a stray . after one of the join clauses and because of two missing ON keywords. Copy-paste error perhaps
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2014 at 8:22 am
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.
Is there more to this anywhere? I'm fascinated.
August 22, 2014 at 8:44 am
sqldriver (8/22/2014)
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.
Is there more to this anywhere? I'm fascinated.
I learned this syntax in a session by Itzik Ben-Gan at my first PASS Summit, I believe 2009. So you can probably find more about it by searching for T-SQL Tips by Itzik Ben-Gan. Since I learned this syntax I never do multiple outer joins like in query 2 or the derived table thing in query 3, I use the query 4 syntax all the time. It looks strange at first, but once you understand it it makes sense quickly and is clean.
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
August 22, 2014 at 9:13 am
Jack Corbett (8/22/2014)
I learned this syntax in a session by Itzik Ben-Gan at my first PASS Summit, I believe 2009. So you can probably find more about it by searching for T-SQL Tips by Itzik Ben-Gan. Since I learned this syntax I never do multiple outer joins like in query 2 or the derived table thing in query 3, I use the query 4 syntax all the time. It looks strange at first, but once you understand it it makes sense quickly and is clean.
Cool, thanks. I actually like the way that syntax is organized quite a bit. In a weird way it seems way more sensible than how I've been doing things.
August 22, 2014 at 9:28 am
Jack Corbett (8/22/2014)
I learned this syntax in a session by Itzik Ben-Gan at my first PASS Summit, I believe 2009. So you can probably find more about it by searching for T-SQL Tips by Itzik Ben-Gan. Since I learned this syntax I never do multiple outer joins like in query 2 or the derived table thing in query 3, I use the query 4 syntax all the time. It looks strange at first, but once you understand it it makes sense quickly and is clean.
That must be about when you posted it here on ssc, Jack. I've used it numerous times since but it's not widely known and requires a extra documentation in any query exploiting it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply