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

Question

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?

ypercubeᵀᴹ · Accepted Answer · 2012-02-22 01:16:30Z

265

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.

answered Feb 22, 2012 at 1:16

ypercubeᵀᴹ

116k19 gold badges181 silver badges248 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

yogithesymbian Over a year ago

how about IFNULL(column, '') , there is any short for if not null ?

ypercubeᵀᴹ Over a year ago

@YogiArifWidodo not sure what exactly you are asking. In most cases, if not all, you can do what you want with a CASE expression.

Eugen Rieck · Accepted Answer · 2012-02-22 01:17:38Z

129

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

answered Feb 22, 2012 at 1:17

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:34:27Z

24

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.

edited May 23, 2017 at 12:34

CommunityBot

11 silver badge

answered Mar 4, 2014 at 0:18

Slawomir

3,3633 gold badges34 silver badges39 bronze badges

1 Comment

Bang Andre Over a year ago

"if(name is null or name = '', 0, 1)" Yes. need add condition name = ''. It's working well

RonnyKnoxville · Accepted Answer · 2016-08-23 16:40:49Z

24

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

edited Aug 23, 2016 at 16:40

RonnyKnoxville

6,43410 gold badges50 silver badges80 bronze badges

answered Feb 22, 2012 at 1:16

bucko

1,1986 silver badges5 bronze badges

Comments

Salvi Pascual · Accepted Answer · 2019-08-13 19:46:03Z

6

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

answered Aug 13, 2019 at 19:46

Salvi Pascual

1,91519 silver badges22 bronze badges

Comments

Giacomo1968 · Accepted Answer · 2024-01-12 03:51:45Z

6

If within TSQL, you can try :

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

SQL Server should work

edited Jan 12, 2024 at 3:51

Giacomo1968

25.3k11 gold badges78 silver badges106 bronze badges

answered Feb 25, 2019 at 19:50

Kevin Chen

1111 silver badge2 bronze badges

Comments

PodTech.io · Accepted Answer · 2016-05-02 06:54:30Z

5

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.

answered May 2, 2016 at 6:54

PodTech.io

5,31246 silver badges24 bronze badges

1 Comment

PodTech.io Over a year ago

Also useful: select IFNULL(fieldname, "1") from tablename;

Collectives™ on Stack Overflow

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

7 Answers 7

2 Comments

Comments

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related