MySQL - LEFT JOIN where nothing is found

Question

I got the following query :

SELECT      budgets_income.income_amount, budgets_income.rule_type, budgets_income_rules.day_of_week, budgets_income_rules.interval_week, budgets_income_rules.fixed_day, budgets_income_rules.last_day_month, budgets_income_rules.date_start, budgets_income_rules.date_end
FROM        budgets_income
LEFT JOIN   budgets_income_rules USING (income_id)
WHERE       budgets_income.account_id = :account_id
    AND
    (
        budgets_income_rules.date_start <= :date_end
    )

If budgets_income.rule_type is equal to 1 or 2, then in the table budgets_income_rules there's a rule in there that I get with the LEFT JOIN - this is fine.

Now, sometime if the rule is equal to 0, it means that the rule doesn't exist. Since the rule doesn't exist, the field budgets_income_rules.date_start doesn't either - meaning that the row won't be fetch with the others (because of the WHERE budgets_income_rules.date_start <= :date_end).

I tried these things :

• First of all, I did this :

  AND
  (
      budgets_income_rules.date_start <= :date_end
          OR
      budgets_income_rules.date_start NOT EXISTS
  )
  

  Didn't work out so well because it doesn't work at all.

• After that, I did anoter attempt like this :

  AND
  (
      budgets_income_rules.date_start <= :date_end
          OR
      budgets_income_rules.date_start IS NULL
  )
  

That did work by logic because if MySQL does not find the row in the LEFT JOIN, then the column is set to NULL.

Now my question, is there a better way to do it ?

Thanks.

Answer 1

At the moment you will also get rows where date_start is NULL in the record. You could use the id of the budgets_income_rules table to test for NULL, because that will only be NULL in the join query where the row doesn't exist.

budgets_income_rules.id IS NULL