Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL: how to use COALESCE

Tags:

mysql

coalesce

Say I have the following table:

TABLE: product
===============================================================================
| product_id | language_id | name           | description                     |
===============================================================================
| 1          | 1           | Widget 1       | Really nice widget. Buy it now! |
-------------------------------------------------------------------------------
| 1          | 2           | Lorem  1       |                                 |
-------------------------------------------------------------------------------

How do I query this such that it tries to give me the name and description where language_id = 2, but fall back to language_id = 1 if the column contains a NULL?

In the above example, I should get Lorem 1 for name and Really nice widget. Buy it now! for description.

like image 634
StackOverflowNewbie Avatar asked Apr 09 '11 23:04

StackOverflowNewbie


People also ask

How do I use coalesce in MySQL?

The MySQL COALESCE() function is used for returning the first non-null value in a list of expressions. If all the values in the list evaluate to NULL, then the COALESCE() function returns NULL. The COALESCE() function accepts one parameter which is the list which can contain various values.

Does coalesce work in MySQL?

The COALESCE function can be used in the following versions of MySQL: MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23.

How does coalesce function work?

The COALESCE function returns the first non-NULL value from a series of expressions. The expressions are evaluated in the order in which they are specified, and the result of the function is the first value that is not null. The result of the COALESCE function returns NULL only if all the arguments are null.

How do you use coalesce on a column?

The coalesce in MySQL can be used to return first not null value. If there are multiple columns, and all columns have NULL value then it returns NULL otherwise it will return first not null value. The syntax is as follows. SELECT COALESCE(yourColumnName1,yourColumnName2,yourColumnName3,.......


1 Answers

How about this?

SET @pid := 1, @lid := 2;
SELECT 
    COALESCE(name,(
        SELECT name
        FROM product
        WHERE product_id = @pid AND description IS NOT NULL
        LIMIT 1
    )) name, 
    COALESCE(description,(
        SELECT description
        FROM product
        WHERE product_id = @pid AND description IS NOT NULL
        LIMIT 1
    )) description
FROM product
WHERE product_id = @pid 
    AND (language_id = @lid 
    OR language_id = 1)
ORDER BY language_id DESC
LIMIT 1;

where:

  • @pid: current product id
  • @lid: current language id
  • Values for name and/or description could be null
  • language_id = 2 item could not exist
like image 119
Francisco Alvarado Avatar answered Nov 02 '22 23:11

Francisco Alvarado