Need explanation SELECT query on many to many relationship

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?

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';

StackExchange Database Administrators Q#173354, answer score: 7