mysql where isnull函数,WHERE子句MYSQL中的ISNULL（value，0）

I have this requets:

SELECT sc.no, scl.quantite, scl.description, scl.poids, scl.prix, sl_ref.refsl, sl_ref.codetva, sl_ref.tauxtva, sl_ref.compte

FROM shop_commande

AS sc, shop_commande_ligne AS scl, selectline_ref AS sl_ref

WHERE sc.id = scl.shop_commande_id

AND sl_ref.refshop = ISNULL(scl.shop_article_id, 0)

AND sc.id NOT IN (SELECT id_command FROM selectline_flag)

Sometimes, in sl_shop_article_id, there is a NULL value. What I want is to replace it by 0 so the clause:

sl_ref.refshop = scl.shop_article_id

can work even if scl.shop_article_id is NULL. To do that I tried to use the ISNULL function but it makes the request wrong and I get there error:

1582 - Incorrect parameter count in the call to native function 'ISNULL'

How can I use it ?

解决方案

I believe you are trying to use the IFNULL() function. IF you replaced ISNULL with IFNULL that should fix your query.

I suggest you go one step further and use COALESCE() instead of IFNULL(), since COALESCE() is part of the SQL standard (and IFNULL() is not).

SELECT sc.no, scl.quantite, scl.description, scl.poids,

scl.prix, sl_ref.refsl, sl_ref.codetva, sl_ref.tauxtva, sl_ref.compte

FROM shop_commande

AS sc, shop_commande_ligne AS scl, selectline_ref AS sl_ref

WHERE sc.id = scl.shop_commande_id

AND sl_ref.refshop = COALESCE(scl.shop_article_id, 0)

AND sc.id NOT IN (SELECT id_command FROM selectline_flag)