Left join returning no rows when no row from one of the joined tables is available

Question

SELECT * 
FROM `user` 
LEFT JOIN `user_group` 
  ON `user_group`.`userid` = `user`.`userid` 
LEFT  JOIN `email_template` 
  ON `email_template`.`user_id`=`user`.`userid` 
WHERE `user`.`agent_id`='123' 
 AND `email_template`.`type`='advertise'

That is my sql statement. I am joining tables user, user group and email_template. The problem is for the user I am querying there is no email_template of type advertise in the table so the entire query returns no rows. Instead I want 1 row, with the email_template type column null.

Answer 1

Did you try moving the email_template filter to the join condition?

Doing this applies the filter on the join instead of the WHERE clause. If you apply the filter on the WHERE then you are basically performing an INNER JOIN which will cause no rows to be returned if nothing meets the criteria:

SELECT * 
FROM `user` 
LEFT JOIN `user_group` 
  ON `user_group`.`userid` = `user`.`userid` 
LEFT  JOIN `email_template` 
  ON `email_template`.`user_id`=`user`.`userid` 
  AND `email_template`.`type`='advertise'
WHERE `user`.`agent_id`='123'