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 1,649 other followers

Archive for December 7th, 2011

Trouble with Google Reader and GEXperts RSS feed :)

Posted by jpluimers on 2011/12/07

While catching up my feeds after an astonishing holiday around the Antarctic Peninsula (some photos at Flickr), I bumped into a Google Reader issue with the GExperts.com RSS feed: all posts seem to be stamped 20111117T0555.

Funny, as I remember being late to report GExperts 1.35 for Delphi XE2 was released a while a go :)

It reminded me to ask Thomas to publish the XE2 version of his Experimental GExperts build though.

Lets hope he is faster than me catching up :)

–jeroen

via: Google Reader feed for GExperts.org.

Posted in Delphi, Development, Software Development | 7 Comments »

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

Posted in Database Development, Development, SQL Server, SQL Server 2000, SQL Server 2008 | Leave a Comment »

 
%d bloggers like this: