Cascaded LEFT JOIN v.s. sub-select

Question

I have 2 queries:

1)

    SELECT person.idx, person.name, groups.idx
      FROM people
 LEFT JOIN membership
        ON membership.person=person.idx
 LEFT JOIN groups
        ON groups.name='supervisors'
       AND membership.group=groups.idx

2)

   SELECT person.idx, person.name, a.idx
     FROM people
LEFT JOIN
          (SELECT group.idx, membership.person
             FROM groups, membership
            WHERE membership.group=group.idx
              AND group.name='supervisors') a
       ON a.person=person.idx

These queries have been simplified but the core logic is the same. They seem to be equivalent. The 1st seems "cleaner" syntactically. I'm not an SQL expert, and am pretty new to LEFT JOIN in particular, but it seems to be the way to answer this kind of membership question, where one table contains a subset of information about another table. Is this the right approach?

You mentioned LEFT JOIN, but I cannot see any LEFT JOINs in your queries. — fifonik, Jun 25 '19 at 02:56
There are three kinds of outer joins--left, right & full. PS Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. — philipxy, Jun 25 '19 at 08:19
T t INNER JOIN U u ON c is (t.t1,...,u.u1,...) rows where (t.t1,...) IN T AND (u.u1,...) IN U AND c. T t LEFT JOIN U u ON c is (t.t1,...,u.u1,...) rows where (t.t1,...) IN T AND ((u.u1,...) IN U AND c OR NOT EXISTS u.u1,... [(u.u1,...) IN U AND c] AND u.u1 IS NULL AND ...). People build queries using these intuitively but don't know how to justify their choices. [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) — philipxy, Jun 25 '19 at 09:09

score 0 · Answer 1 · answered Jun 25 '19 at 03:03

The two queries are not the same. The first returns rows for all groups a person is a member of, with the idx of the "supervisors" where appropriate.

The second returns one row for each member, with the idx of the "supervisors" group where appropriate. You should choose the version that does what you want.

Once you have the logic that you want, then in MySQL, it is usually best to avoid subqueries in the FROM clause if possible. MySQL has a tendency to materialize them, which makes optimizations more difficult (I think this has gotten better in more recent versions).

Also, you should eschew commas in the FROM clause and always use proper, explicit, standard JOIN syntax.

It's the 2nd behavior I want. Can I do it without the sub-select? — Bob Mercier, Jun 25 '19 at 04:35

score 0 · Answer 2 · answered Jun 25 '19 at 05:10

0

Poor form to answer my own question?

This seems to work:

     SELECT person.idx, person.name, groups.idx
      FROM people
LEFT JOIN (groups, membership)
       ON groups.name='supervisors'
      AND groups.person=person.idx
      AND membership.group=groups.idx

Maybe a MySQL only extension?

answered Jun 25 '19 at 05:10

Bob Mercier

1
2

If you read the MySQL JOIN documentation, they have an extension where you can put comma lists of tables in parentheses rather than only not in parentheses. Comma means CROSS JOIN with lower precedence than keyword joins. INNER JOIN ON c returns CROSS JOIN WHERE c; CROSS JOIN means INNER JOIN ON true. – philipxy Jun 25 '19 at 08:29
Using standard SQL this is p left join (g cross join m) on c, also (ignoring column order) g cross join m right join p on c. Read an introduction and/or the manual & find out what joins calculate. – philipxy Jun 25 '19 at 09:07

Cascaded LEFT JOIN v.s. sub-select

2 Answers2