June 2, 2011 at 2:44 pm
I know that its different and reading the execution plan obviously shows it's different, but I'm trying to figure out why it's interpreted differently.
Select * from
A
Inner join
B on a.id = b.id
LEFT OUTER JOIN
C on c.id = b.id
INNER JOIN
D on d.id = c.id
INNER JOIN E
ON E.id = d.id
Edit: Got carried away. you can assume that all joins are int to int.
Select * from
A
Inner join
B on a.id = b.id
LEFT OUTER JOIN
C
INNER JOIN
D on d.id = c.id
INNER JOIN E
ON E.id = d.id
on c.id = b.id
June 2, 2011 at 3:07 pm
Are you sure about your syntax?
Where is the join to F in either query? What are using to join to C in the second query?
Please cut and paste the actual code. Don't type freehand.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 2, 2011 at 3:24 pm
It's a syntactical way to represent subquerying.
The basic gist is do you inner join the entire left join first, thus restricting what gets left joined, or perform the left join then travel down the path and hard join on the results of the left join.
It's not obvious and you've really got to play with it a bit to get used to where you place the ON clauses.
Most people will use a subquery for the explicit methodology of it, and clearer code writing. I use it as a shorthand, but it's not good practice. As you can tell, it's tough to see the nuance.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 2, 2011 at 3:30 pm
The Dixie Flatline (6/2/2011)
Are you sure about your syntax?Where is the join to F in either query? What are using to join to C in the second query?
Please cut and paste the actual code. Don't type freehand.
This will probably help you with the formatting and a very simplified example, Dixie, of what's happening here:
IF OBJECT_ID('tempdb..#tmp') is not null
DROP TABLE #tmp
IF OBJECT_ID('tempdb..#tmp1') is not null
DROP TABLE #tmp1
IF OBJECT_ID('tempdb..#tmp2') is not null
DROP TABLE #tmp2
CREATE TABLE #tmp (BaseID INT)
CREATE TABLE #tmp1 (LeftJoinID INT)
CREATE TABLE #tmp2 (InnerJoinID INT)
INSERT INTO #tmp VALUES (1)
INSERT INTO #tmp VALUES (2)
INSERT INTO #tmp VALUES (3)
INSERT INTO #tmp VALUES (4)
INSERT INTO #tmp VALUES (5)
INSERT INTO #tmp VALUES (6)
INSERT INTO #tmp1 VALUES (1)
INSERT INTO #tmp1 VALUES (2)
INSERT INTO #tmp1 VALUES (3)
INSERT INTO #tmp1 VALUES (4)
INSERT INTO #tmp2 VALUES (4)
INSERT INTO #tmp2 VALUES (5)
INSERT INTO #tmp2 VALUES (6)
--SELECT * FROM #tmp
--SELECT * FROM #tmp1
--SELECT * FROM #tmp2
--The usual way
SELECT
t.*, t1.*, t2.*
FROM
#tmp AS t
LEFT JOIN
#tmp1 AS t1
ON t.BaseID = t1.LeftJoinID
JOIN
#tmp2 AS t2
ON t1.LeftJoinID = t2.InnerJoinID
-- Inner join on the outside shorthand
SELECT
t.*, t1.*, t2.*
FROM
#tmp AS t
LEFT JOIN
#tmp1 AS t1
JOIN
#tmp2 AS t2
ON t1.LeftJoinID = t2.InnerJoinID
ON t.BaseID = t1.LeftJoinID
EDIT: I should note I ran that on 2k5 to confirm I got the results I expected. If you'll notice, I did nothing but move the ON clause from the LEFT JOIN until after the JOIN. That's it... well, and a little formatting.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 2, 2011 at 3:38 pm
Craig, I created tables A-F in my sandbox database and the queries posted would not run.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "f.id" could not be bound.
I understood what his question was, but I wanted to see actual executable queries before trying to answer.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 2, 2011 at 3:44 pm
The Dixie Flatline (6/2/2011)
Craig, I created tables A-F in my sandbox database and the queries posted would not run.Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "f.id" could not be bound.
I understood what his question was, but I wanted to see actual executable queries before trying to answer.
Ooooooh, sorry then. 😛 I must have misread something you wrote. Sorry bout that. However, apparently he edited after you swung through and removed the F's.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 2, 2011 at 3:55 pm
Ah, thanks guys. It makes sense, just never occurred to me to write it that way.
Edit: Indeed, I did edit it. Meant to note it. My bad.
June 2, 2011 at 3:57 pm
No problem. 🙂 Craig, I learned from your explanation.
Have a great evening, guys.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 2, 2011 at 6:21 pm
Obs (6/2/2011)
Ah, thanks guys. It makes sense, just never occurred to me to write it that way.Edit: Indeed, I did edit it. Meant to note it. My bad.
Glad to help. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply