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.)
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!
What database are you using there?
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.