Trying to parse this sql

I'm trying to understand what this SQL (from a MySQL installation) actually does:

IF(coalesce(a.entity_id, 0) != 0, 0, 1)

While I understand what the coalesce does I don't understand h开发者_运维问答ow the IF statement is modifying it.

I think:

coalesce(a.entity_id, 0) - return the first not null value,

if a.entity_id is not null you get 0 as a result of if, else 1.

a.entity_id = null => coalesce = 0 => if = 1

a.entity_id is not null => coalesce = a.entity_id => if = 0

coalesce returns the first NON-null argument. So if a.entity_id is null, coalesce will return 0. the containing if() then checks if the argument is not zero, and returns 0 or 1.

basically it's a convoluted way of writing a.entity_id IS NULL.

The condition will be met if a.entity_id is a value that's not NULL AND not 0.

You're looking at the MySQL IF() function. Basically it's a ternary operator on the boolean expression supplied in argument 1. (It sounds from your question that you understood with that COALESCE is doing.)

• If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2;
• If expr1 is FALSE, it returns expr3.

IF() returns a numeric or string value, depending on the context in which it is used.

mysql> SELECT IF(1>2,2,3);
        -> 3
mysql> SELECT IF(1<2,'yes','no');
        -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
        -> 'no'

It's a really funky non-readable way of not using a.entity_id IS NULL.

The MySQL manual entry on IF() - http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if

Looks to me like this clause will evaluate to 0 if a.entity_id is not null, and 1 otherwise.