CODEWITHSUNDEEP
mysqlif-statement
octopusgrabbus
octopusgrabbus

Reputation: 10695

Why won't if clause return nationality

In the following SQL select statement, I want nationality to print if name is null, and I have a SQL clause's if syntax working, but am not seeing nationality print out in the following:

table test2

id           integer
case         integer
nationality  char(10)
name         char(24)

1 1 france  ""
2 2 england john

select t.id, 
       if(t.name is null, t.nationality, t.name) 
       as name_equivalent 
from   test2 t;

produces id name_equivalent

1 ""
2 john

Why is that? Thank You.

Upvotes: 1

Views: 96

Answers (3)

Dave F
Dave F

Reputation: 370

You're checking if the name is null, but it was set to an empty string. To check for both, you need:

if(t.name is null or t.name='', t.nationality, t.name)

Upvotes: 2

larissa
larissa

Reputation: 493

null is not equivalent to the empty string. The empty string '' is a valid form of data, null indicates there is no data present.

Try either 

if (t.name is null or t.name='', t.nationality, t.name)

Or set the name in the first column to null

1 1 france null

Upvotes: 2

davethegr8
davethegr8

Reputation: 11595

It looks like France's name field is actually an empty string, and not null. You need to also account for it being empty in your select statement. Such as t.name = ''

Upvotes: 1

Related Questions