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.