Search code examples
sqlpostgresqltypesquery-optimizationboolean-logic

Optimize query with null and false checks in WHERE clause


Can I make the following query shorter and/or optimal?

WITH myvars (t_state, t_hv_involved, num_vehicle_hv )
AS (VALUES ('TAS', null, null))

SELECT * FROM safety.crash_summary_view c, myvars 
WHERE jurisdiction = t_state
AND ((t_hv_involved::boolean = TRUE AND c.num_vehicle_hv > 0)
   OR t_hv_involved is null
   OR t_hv_involved::boolean = FALSE)

If t_hv_involved is true then it should filter on num_vehicle_hv > 0.
If t_hv_involved is false or null, do not filter.


Solution

  • ...
    AND   (t_hv_involved IS NOT TRUE OR c.num_vehicle_hv > 0)
    

    Assuming t_hv_involved is type boolean as it should, you don't need to cast.

    The manual:

    boolean IS NOT TRUEboolean

    Test whether boolean expression yields false or unknown.
    true IS NOT TRUEf
    NULL::boolean IS NOT TRUEt (rather than NULL)

    The complete test case could be:

    SELECT *  -- you really need all columns?
    FROM   safety.crash_summary_view c
    CROSS  JOIN (
       VALUES
         ('TAS', null::bool, null::numeric)
       ) v (t_state, t_hv_involved, num_vehicle_hv)
    WHERE  c.jurisdiction = v.t_state
    AND   (v.t_hv_involved IS NOT TRUE OR c.num_vehicle_hv > 0);
    

    Note the explicit type declaration in the first row of the VALUES expression. I didn't cast 'TAS' as that defaults to type text anyway. You may want to be explicit and cast that, too.
    Additional rows can be untyped literals - but boolean values can't be quoted.
    See:

    Of course, c.num_vehicle_hv > 0 is null while you pass null for num_vehicle_hv, and the test returns no rows with v.t_hv_involved IS TRUE.