mysqlmysql-select-db

how to selected data based on condition null or not null mysql 5.7

i have table called my_table like this 

+------------+-------+-------+
|  product   | type1 | type2 |
+------------+-------+-------+
| Banana     | NULL  | A1    |
| Grape      | NULL  | B1    |
| Watermelon | A     | A1    |
| Orange     | B     | A1    |
+------------+-------+-------+

i want to select product based on his type, if type1 NULL then the type using type 2, if not then use type 1

so the expected results like this

+------------+------+
|  product   | type |
+------------+------+
| Banana     | A1   |
| Grape      | B1   |
| Watermelon | A    |
| Orange     | B    |
+------------+------+
Solution

  • Use COALESCE:

    SELECT
    product,
    COALESCE(type1, type2) AS type
FROM yourTable;

    The COALESCE(a, b) function will return a, should it be not NULL, otherwise it would fallback to returning b.