Is there an equivalent sql check to python’s concept of truthy / falsy / bool?

by

The question:

I’m trying to find the usage of particular columns in a mysql database connected to python. Python counts the following to be ‘falsy’:

  • Empty lists: []
  • Empty tuples: ()
  • Empty dictionaries: {}
  • Empty strings “”
  • Integer: 0
  • Float: 0.0
  • None
  • False

I realise this is somewhat ORM dependent (e.g. empty datatypes are stored as NULL rather than a string) but is the below somewhat equivalent, is there a better way and is there anything obvious I’m missing? Maybe 0.0? The idea being to calculate a row_count and subtract this result. I tried NOT IN for truthy but didn’t seem to get a consistent result.

SELECT COUNT(*) FROM `tabBatch` WHERE naming_series IN ('', 0, NULL);

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

  • MySQL returns 3 types of “true/false”: 0 (false), 1 (true), NULL.
  • In most situations where NULL is involved, the result will be NULL.
  • You can test for NULL only with these: IS NULL, IS NOT NULL, <=>. (OK, I may have forgotten something.) See also, COALESCE().
  • When a column is declared NOT NULL, it cannot store a NULL; attempts to do so may store 0 / 0.0 / ”.
  • Try to avoid the inefficient WHERE x IS NULL OR x = 0; pick one or the other for your ‘exception’ case.
  • There are many possible uses for NULL; let’s discuss your particular business logic. This general discussion may not be sufficient.
  • If a subquery might return no rows, consider whether EXISTS(SELECT 1 FROM .. WHERE ...) would work faster. It always returns 0 or 1.
  • NULL values are ignored by certain aggregates (see GROUP BY).

Run these to see some examples:

SELECT NULL IN (1, 2);  --> NULL
SELECT 1 IN (1, NULL);  --> 1
SELECT 1 IN (2, NULL);  --> NULL
SELECT NULL IN (1, NULL); --> NULL

Think of it this way, NULL represents any possible value; it does not know which one.

Method 2

In the end I found this did what I needed as I needed to use it multiple times in queries and it had to cope with various field types (you can’t CAST down from CHAR to INT, only up it seems).

The COALESCE is for tables where there are no rows, SUM returns NULL in this case. This turned out to be useful because in another application I wanted to distinguish between zero rows (SUM = NULL) and rows where all were empty (SUM = 0).

sql_truthy = """
  COALESCE(SUM(
    CASE WHEN `{field}` IS NULL THEN 0
         WHEN CAST(`{field}` AS CHAR) IN ('0', '0.0', '') THEN 0
         ELSE 1
    END
  ), 0) AS used"""

The advantage to using this as a column field rather than a WHERE clause is it could be used multiple times in the same query for multiple fields. The WHERE clause needs a query for each field.

Bonus: I used a COUNT(*) as total to find the total rows in the same query.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment