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