ISNULL(value, 0) in WHERE clause MYSQL

问题:

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 ?

回答1:

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)

回答2:

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 = IFNULL(scl.shop_article_id, 0) 
AND sc.id NOT IN (SELECT id_command FROM selectline_flag)