Search code examples
mysqlcorrelated-subquery

How to replace outer query value if subquery returns null

This is my query:

SELECT * FROM main_menu AS m WHERE EXISTS(select * from menu where menu.main_menu_id = m.main_menu_id AND menu.menu IS NOT NULL)

Above query shows rows from main_menu table only if their respective rows in menu table is not NULL, which is correct.

But I would like to do something more. If the value for menu table(subquery) found NULL , the main_menu value(outer query) should be altered,something like using IFNULL but for the outer query. How do I do that, please?

Solution

  • Then you should use a LEFT JOIN and not EXISTS() statement :

    SELECT m.*,COALESCE(m2.<Column>,OtherValue),....
FROM main_menu m
LEFT JOIN menu m2
 ON(m2.main_menu_id = m.maid_menu_id AND m2.menu is not null)

    This will join both tables even when there is no such record, and will have NULL values for all menu columns.

    You didn't define 'should be altered' , so that's the best I can come up with.