difference between on & where clause in left join

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

difference between on & where clause in left join

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-10-09 : 11:07:02

The following two similar codes which I thought same produces two different outputs.

I wonder what the different is then between them.


select personid from table1 t1
left join table2 t2
on t1.personid = t2.personid and t2.personid is null
where date between '21020801' and '20120805' and t1.personid like '95*'

second code :


select personid from table1 t1
left join table2 t2
on t1.personid = t2.personid 
where date between '21020801' and '20120805' and t1.personid like '95*' and t2.personid is null

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-10-09 : 11:58:16

[code]The First will show data from Table1 with respect to the referencing data from Table1 to Table2.
Second will Show data from Table1 BUT excluding the the JOIN data.

Here's a good example:
http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins[/code]

--------------------------
Joins are what RDBMS's do for a living

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-10-09 : 12:41:11

The first one still seems confusing. The second one it seems I have got it now. Thank you very much. Very nice examples with images and pictures.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-10-09 : 13:42:44

you are welcome

--------------------------
Joins are what RDBMS's do for a living

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources