MySQL query with both inner joins and left joins?

All we need is an easy explanation of the problem, so here it is.

I had a problem where I need to query multiple tables, but one of them needed to be LEFT JOIN as well as my normal inner joins, so I could see nulls in the result set, if there were missing values.

Took me forever to get it right, so I am posting the answer below.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This is from a Drupal site, which is why the table names are weird.

SELECT * 
FROM (field_data_field_due_date a, node n)
LEFT JOIN field_data_field_sent_due_date_email b on a.entity_id = b.entity_id
WHERE 
  a.entity_id = n.nid
  AND a.field_due_date_value IS NOT NULL
  AND b.field_sent_due_date_email_value IS NULL
  AND n.type = 'task';
  
  

Just FYI: What I am trying to accomplish with this query is I am looking for tasks where the due date is set, but no "due date email" has been sent yet.

So to explain a little– the tables listed next to my FROM are in parenthesis, and you can see in the WHERE clause which fields I am joining them on.

The secret that took me so long is this: Notice I place my LEFT JOIN just under that. This allows me to get back "NULL" for the values in the table "field_data_field_sent_due_date_email" if it doesn’t match on entity_id. In this way I can test if a value is NOT there by testing for NULL.

I hope this helps someone.

Method 2

re Title question: Yes, you can mix them. However…

Tips:

  • Put JOINs first and LEFT JOINs last.

  • If you choose to violate the above tip, use parentheses to group JOINs, but that is confusing. [This may have been part of your problem.] Example:

    FROM (a LEFT JOIN b ON ...) JOIN c ON ...
    

versus

  FROM a LEFT JOIN (b ON ... JOIN c ON ...)
  • Turn RIGHT JOIN into LEFT JOIN (else my head will spin like an owl’s)

  • In MySQL, the keywords INNER, OUTER, CROSS have no impact. In particular, INNER JOIN is treated identically to JOIN.

  • To help with readability, use ON for specifying how tables are related; use WHERE for "filtering". (The Optimizer treats ON and WHERE as equivalent for INNER JOIN; not for LEFT JOIN.) [This may have been part of your problem.]

  • The pattern for finding just the rows of a that have no corresponding row in b:

      FROM a
      LEFT JOIN b  ON a.foo = b.foo
      WHERE ...
        AND b.id IS NULL
    

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply