use NOT IN for multiple fields?
-
How do you select from a table where multiple fields do not appear in a corresponding table? (because of a unique index on Field1, Field2, Field3). I want to accomplish something like the following:
SELECT Type_ID
FROM Db1.dbo.TypeTable
WHERE Field1, Field2, Field3 NOT IN
(SELECT Field1, Field2, Field3
FROM Db2.dbo.TypeTable)
-
NOT IN does not allow multiple choices.
The following Join logic MAY get you the logic you want.
SELECT Type_ID
FROM Db1.dbo.TypeTable t1
LEFT JOIN
(SELECT Field1, Field2, Field3
FROM Db2.dbo.TypeTable) t2
ON t1.field1 = t2.field1 and t1.f2 = t2.f2 and t1.f3 = t2.f3
WHERE t2.field1 is null
-
(DUH) think homer simpson
no need for derived table
SELECT Type_ID
FROM Db1.dbo.TypeTable t1
LEFT JOIN
Db2.dbo.TypeTable) t2
ON t1.field1 = t2.field1 and t1.f2 = t2.f2 and t1.f3 = t2.f3
WHERE t2.field1 is null
-
Also NOT IN can cause serious performance issues, you're always better off using a left anti-semi join as Daryl illustrated.
-
I second that - Not In's can kill you. Avoid non-sargable queries.
http://www.sql-server-performance.com/transact_sql.asp
"Try to avoid WHERE clauses that are non-sargable. The term "sargable" (which is in effect a made-up word) comes from the pseudo-acronym "SARG", which stands for "Search ARGument," which refers to a WHERE clause that compares a column to a constant value. If a WHERE clause is sargable, this means that it can take advantage of an index (assuming one is available) to speed completion of the query. If a WHERE clause is non-sargable, this means that the WHERE clause (or at least part of it) cannot take advantage of an index, instead performing a table/index scan, which may cause the query's performance to suffer.
Non-sargable search arguments in the WHERE clause, such as "IS NULL", "", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'" generally prevents (but not always) the query optimizer from using an index to perform a search. In addition, expressions that include a function on a column, expressions that have the same column on both sides of the operator, or comparisons against a column (not a constant), are not sargable."
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Thanks to all for input, especially Daryl for a solution that works
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply