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 |
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
 Posted - 2007-02-28 : 04:10:38
|
| What is the verdict on the best method to find out if a record does not exist in a table. I just read about the "Except" operator, and am curious how this compares with both the "Exists" operator, and also a left join filtering out the null records.My assumption the new Except operator is the best method because it seems to be what is now leaning towards, but would everyone agree this will give the best performance? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-28 : 04:17:03
|
| Compare execution plan and IO statistics for all three operations and check which one is best.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-02-28 : 17:02:43
|
| I'm actually generalizing the question. I am trying to find out what the best practice is, not what is best for a specific query. Any information would be appricated. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2007-02-28 : 18:08:36
|
| I can have a go at this...You are generalizing the question,so generally speaking, the EXISTS operator is ANSI SQL and will cater for all variants of the problem:"method to find out if a record does not exist in a table"and it will be fast(est) (imc), at least in sql server.There is a problem with the EXCEPT operator in that it can only retreive a distinct set of the columns provided, not all the columns of the "left" table.But it has an extremely nice syntax for comparing sets, and is a GREAT new operator!Comparing LEFT JOIN approach and EXISTS, the left join can accomplish well for: "method to find out if a record does NOT exist in a table",but does not work well for "method to find out if a record DOES EXIST in a table". So is less general (imo).For IN / NOT IN, I just use it for ad-hoc queries or very simple stuff, never in production code.rockmoose |
|
|
|
|
|
|
|
|
Except vs. Exists vs. Left Join