4

What is a canonical expression for the following:

NULL  -> false
false -> false
true  -> true

What I am using is:

select (foo is not null and foo)

which seems to work fine, but I'm wondering if there is a standard convention for this.

gcbenison
  • 11,723
  • 4
  • 44
  • 82
  • mysql has `COALESCE(foo, false)` http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce – dnagirl Mar 06 '13 at 13:12
  • Methinks you probably have the most elegant solution - other solutions would tend to use vendor-specific methods/functions (e.g. coalesce) and therefore could not be considered universal convention. – Raad Mar 06 '13 at 13:16
  • btw +1 for the smart solution ;) – Andrea Ligios Mar 06 '13 at 13:27
  • @raad my solution may be simplest for my example, but I still like `coalesce` for more complex boolean expressions, i.e. you can do `coalesce(foo or bar or baz, false)` – gcbenison Mar 06 '13 at 13:41
  • Actually `COALESCE` is far more widely available that I originally thought, so you're right - it is a good solution! – Raad Mar 06 '13 at 13:46

4 Answers4

5
select coalesce(foo, false) from table
chue x
  • 18,573
  • 7
  • 56
  • 70
1

I just looked it up. COALESCE() is ansi SQL. So SELECT (foo, false) would give foo or false.

dnagirl
  • 20,196
  • 13
  • 80
  • 123
1

If with canonical you means a standardized way, not DBMS/vendor-depending, then use CASE WHEN.

It is ANSI, and easy:

SELECT CASE WHEN (foo is null) 
            THEN false 
            ELSE foo 
       END 
       AS YourStuff
...

Or Coalesce as stated in the other answers.

Their performance are identical: Coalesce vs Case

Community
  • 1
  • 1
Andrea Ligios
  • 49,480
  • 26
  • 114
  • 243
0

You can use COALESCE or ISNULL

For MySQL you can also use IFNULL

SELECT COALESCE(foo,false)

or

SELECT ISNULL(foo,false)

or

SELECT IFNULL(foo,false)
Gabriele Petrioli
  • 191,379
  • 34
  • 261
  • 317