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.
| Author |
Topic |
|
ujjaval
Posting Yak Master
108 Posts |
 Posted - 2007-12-18 : 02:11:17
|
| Hi all,Just wondering, can i get a query on two tables say Table A and Table B without using left outer join clause, I want to get the records from TAble A if there are no matching records in Table B. The common key can be called say personID and Table A and Table B has one to many optional relationship.Thanks,UJjaval |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2007-12-18 : 02:13:58
|
you can use Exists or In Clause for the same. Select * From TableA Where Not Exists ( Select 1 From TableB Where TableA.PersonID = TableB.PeronID) Chiraghttp://www.chirikworld.com |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-18 : 02:14:36
|
| [code]SELECT {fields}FROM TableAWHERE PersonID NOT IN (SELECT DISTINCT PersonID FROM TableB)[/code] |
|
|
|
ujjaval
Posting Yak Master
108 Posts |
Posted - 2007-12-18 : 02:21:54
|
| thanks for your replies.Just wondering, which one will be faster? the other way or using the left outer join clause? why? |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2007-12-18 : 02:24:51
|
| Its depends on the data, but normally i have seen that Exists is much more faster then other methodologies. But it may be different for some one else.Chiraghttp://www.chirikworld.com |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-18 : 02:34:09
|
| Its always better to use JOIN rather than nonsargable clauses like NOT EXISTS,NOT IN etc where SQL engine can make use of an use an available index and perform faster. |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2007-12-18 : 02:44:40
|
quote:
Originally posted by visakh16
Its always better to use JOIN rather than nonsargable clauses like NOT EXISTS,NOT IN etc where SQL engine can make use of an use an available index and perform faster.
I guess you require to read this .. http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspxChiraghttp://www.chirikworld.com |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-18 : 02:49:27
|
| i was refering to this:-[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ssceqpop.mspx[/url] |
|
|
|
|
|
|
left outer join without left outer join clause