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 |
|
baja_yu
Starting Member
24 Posts |
 Posted - 2010-03-19 : 18:19:40
|
Hi guys,I'm trying to recreate a statement that uses subqueries to use Joins instead. What I have is two identicaly structured tables (fields: FirstName, LastName, Email, Status, ID), named "Old" and "New". What I need is all records from table "New" in which "Email" is not already found in "Old". Currently I can get it with:SELECT *FROM new WHERE [Status] = '1' AND (id >= 0 AND id <= 10000) AND email NOT IN (SELECT email FROM old WHERE email IS NOT NULL) ORDER BY id desc I'd like to rework this to use Joins instead. What I would need is something like a cross-section of an Outer and Inner Joins of the two tables. Also, if it affects the solution, I plan to exapnd this later to Join three tables (New, Old, Old2; Listing records from new not found in Old or Old2).Thanks in advance for any suggestions.P.S: I'm working with MS SQL 2000. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
baja_yu
Starting Member
24 Posts |
Posted - 2010-03-19 : 19:35:44
|
| Hi,thanks for the suggestion. What I'm hoping to achieve over the original method I posted is processing speed. The tables in question are quite large and I was told that JOIN would be faster than subqueries. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
baja_yu
Starting Member
24 Posts |
Posted - 2010-03-20 : 10:08:07
|
| Tested both methods, both produce the same results, and take the sime time to process. I'll give Joins one more try if I manage to figure out the rigth query, but maybe this is as fast as it is going to get.Thanks again tkizer! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Replacing subqueries with Joins