Home › Blogs › phil's blog

MySQLs mangled syntax handling and INNER JOINs

Tue, 2010-02-02 15:18 — phil

As my DBA skills have progressed over the years, I'm always impressed and upset to see the extent to which MySQL has allowed me to use terrible syntax generally not accepted by standards-compliant SQL servers.

A recent one really blew me away, because it exposed a lack of understanding on my part about how to understand the syntax of JOIN commands.

This is how I always built nested JOIN queries. The premise is that semantically you are always selecting from a single table; the use of the INNER JOIN statement is treated like a function that returns a compound table.

SELECT r.name AS role, p.name AS permission
    FROM role r
        INNER JOIN (role_permission rp
            INNER JOIN permission p ON (rp.permission_id = p.id))
        ON (rp.role_id = r.id)
    ORDER BY r.name, p.name

However, when trying to run this same snippet against an SQLite3 database, I would continue to get a no such column: p.name error. After some research, I found the preferable way to do nested joins is much simpler, if less semantically pleasing:

SELECT r.name AS role, p.name AS permission
    FROM role r
        INNER JOIN role_permission rp ON rp.role_id = r.id
        INNER JOIN permission p ON rp.permission_id = p.id
    ORDER BY r.name, p.name

This works for me properly in both SQLite and MySQL. I'm curious what it was that made me ever start writing nested JOINs in the former method; it was fairly error-prone due to missing parenthesis, etc.