Once again: I should lay off the SQL discussions on social media

LinkedIn is the worst for these SQL "hot takes." Below is a screenshot of a recent such hot take, and below that is my hot take on the hot take.

This one wouldn't bother me so much if the poster hadn't painted those who prefer the style that they dislike as "inexperienced." If they had just said, "I prefer this style and here's why" - sure, fine. But instead they chose to deride those who disagree. Look at the file names at the bottom of the post even: inexperienced.sql vs. experienced.sql. 🙄 It just feels condescending.

But here's the thing - the preference to search by WHERE IS_ACTIVE vs. WHERE IS_ACTIVE=TRUE may not be confined to the realm of style. It may touch upon performance as well. But like most things performance in SQL, it depends on the implementation (i.e. the DBMS/SQL engine).

Below is my reply. I had to leave out some details and clarifications because I was fighting the LinkedIn character limit, but here goes:

Disagree, because I work with MySQL which processes WHERE bool less efficiently. It's not a universal problem-there's likely no discrepancy in most SQL engines, so fair enough if you prefer that style and it causes no issues for you. (SQL best practices should not be discussed universally IMO.)

In MySQL I see THIS take ~500 ms:

EXPLAIN ANALYZE
SELECT COUNT(*) FROM MY_TABLE WHERE IS_ACTIVE = TRUE;

-> Aggregate: count(0) (cost=456525 rows=1) (actual time=511..511 rows=1 loops=1)
-> Covering index lookup on MY_TABLE using IDX_IS_ACTIVE (IS_ACTIVE=true) <snip>

And THIS takes ~650 ms, because it results in a more complex search expression, and a more complex execution plan:

EXPLAIN ANALYZE
SELECT COUNT(*) FROM MY_TABLE WHERE IS_ACTIVE;

-> Aggregate: count(0) (cost=683424 rows=1) (actual time=657..657 rows=1 loops=1)
-> Filter: (0 <> MY_TABLE.IS_ACTIVE) <snip>
-> Covering index range scan on MY_TABLE using IDX_IS_ACTIVE over (NULL < IS_ACTIVE < 0) OR (0 < IS_ACTIVE) <snip>)

This behavior likely stems from MySQL's lack of a native boolean type - boolean is just an alias for tinyint - so in this example, IS_ACTIVE is a tinyint. Therefore if you do "WHERE <tinyint>" it says to check for any not null, non-zero value.

I received no reactions/replies. 

But what do I know. I'm just an inexperienced noob when I make MySQL queries faster by avoiding what the "experienced" people do. 🤷🏻‍♂️

Ultimately, if you prefer one style and it works better for you and your SQL engine/DBMS, then great! But maybe others see something you don't - something measurable and tangible, not just feels based! So don't dismiss others as inexperienced before learning the other point of view. 

Additionally, I'm a big believer that in all things SQL, one always has to qualify a statement with your choice of SQL engine. The world of SQL is wide and weird, so any time I feel like saying, "In SQL, you should always do this and not that, because it's only better and never worse...," I pause myself. I've seen enough weird stuff in my years, that I'd only feel comfortable qualifying such a statement with "...on Postgres," or, "...on MySQL," and so on.

The funny thing is I actually agree with the original poster in the sense that, if a column is a well-named boolean, such as IS_ACTIVE or HAS_POTATOES, purely stylistically I'd rather see WHERE IS_ACTIVE or WHERE HAS_POTATOES. I come from a Java background, in which the community generally encourages making your code as naturally readable as possible. So if (order.isActive()) is generally the preferred style over if (order.isActive() == true). But I certainly wouldn't refer to someone who preferred the latter Java syntax as "inexperienced." And as we've seen, in the realm of SQL, stylistic preferences can have real, rubber-meets-the-road implications that should not be ignored.