fbpx
dirtSimple.orgwhat stands in the way, becomes the way
The Not-So-Secret Truth About SQL

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.)

Join the discussion
5 comments
  • That’s not really a secret about SQL but it is a gotcha. Well-designed databases shouldn’t have nullable columns, but you have to know how to deal with them because not all databases are well-designed. And outer joins can introduce nulls even when the underlying relations don’t allow nulls.

    The coalesce function is your friend here.

    select … where (coalesce(arrivals, 0) > 0 or coalesce(sales, 0) > 0) …

  • create table (id int, val int);
    insert into t1 values (1, null);
    insert into t1 values (null, 1);
    select * from t1 where id > 0 or val > 0;
    +——+——+
    | id | val |
    +——+——+
    | 1 | NULL |
    | NULL | 1 |
    +——+——+
    2 rows in set (0.00 sec)

    Looks fine to me!

  • coalesce is a Transact-SQL construct used in SQL Server and associates. It’s also a function in PostgreSQL

    Alternatively you can use ISNULL in SQL Server, but check the documentation first.

    If it’s Oracle you would use NVL.

    In MySQL you would use IFNULL.

    If you are using SQLite you are out of luck.

  • pje: i really, really enjoy your blog, both the programming insights and the process/big picture insights. thanks.

    i tried sureshvv’s example in sqlite (ver 3.5.7), and it works fine.

dirtSimple.org

Menu

Stay In Touch

Follow our feeds or subscribe to get new articles by email on these topics:

  • RSS
  • RSS
  • RSS

Delivered by FeedBurner

Get Unstuck, FAST

Cover photo of "A Minute To Unlimit You" by PJ Eby
Skip to toolbar