sql join doubt - 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

sql join doubt

Author

Topic

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-10-23 : 03:33:12

Hi all,
can u solve my doubt regarding join

What is the difference between the two queries below

select * from table1 inner join table2
on table1.id=table2.id
AND table1.status=1

select * from table1 inner join table2
on table1.id=table2.id
WHERE table1.status=1

Shall above two queries give the same result every time.?

thanks in advance....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 03:40:29

because its inner join i dont think it really matters if condition is given on WHERE or ON clause.however, in case of outer joins they yield different results.

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-10-23 : 04:37:09

ok,
if i am using left join, will the above query gives different results..
as below

select * from table1 inner join table2
on table1.id=table2.id
AND table1.status=1

select * from table1 left join table2
on table1.id=table2.id
WHERE table1.status=1

if the query gives different results , what was the reason for tat...

ok tanx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 04:41:01

see this

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-10-23 : 05:28:45

ok
tanx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 05:32:03

welcome