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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-20 : 09:42:24
|
| Stewart writes "I am trying to get the elements of table 2 that are not members of table 1. Basically, I am trying to get the logical complement on (select * from table 1 INNER JOIN table 2 on table 1.<column>=table 2.<column). While this can be accomplished using a NOT IN clause in the select i.e.,select * from table 1 where <column> not in (select <column> from table 2),using a large table 2, I believe the nested select will hog resources even if indexed properly. Any suggestions or observations would be greatly appreciated" |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-03-20 : 10:17:52
|
| select * from table2 where not exists (select * from table1 where table2.commonfield = table1.commonfield) |
 |
|
|
davidpardoe
Constraint Violating Yak Guru
324 Posts |
Posted - 2002-03-20 : 11:12:25
|
How about...select * from table1right outer join table2 on table1.commonfield=table2.commonfieldwhere table1.commonfield is null (This returns all rows in table2 that are not in table1 - using commonfield to define the rows)============The Dabbler! |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-03-20 : 11:41:54
|
| same logic, but andrew's correlated subquery using NOT EXISTS() is slightly faster.setBasedIsTheTruepath<O> |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-03-20 : 15:43:08
|
| ...but they have both got table1 and 2 swapped compared to the original using NOT IN. Assuming table2.commonfield is not nullable, the NOT IN version yields the same query plan as the equivalent NOT EXISTS version.Edited by - Arnold Fribble on 03/20/2002 15:43:50 |
|
|
|
|
|
|
complement of join