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

I'm working with a little display complication here. I'm sure there's an IF/ELSE capability I'm just overlooking.

I have 2 tables I'm querying (customers, addresses). The first has the main record, but the second may or may not have a record to LEFT JOIN to.

I want to display a zero if there is no record in the addresses table. And I want to only display 1, if a record exists.

What I've attempted so far:

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

This first example does not do it. But I may be utilizing COALESCE wrong.

How can I display a 0, if null, and a 1, if something exists?

7 Answers

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.

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.

SELECT c.name, CASE WHEN a.addressid IS NULL THEN 0 ELSE 1 END AS addressexists FROM customers c LEFT JOIN addresses a ON c.customerid = a.customerid WHERE customerid = 123 

Another method without WHERE, try this..

Will select both Empty and NULL values

SELECT ISNULL(NULLIF(fieldname,'')) FROM tablename 

It will set null if it is an empty string, then be true on that also.

You can actually use an IF statement in the latest versions of MySQL.

IF(expr,if_true_expr,if_false_expr) 

IE:

SELECT name, IF(ISNULL(name), 'robot', 'human') AS type FROM visitors 

If within TSQL, you can try :

SELECT IIF(a.addressid IS NULL, 0, 1) AS addressexists 

SQL Server should work