Checking for a Null value in SQL

Yesterday I fell into a very simple but dangerous SQL trap.  Can you identify it?

SELECT first_column, another_column
FROM my_table
WHERE first_column = 1
AND (another_column >= 100 OR
another_column = null)

Found it?  It’s the comparison to null.  My intention was to get rows where another_column was null, however the query did not do so.  It didn’t give me any results where another_column was null, but didn’t give me any error either.  Why?

Null in SQL is not a real value.  Null means “lack of a value” or “unknown”. Anything compared to null leads to an “unknown” result. Therefore, in SQL there are actually three values conditions can evaluate to: true, false, and unknown/null. This is called three-valued logic. SQL WHERE clauses only return rows that evaluate to true, not rows that evaluate to false or “unknown”.

The proper way of checking for null is by using the IS NULL or IS NOT NULL predicates. The correct SQL query would have been as follows:

SELECT first_column, another_column
FROM my_table
WHERE first_column = 1
AND (another_column >= 100 OR
another_column IS null)

So if you’re used to languages where you check for null with equal signs, remember that in SQL, you must use the special IS NULL or IS NOT NULL predicates instead of the equals sign or other comparison operators. Don’t get tripped up like I did.

If you want to read more about, Wikipedia has a detailed article on null in SQL.

App Remote – Unleashing the Apple Remote

I’ve been using Sofa Control for my Apple Remote since I bought my Mac in 2009.  I use Sofa Control pretty much every week when I volunteer with the youth ministry at my church.  The Apple Remote by itself only works with certain programs that are designed to handle its input and it lacked support for other programs we were using such as OpenSong, which we use for song lyrics.  So I bought a license for Sofa Control.

Recently Sofa Control’s move to the App Store required all existing customers to repurchase the license.  I didn’t want to shell out another $20 for this small utility (as great as it has been, a total of $40 for this utility is a little pricy, considering the latest OS X Mountain Lion upgrade only cost $20), so I decided to see if I could program one myself.

I liked the feature that the application-specific handles were scripted.  I also liked how it was small and tucked away as a menu bar extra.  I definitely wanted to keep these features in my own version.

Yesterday (Labour Day, a statutory holiday) was the perfect day to work on this project.  I started off looking for some APIs to hook into the Apple Remote events.  Quite quickly, I found an Apple Remote wrapper written by Martin Kahr, the developer who created Sofa Control.  (Actually I lied; I looked for Apple Remote libraries weeks ago to determine the feasibility of the project, but I did have to find them again yesterday).

Then I looked for an easy scripting language to embed to receive the events generated from the Apple Remote.  I decided to go with AppleScript, the Mac OS built-in scripting language for two reasons.  First, AppleScript already has hooks to script actions in various applications.  Second, Cocoa already has classes to handle execution of AppleScript.

With two-thirds of my application already complete, I only needed to write a small delegate to receive events from the Apple Remote, and call the appropriate AppleScript.

About 200 lines later, I had a working prototype.  Honestly, there isn’t really much to show in terms of screenshots…

The source code and other technical stuff can be found on my GitHub account.  The binary can also be downloaded from Github (beware currently there isn’t much help or instruction with the package).

So with that, I conclude yesterday was a productive Labour Day 🙂