whats the difference betwee these 2 sql statements

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 2000 Forums

Transact-SQL (2000)

whats the difference betwee these 2 sql statements

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-12-15 : 18:04:28

Hi friends
am trying understand the sql concept here.i've 2 tables (master and patient).master is parent and patient is one of child tables to master.
whats the difference between two following sql statements, are they same ? which is one most optimised.
look at conditions in bold (the both return results i want but curious to know the difference).Thanks

1) select master.someflds,patient.someflds
FROM dbo.master INNER JOIN
dbo.patient ON dbo.master.MASTERID = dbo.patient.FK_MASTER_MASTERID AND dbo.u_master.TYPE = 'p'

2)select master.someflds,patient.someflds
FROM dbo.master INNER JOIN
dbo.patient ON dbo.master.MASTERID = dbo.patient.FK_MASTER_MASTERID
where dbo.u_master.TYPE = 'p'

Cheers

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-12-15 : 18:16:05

The 2nd one is the preferred format. They'll return the same result set though. Join conditions should be in the ON portion and filtering should be in the WHERE clause.

Tara Kizer
aka tduggan

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-12-15 : 19:39:00

Not that it is relevant in your inner join, but good info nonetheless: http://www.sqlteam.com/item.asp?ItemID=11122

Nathan Skerl

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-12-18 : 14:57:29

Thank you Tara and Nathan for your posts.
Nathan,thats a nice article .Thanks

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources