FROM vs WHERE Selection Criteria

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

FROM vs WHERE Selection Criteria

Author

Topic

TeenageKicks
Starting Member

9 Posts

Posted - 2006-01-27 : 06:32:37

Hi,

While playing with SQL Server 2000 I found you can specify the selection criteria in either the FROM clause or the WHERE clause:
e.g.
select *
from Table1 a inner join Table2 b ON a.key = b.key and a.field = 1

Is logically the same as:
select *
from Table1 a inner join Table2 b ON a.key = b.key
where a.a = 1

Any comments on which is best, and why?

Thanks,

Chris

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-27 : 06:38:38

There wont be any problem if you use Inner join. But if you use Left join then second method is good to go

Madhivanan

Failing to plan is Planning to fail

TeenageKicks
Starting Member

9 Posts

Posted - 2006-01-27 : 07:32:53

Thanks for the reply madhivanan. I hadn't played with OUTER joins in the first method so it's useful to know that it could return different results. I was just curious as I'd never seen it used before.

Being from an Oracle background and a bit of a purist I prefer the second method as I think it's more understandable and it's what the WHERE clause is for!

I wondered if it had any impact on performance, etc.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-01-27 : 09:22:12

In case of INNER JOINs there might be one method slightestly faster during compile time. However as the execution plans are identical, so should be execution time ceteris paribus.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources