Is there a way to simplify a NULL compare of 2 values

by

Yes you can, and you can get the optimizer to recognize it too.

Paul White has this little ditty:

WHERE NOT EXISTS (
    SELECT d.[Data]
    INTERSECT
    SELECT i.[Data])

This works because of the semantics of INTERSECT which deal with nulls. What this says is “are there no rows in the subquery made up of value B and value B”, this will only be satisfied if they are different values or one is null and the other not. If both are nulls, there will be a row with a null.

If you check the XML query plan (not the graphical one in SSMS), you will see that it compiles all the way down to d.[Data] <> i.[Data], but the operator it uses will have CompareOp="IS" and not EQ.

See the full plan here.

The relevant part of the plan is:

                <Predicate>
                  <ScalarOperator ScalarString="@t1.[i] as [t1].[i] = @t2.[i] as [t2].[i]">
                    <Compare CompareOp="IS">
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Table="@t1" Alias="[t1]" Column="i" />
                        </Identifier>
                      </ScalarOperator>
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Table="@t2" Alias="[t2]" Column="i" />
                        </Identifier>
                      </ScalarOperator>
                    </Compare>
                  </ScalarOperator>
                </Predicate>

I find the optimizer works very well this way round, rather than doing EXISTS / EXCEPT.

I urge you to vote for the Azure Feedback to implement a proper operator

More Related Contents:

  1. Max Nbr of Months W/ No Activity in Past 12 Months
  2. SQL server query to output the data shown below
  3. Will ANSI JOIN vs. non-ANSI JOIN queries perform differently?
  4. Find a value anywhere in a database
  5. How do I generate a random number for each row in a T-SQL select?
  6. Combine multiple results in a subquery into a single comma-separated value
  7. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  8. Floor a date in SQL server
  9. How to drop a table if it exists?
  10. Create a view with ORDER BY clause
  11. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  12. INSERT INTO vs SELECT INTO
  13. Possible to store value of one select column and use it for the next one?
  14. How FOR XML PATH(”) works when concatenating rows
  15. Scheduled run of stored procedure on SQL server
  16. SQL Server ORDER BY date and nulls last
  17. How to check if a database exists in SQL Server?
  18. How to remove accents and all chars a..z in sql-server?
  19. tsql returning a table from a function or store procedure
  20. How can I do something like: USE @databaseName
  21. How to split string and insert values into table in SQL Server
  22. How to determine the field value which can not convert to (decimal, float,int) in SQL Server
  23. How can I get the sum of multiple datetime values?
  24. Use of Begin / End Blocks and the Go keyword in SQL Server?
  25. How do you determine what SQL Tables have an identity column programmatically
  26. Ampersand (&) operator in a SQL Server WHERE Clause
  27. Avg of float inconsistency
  28. how to write number to word function in sql server [closed]
  29. Is there an “IF EXISTS” test for a linked server?
  30. SQL Server stored procedure return code oddity

Leave a Comment