Need explanation SELECT query on many to many relationship

Question

I have two tables related through another (many-to-many)

This is a schema excerpt:

CREATE TABLE user (
    user_id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    email    TEXT    NOT NULL UNIQUE
);

CREATE TABLE alias (
    alias_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    address  TEXT    NOT NULL UNIQUE
);

CREATE TABLE alias_member (
    alias_id INTEGER NOT NULL,
    user_id  INTEGER NOT NULL,
    PRIMARY KEY(alias_id, user_id),
    FOREIGN KEY(alias_id) REFERENCES alias(alias_id) ON DELETE CASCADE,
    FOREIGN KEY(user_id)  REFERENCES user(user_id)   ON DELETE CASCADE
);

I have this query that satisfies my needs:

SELECT email
FROM
    user,
    alias,
    alias_member
WHERE 
    user.user_id = alias_member.user_id
    AND alias.alias_id = alias_member.alias_id
    AND alias.address = 'foo@domain.tld';

A friend suggested me another query that works too but I don't get it:

SELECT
    email
FROM
    user
INNER JOIN
    (alias INNER JOIN alias_member ON alias.alias_id = alias_member.alias_id) ON user.user_id = alias_member.user_id
WHERE address='foo@domain.tld';

Would anyone explain it for me?

Answer 1

Your version uses a very old JOIN syntax from the ANSI-89 standards - as in 1989. In 1992, the standard was updated to adopt the JOIN...ON syntax. You can read about the differences and their adoption in this SO question.

The problem with listing tables in a FROM and then listing the conditions in the WHERE is that it's very easy to forget a condition somewhere. How easy would it be to accidentally write:

SELECT email
FROM
    user,
    alias,
    alias_member
WHERE 
    user.user_id = alias_member.user_id
    AND alias.address = 'foo@domain.tld';

Now, your friend's version also uses a weird convention (the "nested" JOIN). Here, it's trying to help with the fact that you have alias_member as the last table in the JOIN, but is needed to connect the other 2 tables. If alias_member is the first table, the syntax gets a bit cleaner:

SELECT
    email
FROM
    alias_member 
INNER JOIN
    user
  ON alias_member.user_id = user.user_id
INNER JOIN
    alias 
  ON alias_member.alias_id = alias.alias_id
WHERE address='foo@domain.tld';

Answer 2

It's not actually another query - it's a rewriting of the existing query, to make it more explicit what's happening.

Your original FROM clause, with a list of three tables separated by commas, is actually an implicit INNER JOIN of the three tables, with the criteria for the JOIN specified in the WHERE clause. It's generally considered better coding to make your JOINs explicit, and to include the criteria for the JOINs in the ON clause of the join. This makes it easier to understand how the tables are meant to fit together.

The one criteria that's not specific to a JOIN is left in the WHERE clause. This isn't limiting data based on how the various tables are related, but based on matching a field in one table regardless of the relationship.