CODEWITHSUNDEEP
mysqljoinleft-joinwhere-clauseambiguity
Chris Athanasiadis
Chris Athanasiadis

Reputation: 390

MySQL JOIN independent of WHERE clause

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

Upvotes: 0

Views: 237

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35583

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

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Move the predicates of the WHERE clause to ON:

SELECT debates.id,
       COUNT(opinions.id) AS total_opinions    
FROM debates    
LEFT JOIN `opinions` 
ON `opinions`.`debate` = `debates`.`id`  AND
   `opinions`.`opinion` IS NOT NULL AND 
   `opinions`.`opinion` != ''     
GROUP BY `debates`.`id`

This query will fetch all records of debates, joined by the records of opinions that satisfy the conditions as specified by the ON predicates. If there is no match NULL is returned, which will be ignored be the COUNT aggregate function.

Demo here

Upvotes: 3

Related Questions