SQL best practices – don’t compare count(*) with 0

submited by

Style Pass

2024-12-03 07:30:14

Lets consider what went through someones mind when they wrote it. This query will return all users, that have zero addresses. In other words – users that never provided any address.

The problem is counting. What will happen if an user has million addresses? Pg will dutifully count all of them, only then to reject the row, because there was something.

The thing is that you don't need to have million addresses. Even if there can be two – they still need to be counted, and while the time cost will be lower than counting million rows, it is still there, and it's 100% useless work.

Of course, in some (most?) cases, where people use this construct (0 == count), someone can say: “but it doesn't matter in our case, because…". Well, this is where best practices part of the title comes from. One shouldn't do wrong things, even if they don't matter in this particular case, at the very least not to get into habit of doing it, and then accidentally make the same mistake when it will actually be important.