Just a brief break from Swift programming — I stumbled across an interesting SQL shortcut the other day.
I was exploring a SQL table, trying to understand what kind of data was contained therein. I was doing things of the form:
select count(*) from thetable where description like ‘%thing%';
count
-------
60
(1 row)
(I’m using PostgreSQL here, but I’m pretty sure this would work anywhere). And this was then often followed by
select count(*) from thetable where description not like ‘%thing%';
count
-------
16
(1 row)
Which would have been OK, except that I remembered that
select count(*) from thetable;
count
-------
80
(1 row)
Where were the missing rows? Oh, right! Nulls are special — they are neither equal nor not-equal to regular values. So that meant:
select count(*) from thetable where description is null;
count
-------
4
(1 row)
I was constantly running the same comparison repeatedly to get the equal / not-equal / null breakdowns for various columns. And then I realized that:
select description like ‘%thing%’ as f, count(*) from thetable group by f;
f | count
---+-------
∅ | 4
t | 16
f | 60
(3 rows)
I could create the comparison as a field, then group by that field. There would be three possible values (true, false, and null) — so one pass would give me my counts.
I also prefer the way it reads. The thing I’m counting is at the beginning of the phrase, not the end — where it feels more natural. And this way, I don’t forget to check for the null case (which I often forget) — I get it for free.