SQL Kiwi (8/22/2012)

---

sqlfriends (8/22/2012)

---

Because I have quite a few <> comparisons in my sql, ( in my post it is just a part of them), if I write something like :

Student.WithdrawDate <> esis.WithdrawDate

OR (student.WithdrawDate is NULL AND esis.WithdrawDate is NOT NULL)

OR (student.WithdrawDate is NOT NULL AND esis.withdrawdate is NULL)

It will get very long, is it an easier way to do it?

can I use isnull(student.mailingaddress,'')<>isnull(esis.mailingaddress,'')

There is a better, and easier way to do it using NOT EXISTS and INTERSECT. I describe the technique here: http://bit.ly/EqualityComparison

This handles NULLs correctly, means you don't have to find a 'magic value' to use with ISNULL or COALESCE, and is SARGable. The basic pattern is as follows, but please read the full blog post to make sure you understand how and why it works.

WHERE NOT EXISTS

(

SELECT

Student.SchoolID,

Student.GradeLevel,

Student.LegalName,

Student.WithdrawDate,

Student.SPEDFlag,

Student.MailingAddress

INTERSECT

SELECT

esis.SchoolID,

esis.GradeLevel,

esis.LegalName,

esis.WithdrawDate,

esis.SPEDFlag,

esis.MailingAddress

)

Listing the columns is easy in SSMS (drag them from the object explorer to the query pane). In the case where all columns are significant, you can also use the star syntax:

WHERE NOT EXISTS

(

SELECT

Student.*

INTERSECT

SELECT

esis.*

)