SQL Server Performance

Question About NOT EXISTS and IS NULL (or IS NOT NULL)

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by LucasDa, Jun 9, 2009.

  1. LucasDa New Member

    Hi.
    I need some advices about the clauses NOT EXISTS and IS NULL or NOT NULL.
    If I have a statement with in the clause WHERE have a field (or a lot of fields) with IS NULL or IS NOT NULL, exist another way to eliminate this or replace with another statement more performance. For example:
    SELECT Name, LastName, DateofBirth
    FROM Customer
    WHERE Name IS NOT NULL
    AND LastName IS NOT NULL
    AND DateOfBirth I NOT NULL
    And the last question is that exist some statement more performance to replace the clauses NOT EXISTS. For example.
    SELECT Name, LastName, DateofBirth
    FROM Customer
    WHERE Name IS NOT NULL
    AND LastName IS NOT NULL
    AND DateOfBirth I NOT NULL
    AND NOT EXISTS ( SELECT Name FROM XXX WHERE XXX.Name = Customer.Name)
    Thank.
  2. MohammedU New Member

  3. sbright New Member

    MohammedU,
    I read the article you pointed. It is very helpful. Thanks!
    But I still don't know how to re-write the following Where clause. Any suggestions? Thanks! Sheenawhere status not in ('1', '9', '5','6')
    and
    hpcode not in ('CALO', 'COHF', 'HEPO')and
    proccode not like '7%'and
    proccode not like '8%'and
    proccode not like 'J%'and
    proccode not like 'L%'and
    proccode not like 'A%'and
    proccode not like 'Q%'
  4. Sandy New Member

    For: [quote user="sbright"]where status not in ('1', '9', '5','6') and
    hpcode not in ('CALO', 'COHF', 'HEPO')
    [/quote]
    Ans: You may not change your query because "IN" Keywords applied to 2 different columns.
    But For: [quote user="sbright"] and
    proccode not like '7%'and
    proccode not like '8%'and
    proccode not like 'J%'and
    proccode not like 'L%'and
    proccode not like 'A%'and
    proccode not like 'Q%'
    [/quote]
    Sheena, you can change this by using full text search command like below:WHERE
    NOT CONTAINS(proccode , ' "7*" and "8*" and "J*" and "L*" and "A*" and "U*" ')
    NOTE: Before running the query you need to create full text index on the procode column.
    Thanks,
    Sandy.
  5. Adriaan New Member

    These two syntactical elements have very different applications:
    You can use IS NOT NULL to evaluate the value of a field (or of an expression) within a row of a resultset.
    You can use NOT EXISTS to verify that the subquery after it does not return any rows.
    To "find unmatched rows", you can use NOT EXISTS or an outer join:

    SELECT t1.*
    FROM maintable t1
    WHERE NOT EXISTS (SELECT t2.* FROM checktable t2 WHERE t2.checkcolumn = t1.checkcolumn)
    SELECT t1.*
    FROM maintable t1
    LEFT JOIN checktable t2 ON t1.checkcolumn = t2.checkcolumn
    WHERE t2.checkcolumn IS NULL
    Performance should be comparable, so use the syntax you're most comfortable with.

Share This Page