The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,466 other followers

SQL Server inverse of (equals) is ((not equals) or (is NULL)

Posted by jpluimers on 2011/12/07

Usually getting queries right with SQL Server comes down to handling NULL correctly.

For instance, on this table:

MYTABLE
ID LAST_NAME
6 COUNT(*)
1 FOO
2 BAR
3 FOO
4 **NULL**
5 BAR
6 FOO

What are the results of these SELECT statements

SELECT COUNT (*)
FROM MYTABLE

SELECT COUNT (*)
FROM MYTABLE
WHERE     (LAST_NAME = 'FOO')

SELECT COUNT (*)
FROM MYTABLE
WHERE     (LAST_NAME <> 'FOO')

SELECT COUNT (*)
FROM MYTABLE
WHERE     NOT (LAST_NAME = 'FOO')

You might think they are like these, as LASTNAME <> ‘FOO’ looks like the inverse of LASTNAME = ‘FOO’:

  • 6
  • 3
  • 3
  • 3

But in fact the results are these:

  • 6
  • 3
  • 2
  • 2

When you look closely to the SQL statements below, you will see that the inverse of EQUALS contains an IS NULL, the same for the inverse of NOT EQUALS:

SELECT COUNT (*)
FROM MYTABLE

-- inverse of NOT EQUALS
SELECT COUNT (*)
FROM MYTABLE
WHERE     (LAST_NAME = 'FOO') OR (LAST_NAME IS NULL)

SELECT COUNT (*)
FROM MYTABLE
WHERE     (LAST_NAME <> 'FOO')

SELECT COUNT (*)
FROM MYTABLE
WHERE     (LAST_NAME = 'FOO')

-- inverse of EQUALS
SELECT COUNT (*)
FROM MYTABLE
WHERE     (LAST_NAME <> 'FOO') OR (LAST_NAME IS NULL)

-- inverse of EQUALS
SELECT COUNT (*)
FROM MYTABLE
WHERE     (NOT (LAST_NAME = 'FOO')) OR (LAST_NAME IS NULL)
  • 6
  • 4
  • 2
  • 3
  • 3
  • 3

Lesson learned: always take into account NULL when trying to formulate your SQL comparisons well.

–jeroen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: