MySQL JOIN independent of WHERE clause

Question

I have two tables:

debates
----------------------
id | name
----------------------
 1 | why is that?
 2 | why is the other?

opinions
---------------------
id | debate | opinion
---------------------
 1 | 1      | because
 2 | 1      | NULL

If I use a left join ON opinions.debate = debates.id I can get both debates (1, 2).

If I use a where clause on top of the left join WHERE (opinions.opinion != '' AND opinions.opinion IS NOT NULL) I get only debate with id = 1

How is possible to get both records but still keep the condition because I use it for counting records?

ex. the query:

SELECT 

debates.id,
COUNT(opinions.id) AS total_opinions

FROM debates

LEFT JOIN 
`opinions` ON `opinions`.`debate` = `debates`.`id` 

WHERE 
`opinions`.`opinion` IS NOT NULL AND `opinions`.`opinion` != '' 

GROUP BY 
`debates`.`id`

Should return:

debates
-------------------
id | total_opinions
-------------------
 1 | 1
 2 | 0

Answer 1

The purpose of the LEFT OUTER JOIN is to allow all rows of one table to listed even if those rows are not referenced in another table, and if those conditions exist you get NULL values from that other table. So to get all debates even if there has been no opinions for some of them you can do this:

FROM debates
LEFT OUTER JOIN `opinions` ON `opinions`.`debate` = `debates`.`id` 

When there are no opinions on a debate all column positions for data in the opinions table will be NULL

If you are counting the number of opinions then it is vital to note that the COUNT() function ONLY increments by 1 if a value is NON NULL. Hence the following will automatically count the correct number of opinions without any need to filter out NULLs.

SELECT 
      debates.id
    , COUNT(opinions.id) AS total_opinions
FROM debates
LEFT OUTER JOIN `opinions` ON `opinions`.`debate` = `debates`.`id` 
GROUP BY
      debates.id

Now if you really do have rows in the opinion table where an opinion is a blank string you could so this:

SELECT 
      debates.id
    , COUNT(opinions.id) AS total_opinions
FROM debates
LEFT OUTER JOIN `opinions` ON `opinions`.`debate` = `debates`.`id` 
                          AND `opinions`.`opinion` <> '' 
GROUP BY
      debates.id

or:

SELECT 
      debates.id
    , COUNT(case when `opinions`.`opinion` <> '' then opinions.id end) AS total_opinions
FROM debates
LEFT OUTER JOIN `opinions` ON `opinions`.`debate` = `debates`.`id` 
GROUP BY
      debates.id