Boolean data type in BigQuery

I remember that one of the things that struck me some years ago when switching from SQL Server to BigQuery was the existence of the bool data type in the latter, which I didn't have before.

I still see from time to time BigQuery code that does not leverage it to the fullest.

For example, this means you can just say val_b > val_a AS is_val_b_higher for a boolean flag instead of comparing and determining TRUE/FALSE. Also, just WHERE is_val_b_higher instead of WHERE is_val_b_higher IS/= TRUE.

This, in my opinion, makes the query much more readable when working with boolean flags.

If you properly name the flags, reading the query feels much closer to natural language.

BigQuery:

WITH input_data AS (
  SELECT
    1 AS val_a,
    2 AS val_b
),

bool_flags AS (
  SELECT
    val_a,
    val_b,
    val_b > val_a AS is_val_b_higher
  FROM
    input_data
)

SELECT val_a, val_b , is_val_b_higher

FROM bool_flags

WHERE is_val_b_higher

SQL Server:

WITH input_data AS (
  SELECT
    1 AS val_a,
    2 AS val_b
),

bool_flags AS (
  SELECT
    val_a,
    val_b,
    --   val_b > val_a AS is_val_b_higher -- NOT POSSIBLE, ERROR
  CAST(IIF(val_b > val_a, 1, 0) AS BIT) AS is_val_b_higher

  FROM
    input_data
)

SELECT val_a, val_b , is_val_b_higher

FROM bool_flags

WHERE
    --is_val_b_higher -- ALSO NOT POSSIBLE, ERROR
    is_val_b_higher = 1