MySQL query with both inner joins and left joins?

Question

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.

Answer 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.

Answer 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