MySQL IF NOT NULL, then display 1, else display 0

SELECT c.name, IF(a.addressid IS NULL,0,1) AS addressexists
FROM customers c
LEFT JOIN addresses a ON c.customerid = a.customerid
WHERE customerid = 123

Careful if you're coming from C/C++ and expecting this to work:

select if(name, 1, 0) ..

Even if 'name' is not NULL, unlike in C, a false-condition still triggers and the above statement returns 0. Thus, you have to remember to explicitly check for NULL or empty string:

 select if(name is null or name = '', 0, 1)

PS Eugen's example up above is correct, but I wanted to clarify this nuance as it caught me by surprise.


Instead of COALESCE(a.addressid,0) AS addressexists, use CASE:

CASE WHEN a.addressid IS NOT NULL 
       THEN 1
       ELSE 0
END AS addressexists

or the simpler:

(a.addressid IS NOT NULL) AS addressexists

This works because TRUE is displayed as 1 in MySQL and FALSE as 0.