mysqladvanced-search

MySQL Select Column if Not Null only


I have this mysql code:

SELECT firstname, lastname, age, gender from persons WHERE id = 1;

Display:

> firstname: Marlon 
> 
> lastname: Null
> 
> age: 26
> 
> gender: male

What I want to do is:

SELECT IF NULL DONT SELECT(firstname), IF NULL DONT SELECT(lastname), IF NULL DONT SELECT(age), IF NULL DONT SELECT(gender) from persons WHERE id = 1;

Display:

> firstname: Marlon 
> 
> age: 26
> 
> gender: male

lastname didn't display because its null


Solution

  • Certainly you can't do that; rather you can use COALESCE() or IFNULL() function to provide a default value in case of NULL like

    SELECT firstname, 
    COALESCE(lastname,'N/A'),
    age,
    gender from persons WHERE id = 1;
    

    (OR) if you want to remove that record completely then use a WHERE condition like

    SELECT firstname, lastname, age, gender from persons 
    WHERE id = 1 AND lastname IS NOT NULL;