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 |
|
Nroblex
Starting Member
17 Posts |
 Posted - 2009-09-05 : 14:12:53
|
| Hello gurus!I have two identical tables. Table1 is a backuptable containing complete data, table2 is a production table with some data loss.I have the two following key columns (in both tables)document_number, status_codeI want to run a optimal query, figuring out which rows are missing in table2 based on the key columnsDoes this work? And what about performance?Select document_number, status_code from table1 wheredocument_number, staus_code not IN (select document_number, status_code from table2)Many Thanks in advance! |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-05 : 14:26:32
|
IN() with more than a few 1000 rows will have poor performance.WHERE NOT EXISTS is faster.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-05 : 14:27:16
|
And you cannot use more than one column in IN()
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-05 : 14:30:34
|
Select document_number, status_code from table1 t1where not exists (select * from table2 t2 where t2.document_number = t1.document_number and t2.status_code = t1.status_code)
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-09-06 : 13:58:25
|
| [code]Select distinct t1.document_number, t1.status_code from table1 t1left join table2 t2on t2.document_number = t1.document_numberand t2.status_code =t1.status_code where t2.document_number is null[/code] |
|
|
|
|
|
|
SubSelect query