lundi 23 août 2021

(SQL, BigQuery) Is there a meaningful difference between CASE and IF (conditional expressions)?

I was looking at the BigQuery documentation on conditional expressions: https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions

It outlines CASE and IF as below:

1. CASE, as a 'switch' statement

CASE expr
  WHEN expr_to_match THEN result
  [ ... ]
  [ ELSE else_result ]
END

2. CASE, as an if-then-else tree

CASE
  WHEN condition THEN result
  [ ... ]
  [ ELSE else_result ]
END

3. IF, as expected

IF(expr, true_result, else_result)

Is there any meaningful difference between 2. and 3. above?

  • are there any real implications, in performance or otherwise?
  • are there are widely preferred conventions in their usage, or is it mostly up to personal/team's preference?

I suppose if there is a relatively deep if-else tree then 2. might be a bit cleaner to write, as it doesn't come with trailing close-parentheses:

CASE
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  WHEN condition_3 THEN result_3
  WHEN condition_4 THEN result_4
  WHEN condition_5 THEN result_5
  WHEN condition_6 THEN result_6
  ELSE default_result END

↑ above is a perhaps a little cleaner than below ↓

IF(condition_1, result_1
  IF(condition_2, result_2
  IF(condition_3, result_3
  IF(condition_4, result_4
  IF(condition_5, result_5
  IF(condition_6, result_6,
  default_result))))))

Aucun commentaire:

Enregistrer un commentaire