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