Are both these select statements same?

Question

Are both these select statements same?

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
May 14, 2011 at 9:57 pm

#236114

select t1.col1,t1.col2,t1.col3
from (select * from table1 where col3=1) t1
left outer join
(select * from table table2 where col2 is NULL) t2
on t1.col2=t2.col2
select t1.col1,t1.col2,t1.col3
from table1 t1
left outer join
table2 t2
on t1.col2=t2.col2
where t1.col3=1 and t2.col2 is NULL

Viewing 12 posts - 1 through 11 (of 11 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

No.

The first one will return more rows since you'll get rows returned on the left side of your join regardless whether t2.col2 is null or not.

The second query will exclude rows where t2.col2 is not null.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

Thanks. My goal was to reduce the sub-set by using where clause first and then joining the tables. Any better way to tune this?

LutzM SSC Guru Points: 107049 More actions · Answer 3

Which one provides the correct results?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 4

LutzM (5/15/2011)
Which one provides the correct results?

Second one

LutzM SSC Guru Points: 107049 More actions · Answer 5

Please provide the actual execution plan as an attached sqlplan file. Maybe additional/different indexing will help.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 6

curious_sqldba

SSC-Dedicated

Points: 36494

May 15, 2011 at 2:54 pm

#1324778

i was looking more in general. Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 7

As a more general answer you might want to have a look at Gails blog[/url] regarding left outer join vs not exists. 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

sqldba_icon (5/15/2011)
i was looking more in general. Thanks

In general, it's better to return the correct data.

Now once you read Gail's post you'll realize that the optimizer's pretty smart and will <usually> pick the same plan no matter what you throw at him.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

sqldba_icon (5/15/2011)
i was looking more in general. Thanks

General performance tuning advice:

Ensure the queries are written so that they can use indexes

Ensure the indexes are designed for the queries

Don't do row-by-row operations unless there's absolutely no other way

Don't try to out-smart the optimiser

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Ninja's_RGR'us (5/15/2011)
In general, it's better to return the correct data.

*snigger*

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

GilaMonster (5/15/2011)
Ninja's_RGR'us (5/15/2011)
In general, it's better to return the correct data.
*snigger*

Well it always depends!

I've never had a client who didn't care about 100% accuracy but I know it can happen.

I felt it needed pointing out in this thread, call it intuition or call me crazy :hehe:.