0

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.

3
  • Joining isn't a commutative operation so you'll get a different result on a different join order for the same tables. Show us the sample data/code to get it straight.
    – Kondybas
    Jun 15, 2022 at 15:58
  • See my answer below.
    – Richard
    Jun 16, 2022 at 17:13
  • @Kondybas - INNER JOIN is commutative and associative; LEFT JOIN is not. In fact, the Optimizer will [usually] try all permutations of Inner Joins to decide what table to start with, then [usually] use "Nested Loop Join" for the others.
    – Rick James
    Jun 21, 2022 at 5:32

2 Answers 2

0

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.

5
  • I don't see any right joins?
    – J.D.
    Jun 15, 2022 at 1:06
  • Ahh, you're right. I meant to say "inner join" instead of right join. Will edit now.
    – Richard
    Jun 15, 2022 at 4:03
  • 2
    It's a Bad Idea™ to mix explicit join syntax with the old-school Cartesian join syntax in the same statement (or use the old style syntax at all).
    – mustaccio
    Jun 15, 2022 at 11:46
  • You're probably right, but old code and old habits are both hard to let go of!
    – Richard
    Jun 16, 2022 at 17:14
  • Not just weird, but also verbose and clumsy.
    – Rick James
    Jun 21, 2022 at 5:34
0

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
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.