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 |
|
Sun Foster
Aged Yak Warrior
515 Posts |
 Posted - 2010-07-28 : 15:03:22
|
| I need to compare two tables. Data in the table of OrderHist always more than data in the table OrderCurrent.I used code below to compare:select ID from OrderHist h where not exists (select ID from OrderCurrent c where c.ID=h.ID)Can this code be improved? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-28 : 15:40:47
|
If you want to see ID's from OrderHist that not exist in OrderCurrent then your query is like I would do it 
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-29 : 01:56:30
|
| select ID from OrderHist h LEFT OUTER JOIN OrderCurrent con c.ID=h.IDWHERE c.ID IS NULLwhich comes to the same thing, so purely a matter of choice.We tend to use this format where there is also code ("nearby") which is comparing records that DO exist in both - i.e. using an INNER JOIN - because the code being the same, except for INNER / OUTER join, makes it easier to cut & paste any changes that are made in maintenance.But whichever you prefer really.If you need to include records which are in OrderCurrent and not in OrderHist (presumably "impossible" but could occur thus worth reporting on?) then you could use a FULL OUTER JOIN |
|
|
|
|
|
|
Another way to compare two table?