Difference between these two queries. 1

[adam0101]

Is there a difference in performance between the following two queries? I'm wondering if using nested joins has any benefit. If so, can anyone explain why? Thanks.

Query 1

Select
    p.ProjectName,
    d.DeliverableName,
    t.TaskName

From
    tblProjects p

Inner Join
    tblDeliverables d
On  p.ProjectKey = d.ProjectKey

Inner Join
    tblTasks t
On  d.DeliverableKey = t.DeliverableKey

Query 2

Select
    p.ProjectName,
    d.DeliverableName,
    t.TaskName

From
    tblProjects p

Inner Join
    tblDeliverables d

    Inner Join
        tblTasks t
    On  d.DeliverableKey = t.DeliverableKey

On  p.ProjectKey = d.ProjectKey

P.S. If no difference in performance when using INNER JOIN's, what if I were using LEFT or RIGHT OUTER JOIN's?

Adam

http://adameslinger.blogspot.com/

 

[r937]

there should be no difference, assuming you can get the second one to run without a syntax error

but as soon as you change INNER to LEFT or RIGHT OUTER, all bets are off as far as performance goes, because then they aren't the same query, are they

r937.com | rudy.ca

 

[adam0101]

but as soon as you change INNER to LEFT or RIGHT OUTER, all bets are off as far as performance goes, because then they aren't the same query, are they

So are you saying that if I replaced all the INNER JOIN's with LEFT OUTER JOIN's in both queries, that the two queries could potentially give different results?

If that's the case, I guess I don't understand the reason for it.

Query 1

Select
    p.ProjectName,
    d.DeliverableName,
    t.TaskName

From
    tblProjects p

[b]Left Outer Join[/b]
    tblDeliverables d
On  p.ProjectKey = d.ProjectKey

[b]Left Outer Join[/b]
    tblTasks t
On  d.DeliverableKey = t.DeliverableKey

Query 2

Select
    p.ProjectName,
    d.DeliverableName,
    t.TaskName

From
    tblProjects p

[b]Left Outer Join[/b]
    tblDeliverables d

    [b]Left Outer Join[/b]
        tblTasks t
    On  d.DeliverableKey = t.DeliverableKey

On  p.ProjectKey = d.ProjectKey

Adam

http://adameslinger.blogspot.com/

 

[r937]

does query 2 actually run? i didn't know you could have two consecutive ON clauses like that

r937.com | rudy.ca

 

[adam0101]

Sure it does.

Adam

http://adameslinger.blogspot.com/

 

[SQLBill]

INNER JOIN is a MATCH. Only those items that match in both tables are returned. NULLs will not be returned.

OUTER JOIN is everything from the outer table AND whatever matches from the JOINed table, including NULLs.

TableA TableB
Col1 Col2
1 1
NULL 2
3 3
4 NULL

SELECT a.Col1, b.Col2
FROM TableA
INNER JOIN TableB
ON a.Col1 = b.Col2

a.Col1 b.Col2
1 1
3 3

SELECT a.Col1, b.Col2
FROM TableA
LEFT OUTER JOIN TableB
ON a.Col1 = b.Col2

a.Col1 b.Col2
1 1
3 3
4 NULL

SELECT a.Col1, b.Col2
FROM TableA
RIGHT OUTER JOIN TableB
ON a.Col1 = b.Col2

a.Col1 b.Col2
1 1
NULL 2
3 3

I think I have all that right. But your best bet is to refer to the BOL.

-SQLBill



Posting advice: FAQ481-4875

 

[adam0101]

Well I did some experimenting and the execution plans appear to be identical. The only difference I can see is that Query 2 seems to take 1 or 2 milliseconds longer to parse.

Someone told me that Query 2 would run faster because it was limiting the number of records being joined to the outer table. As far as I can tell, this appears to be false. If anyone can add any comments clarifying this, that'd be great.

Thanks,


Adam

