July 9, 2007 at 2:28 pm
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)
July 9, 2007 at 2:38 pm
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
July 9, 2007 at 2:39 pm
(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
July 9, 2007 at 2:49 pm
Also NOT IN can cause serious performance issues, you're always better off using a left anti-semi join as Daryl illustrated.
July 10, 2007 at 1:55 am
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!
July 10, 2007 at 5:13 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy