MYSQL Return a value if exists, if not return another

Question

I have a simple table (items) which has two fields in particular: group_name and single_name

Only a handful have a value for group_name but I need to return one or the other as display_name.

If group_name exists then that is returned (i.e group_name as display_name), if group_name is NULL then return single_name as display name.

I'm sure I could do lots of nested select statements, but I'm sure MYSQL has a much easier way of doing this using an if statement. i.e in PHP code I would do something like this:

if (group_name IS null) {
    display_name = single_name
} else {
    display_name = group_name
}

I will be rendering the data in Angular so would prefer the SQL / API server did do the work rather than the client at render time.

Possible duplicate of [MySQL equivalent of DECODE function in Oracle](https://stackoverflow.com/questions/4706100/mysql-equivalent-of-decode-function-in-oracle) — Dinidu Hewage, Oct 29 '17 at 16:12
I did end up using CASE, the link you sent is sort of appropriate but the title is too complex and 'indirect' for me to have found it to help me as it refers to an ORACLE statement that I have no knowledge of) but a nice Boolean MYSQL only example has its place no? — MOLEDesign, Oct 29 '17 at 16:19
No, if you did a good search on it, you would have found these also. https://stackoverflow.com/questions/13226520/selecting-mysql-column- conditionally --- https://stackoverflow.com/questions/5951157/if-in-select-statement-choose-output-value-based-on-column-values — Dinidu Hewage, Oct 29 '17 at 16:28

Sebastian Brosch · Accepted Answer · 2017-10-29T16:21:55.597

3

You can use IFNULL to solve this:

SELECT IFNULL(group_name, single_name) AS display_name FROM items

... for the completeness you can also use CASE:

SELECT CASE WHEN group_name IS NULL THEN single_name ELSE group_name END AS display_name FROM items

... or you can use IF:

SELECT IF(group_name IS NULL, single_name, group_name) AS display_name FROM items

demo: http://sqlfiddle.com/#!9/853182/3

edited Oct 29 '17 at 16:21

answered Oct 29 '17 at 16:06

Sebastian Brosch

42,106
15
72
87

Each one brilliant, I settled on the CASE statement. Many thanks for the swift response – MOLEDesign Oct 29 '17 at 16:18

score 3 · Answer 2 · answered Oct 29 '17 at 16:09

3

You can also use IF

SELECT 
 IF(group_name IS NULL, single_name, group_name) AS display_name 
FROM 
 [table]

Or CASE

SELECT 
 CASE 
  WHEN group_name IS NULL 
  THEN single_name 
  ELSE group_name
 END 
  AS display_name
FROM 
 [table]

answered Oct 29 '17 at 16:09

Raymond Nijland

11,488
2
22
34

MYSQL Return a value if exists, if not return another

2 Answers2