• ILAsoft.net Blog

    SQL - The Power of NULLs

Some people despise NULLs for their unusual behavior and apparent complexity, while some remain suspicious simply because of a lack of experience with them. Although at times it is not a good idea to use them in database design, they often come in handy in queries alone. They let us easily test for the “third case”- complete lack of data. Assume you have a bit column that only allows 0 and 1, so you can distinguish if something is “on” or “off”, what better way than the NULL is there for you to test for the row’s presence, or vice versa?..

[sql]SELECT *
FROM
BitTable
WHERE
BitColumn IS NOT NULL[/sql]

This approach can also be easily extended to filter vast chunks of data. Suppose you have a big table of users and a table of their last logins (i.e. audit). By left joining the latter and checking for the absent rows, you can say which users haven?t logged-in in the last 3 weeks (or never logged-in for that matter):

[sql]SELECT *
FROM
Users
LEFT JOIN AuditUsers
ON AuditUsers.UserID = Users.ID
AND ChangeDate > DATEADD(week, -3, GETDATE())
WHERE
AuditUsers.ID IS NULL[/sql]

As you can see, the possibilities are really endless. Here is another neat trick that probably deserves an article just by itself. I actually use this more often than I would like, but it does allow me to easily set “preferences” in the data output (or filter it altogether) based on seemingly unrelated data fields. All of this is done by simply having a specific CASE statement. Here is an example derived from the one above. This approach is best when used against multiple tables and columns, but this should suffice just to show how to use it. In this example we will first see users that never logged in at all, then all users who logged in over 3 weeks ago, and then all the rest. Note that you can also use TOP to limit just to the first couple of rows that come out, very handy:

[sql]SELECT *
FROM
Users
LEFT JOIN AuditUsers
ON AuditUsers.UserID = Users.ID
ORDER BY
CASE WHEN AuditUsers.ID IS NULL THEN 0
WHEN ChangeDate < DATEADD THEN 1
ELSE 2
END ASC[/sql]

Edited by Katya Pupko

Contact Me

Information you submit through the form below will only be used in answering the email.