http://adameslinger.blogspot.com/

 

[adam0101]

SQLBill, The question was the difference between nested and non-nested table joins. Not between inner and outer joins.

Adam

http://adameslinger.blogspot.com/

 

[r937]

hey, whaddya know

i checked BOL and sure enough, you can have several ON clauses in a row!

r937.com | rudy.ca

 

[SQLBill]

Adam0101,

You stated this in one of your posts (13 Jan 06 10:13)...

So are you saying that if I replaced all the INNER JOIN's with LEFT OUTER JOIN's in both queries, that the two queries could potentially give different results?

That's what I was responding to. Yes, LEFT OUTER JOINs can give a different result than INNER JOINs.

-SQLBill

Posting advice: FAQ481-4875

 

[adam0101]

Sorry for the confusion. Please allow me to clarify.

So are you saying that if I replaced all the INNER JOIN's with LEFT OUTER JOIN's in both [ of the queries labeled "Query 1" and "Query 2" ], that the queries [ labeled "Query 1" and "Query 2" ] could potentially give different results?

Again, I'm comparing Query 1 vs. Query 2, not INNER vs. OUTER JOINs.

Thanks anyway,

Adam

http://adameslinger.blogspot.com/

 

[SajidAttar]

Hi, Thanks Adam for posting this thread (or for letting me know this way of joining :-D ). I did play with above queries and noticed that . . .

1. When you inner join three tables both the query returns the same result sets and Query cost is identical.

2. When you LEFT / RIGHT OUTER join them the query cost of the second query is smaller than the first one and I think this makes it faster when working with huge number of data.

3. When you use inner join then left join both the queries return same result with the same query cost.

4. When you use Inner join followed by Left join the output of both the queries if different. In this case second query costs more than first query but returns all records from PROJECTS table and only the matching records from the Result of second inner join.

Create table dbo.#Projects (PID int identity (1,1), ProjectName varchar(50))
Create table dbo.#Deliverable (DID int identity (1,1), PID int, Description varchar(50))
Create table dbo.#Tasks (TID int identity (1,1), DID int, Details varchar(50))

declare @Int int
while isnull(@int,0) < 100
begin
set @int = isnull(@int,0) + 1

Insert into #Projects (ProjectName) values ('Project # ' + convert(varchar, @int))

end

set @int = 0
while isnull(@int,0) < 100
begin
set @int = isnull(@int,0) + 3

Insert into #Deliverable (Pid, Description) values (@int, 'Deliverable of Project # ' + convert(varchar, @int))

end


set @int = 0
while isnull(@int,0) < 75
begin
set @int = isnull(@int,0) + 3

Insert into #Tasks (did, details) values (@int, 'Tasks of Delivery # ' + convert(varchar, @int))

end


select P.Pid, P.ProjectName, D.Did, D.Description, t.Tid, t.Details
from #Projects P
left join #Deliverable D on p.Pid = d.Pid
inner join #Tasks T on d.Did = t.Did


select P.Pid, P.ProjectName, D.Did, D.Description, t.Tid, t.Details
from #Projects P
left join #Deliverable D 
	inner join #Tasks T on d.Did = t.Did
on p.Pid = d.Pid

Hope this helps . . .

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[adam0101]

#1, #3, and #4 make sense. So if I wanted to LEFT JOIN to a set of tables rather than an individual table, I'd use the nested joining technique.

So if I wanted to see ALL the projects and ANY deliverables with at least one task, I could use the nested technique to join the Deliverables to the Tasks first, then LEFT JOIN that set of tables to the Projects table.

For #2, I still don't understand why Query 2 would be faster. If all the joins were LEFT JOIN's, I would think they'd be the same. But if your results say differently, maybe the person that told me of this was correct after all. I may not have been working with enough data to see the benefits of it.

Not knowing the reason why is still bothering me though.

Adam

http://adameslinger.blogspot.com/