Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select column value if not null else use another column value

Tags:

mysql

I have 2 columns in mysql table: a and b. a is allways string value and b is sometimes a string value and sometimes it is null.

How to construct a mysql SELECT so that the b would be taken if it is not null and a would be taken otherwise.

I tried to make some magic with concat and if...then with no success...

UPDATE - To extend my question, is there a function that would work like Ifnull but would work for null AND empty values?

like image 654
Jerry2 Avatar asked Oct 17 '10 07:10

Jerry2


People also ask

How do you replace NULL values in a column in SQL?

Null Values can be replaced in SQL by using UPDATE, SET, and WHERE to search a column in a table for nulls and replace them. In the example above it replaces them with 0. Cleaning data is important for analytics because messy data can lead to incorrect analysis.


1 Answers

Use IFNULL(b, a).

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.

This is a MySQL specific function. You can also use COALESCE in the same way. This will work in more databases but it has slightly worse performance in MySQL than IFNULL.

like image 112
Mark Byers Avatar answered Oct 14 '22 20:10

Mark Byers