where filter condition - 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

SQL Server 2005 Forums

Transact-SQL (2005)

where filter condition

Author

Topic

cognos79
Posting Yak Master

241 Posts

Posted - 2008-04-18 : 16:40:19

Is there a difference if I use filter in the join instead in where clause:

1)

select s.*
from students s
join courses c
on s.ssn = c.ssn
and c.type = 'fresher'

2)
select s.*
from students s
join courses c
on s.ssn = c.ssn
where c.type = 'fresher'

Thanks...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-18 : 16:43:59

No, they both will be returning the same records.
One thing that may affect the query plan is how many records that are stored in the two tables.

E 12°55'05.25"
N 56°04'39.16"

cognos79
Posting Yak Master

241 Posts

Posted - 2008-04-18 : 16:52:46

cool...thanks...thats what i thought

cognos79
Posting Yak Master

241 Posts

Posted - 2008-04-18 : 16:53:28

If there are more number of records in both the tables...which one do you say is better?

raky
Aged Yak Warrior

767 Posts

Posted - 2008-04-19 : 02:12:08

It is better to filter while joining rather than filtering after selecting.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-19 : 03:13:56

quote:
Originally posted by cognos79

Is there a difference if I use filter in the join instead in where clause:

1)

select s.*
from students s
join courses c
on s.ssn = c.ssn
and c.type = 'fresher'

2)
select s.*
from students s
join courses c
on s.ssn = c.ssn
where c.type = 'fresher'

Thanks...

It would matter only if you use OUTER joins

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources