mysql syntax for IFNULL with subquery

Question

I want to use subquery inside of IFNULL statement

SELECT t.col1
     , IFNULL(t.col2, (SELECT an.col_11
                       FROM another_table an
                       WHERE an.col1 = t.col5)) as alias_name
     , t.col3
FROM table t;

In IFNULL statement second expression should be subquery.

Please give me proper syntax My actual query is

SELECT u.username, up.gender, d.name, desg.name, 
IFNULL(up.creative_lead_id, 
    (SELECT au.username FROM auth_user au 
     WHERE au.id=up.creative_lead_id)) as creative_lead, up.image 
FROM user_profile up, department d, designation, auth_user 
WHERE up.department_id=d.id 
AND up.designation_id = desg.id up.auth_uesr_id = u.id;

This query is giving syntax error because of IFNULL statement.

score 1 · Answer 1 · answered Sep 18 '14 at 07:22

You can rewrite your query with join,Correlated query will execute for each row in your table and it might affect the performance

SELECT 
  t.col1,
  IFNULL(t.col2, an.col_11) AS alias_name,
  t.col3 
FROM
  `table` t 
  LEFT JOIN another_table an 
    ON an.col1 = t.col5

Joël Salamin · Answer 2 · 2014-09-18T08:55:02.077

Don't use a subquery for this situation, try a query like that instead (use of jointure):

SELECT t.col1
    ,IFNULL(t.col2, an.col_11) AS alias_name
    ,t.col3
FROM your_table t
LEFT JOIN another_table an ON an.col1 = t.col5

In your full query, your using twice up.creative_lead_id for your IFNULL clause (once as first parameter and then in the subquery). That make no sense because if the first param is NULL, your subquery will return no result!

In order to show you the principe that will solve your problem, i just replaced the first param by a fictive one that i called up.creative_lead. This fictive column is the name of the creative lead stored in your table user_profile and if this value is null, i'm looking to the username of the user corresponding to creative_lead_id.

Here is the full query that'll solve your problem with the correction mentioned above:

SELECT u.username
    ,up.gender
    ,d.name
    ,desg.name
    ,IFNULL(up.creative_lead, cl.username) AS creative_lead
    ,up.image
FROM user_profile up
INNER JOIN department d ON d.id = up.department_id
INNER JOIN designation desg ON desg.id = up.designation_id
INNER JOIN auth_user u ON u.id = up.auth_user_id
INNER JOIN auth_user cl ON cl.id = up.creative_lead_id

Notice that i changed the syntax of your query, it's highly recommended to avoid the use of old syntax for jointures (use explicit JOIN clause instead).

Hope this will help you.

thanks for solution but my query contains multiple tables in FROM and WHERE clause also their then How should I write this query — Mugdha, Sep 18 '14 at 08:20
Provide us the entire query and we'll help you to find the best solution. No matter the number of tables and conditions, to get the result you want the best solution is to use a jointure (using a subquery will affect performance because the subquery will be executed for each row!) — Joël Salamin, Sep 18 '14 at 08:22
Query is as follows, SELECT u.username, up.gender, d.name, desg.name, IFNULL(up.creative_lead_id, (SELECT au.username FROM auth_user au WHERE au.id=up.creative_lead_id)) as creative_lead, up.image FROM user_profile up, department d, designation, auth_user WHERE up.department_id=d.id AND up.designation_id = desg.id up.auth_uesr_id = u.id; Please give me appropriate solution. Thanks in advance. — Mugdha, Sep 18 '14 at 08:35
@Mugdha Please add this information in your question, it'll be easier to help you — Joël Salamin, Sep 18 '14 at 08:39

mysql syntax for IFNULL with subquery

2 Answers2