MySQL Select Column if Not Null only

Question

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

Sergeant

Why would you want to do this? And anyway, that's not what's displayed when executing this query (unless using the \G delimiter !?!) – Stroman

Answer 1

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;

Moersch

Answer 2

even there is no way to hide column but yes you can avoid null value in output. So there can be below 2 ways-

Method 1: You can keep field blank where it is null.

SELECT IFNULL(firstname,'') AS firstname, 
IFNULL(lastname,'') AS lastname, 
IFNULL(age,'') AS age, 
IFNULL(gender,'') AS gender 
FROM persons WHERE id = 1;

Method2: You can get all values in single column using concatenate function.

SELECT CONCAT
(
IFNULL(firstname,''),' ',
 IFNULL(lastname,''),' ', 
IFNULL(age,''),' ', 
IFNULL(gender,'')
) 
FROM persons WHERE id = 1;

Aerify

i think method1 displays the column even if its null, right? – Sergeant

my question is "MySQL Select Column if Not Null only", if that column is null it should not show. Look at my second display, it should look like that. – Sergeant

@MarlonBuendia: I have already mentioned at top of my answer that it does not seem possible to hide any column by sql query but you can check alternate solutions if works for you....but if you just want to hide them then you have to go with any small program.... – Aerify