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

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

---