different forms of join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

different forms of join

Author

Topic

b8538230
Starting Member

16 Posts

Posted - 2012-02-24 : 11:47:06

If I have this correct, there are a few ways to do joins in T-SQL. One that I use a lot is:

select * from foo
join (select * from bar)
on foo.key = bar.key

The other way that I know about is:

select * from foo, bar
where foo.key = bar.key

While trying this out on a real example, I was surprised to see a difference in the execution plan:

e.g.


create table #CLOC_RCIP (ticket char(5))
insert into #CLOC_RCIP
set showplan_text on
go
select convert(char(5), rcip.ticket) from CRS_RCIP_3_month rcip
join (select * from CRS_cloc_3_month) cloc
on cloc.ticket = rcip.ticket
where cloc.Outage != rcip.Outage
   or cloc.OSF != rcip.OSF

gives the plan:


  |--Hash Match(Inner Join, HASH:([rcip].[Ticket])=([OR_CRS].[dbo].[CRS_cloc_3_month].[Ticket]), RESIDUAL:([OR_CRS].[dbo].[CRS_RCIP_3_month].[Ticket] as [rcip].[Ticket]=[OR_CRS].[dbo].[CRS_cloc_3_month].[Ticket] AND ([OR_CRS].[dbo].[CRS_cloc_3_month].[Outage]<>[OR_CRS].[dbo].[CRS_RCIP_3_month].[Outage] as [rcip].[Outage] OR [OR_CRS].[dbo].[CRS_cloc_3_month].[OSF]<>[OR_CRS].[dbo].[CRS_RCIP_3_month].[OSF] as [rcip].[OSF])))
       |--Compute Scalar(DEFINE:([Expr1007]=CONVERT(char(5),[OR_CRS].[dbo].[CRS_RCIP_3_month].[Ticket] as [rcip].[Ticket],0)))
       |    |--Table Scan(OBJECT:([OR_CRS].[dbo].[CRS_RCIP_3_month] AS [rcip]))
       |--Table Scan(OBJECT:([OR_CRS].[dbo].[CRS_cloc_3_month]))

Converting the select to the other form:


select convert(char(5), rcip.ticket) 
from CRS_RCIP_3_month rcip, CRS_cloc_3_month cloc
where cloc.ticket = rcip.ticket
and cloc.Outage != rcip.Outage
   or cloc.OSF != rcip.OSF

gives the plan:


  |--Nested Loops(Inner Join, WHERE:([OR_CRS].[dbo].[CRS_cloc_3_month].[Ticket] as [cloc].[Ticket]=[OR_CRS].[dbo].[CRS_RCIP_3_month].[Ticket] as [rcip].[Ticket] AND [OR_CRS].[dbo].[CRS_cloc_3_month].[Outage] as [cloc].[Outage]<>[OR_CRS].[dbo].[CRS_RCIP_3_month].[Outage] as [rcip].[Outage] OR [OR_CRS].[dbo].[CRS_cloc_3_month].[OSF] as [cloc].[OSF]<>[OR_CRS].[dbo].[CRS_RCIP_3_month].[OSF] as [rcip].[OSF]))
       |--Compute Scalar(DEFINE:([Expr1006]=CONVERT(char(5),[OR_CRS].[dbo].[CRS_RCIP_3_month].[Ticket] as [rcip].[Ticket],0)))
       |    |--Table Scan(OBJECT:([OR_CRS].[dbo].[CRS_RCIP_3_month] AS [rcip]))
       |--Table Spool
            |--Table Scan(OBJECT:([OR_CRS].[dbo].[CRS_cloc_3_month] AS [cloc]))

Now I'd like to know, why the difference? And, would I see a meaningful performance difference between the two?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-24 : 12:08:41

your two querys are not logically the same.

The first one:


SELECT
	CONVERT(CHAR(5), rcip.ticket)
FROM
	CRS_RCIP_3_month AS rcip
	JOIN (SELECT * FROM CRS_cloc_3_month) AS cloc ON cloc.ticket = rcip.ticket
WHERE
	cloc.[Outage] != rcip.[Outage]
	OR cloc.[OSF] != rcip.[OSF]

(which you can rewrite simply as)


SELECT
	CONVERT(CHAR(5), rcip.ticket)
FROM
	CRS_RCIP_3_month AS rcip
	JOIN CRS_cloc_3_month AS cloc ON cloc.ticket = rcip.ticket
WHERE
	cloc.[Outage] != rcip.[Outage]
	OR cloc.[OSF] != rcip.[OSF]

btw

Establishes a relationship between two tables and then checks that either of 2 or conditions are met.

----------------
your second query:



select convert(char(5), rcip.ticket) 
FROM
	CRS_RCIP_3_month rcip
	, CRS_cloc_3_month cloc
WHERE
	cloc.[ticket] = rcip.[ticket]
	and cloc.[Outage] != rcip.[Outage]
	or cloc.OSF != rcip.OSF

Does something different. You cross join the tables and then apply some filters.

The logic of the where clause effectively reads:


WHERE
	(
	cloc.[ticket] = rcip.[ticket]
	and cloc.[Outage] != rcip.[Outage]
	)
	OR cloc.OSF != rcip.OSF

(I added the brackets)
So the last condition trumps any of the others. the predicate is met simply if cloc.OSF != rcip.OSF even if the the relationship described by cloc.[ticket] = rcip.[ticket] isn't met.

So the querys are totally different - they will give you different results and have different plans.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-24 : 12:10:59

stick to the first form but just simply JOIN to the table -- you don't need to use a derived table if you aren't filtering any rows.

example:



SELECT
	CONVERT(CHAR(5), rcip.ticket)
FROM
	CRS_RCIP_3_month AS rcip
	JOIN CRS_cloc_3_month AS cloc ON cloc.ticket = rcip.ticket
WHERE
	cloc.[Outage] != rcip.[Outage]
	OR cloc.[OSF] != rcip.[OSF]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

b8538230
Starting Member

16 Posts

Posted - 2012-02-24 : 12:17:32

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources