Re: Select CASE when null ?

On Wed, Jan 14, 2009 at 03:56:25PM -0500, Mark Styles wrote:
> SELECT COALESCE(mid,0) AS mid, COALESCE(id_group,0) AS id_group
> FROM users
> WHERE username = 'test'
> UNION
> SELECT 0, 0
> WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'test');

An alternative using outer joins would be:

  SELECT COALESCE(mid,0) AS mid, COALESCE(id_group,0) AS id_group
  FROM (SELECT 1) x
    LEFT JOIN users ON username = 'test';

Unions tend to preclude various optimisations so I'd tend to stay away
from them where possible.  This query will also only perform only one
index scan of users, rather than two.

--

  Sam  http://samason.me.uk/