Monday, December 10, 2007

The Not-So-Secret Truth About SQL

The other day I was helping my wife debug a report she was trying to set up in her store operations software, and there was something screwy about the totals.

She was trying to set up an inventory change report that would reflect the changes of inventory levels at the store, for items that are also listed on her website, so she could update the website when items sold out or came back in.

For some reason, though, only the inventory arrivals were showing, not the sales.  I went in and changed the selection criteria to only select the sales, and that worked.  I put back in the other part of the query, something like "(arrived>0 or sales>0)", and then only the arrivals showed.

Frowning, I took out the "arrived>0" part again.  The sales showed up.  Then, just for the heck of it, I changed the query to "(sales>0 or arrived>0)", and only the sales showed up.

"This is weird," I said.  "You know, I'll bet NULLs are probably the problem somehow."

And she said, "What makes you say that?"

I laughed.  "Because with SQL, NULLs are nearly always the problem."

(Postscript: I'll leave it as an exercise for the reader to figure out how they were the problem.  It'll be good for your SQL chops.)