From: Tom Lane <tgl@sss.pgh.pa.us>
To: Erik Wienhold <ewie@ewie.name>
cc: Shammat <shammat@gmx.net>, pgsql-general@lists.postgresql.org
Subject: Re: Left join syntax error
In-reply-to: <7d422a60-c581-485d-b5fd-4b2bb284b919@ewie.name>
References: <2c877258-61cc-dd2d-fac7-4f2a5c6293e7@appl-ecosys.com> <9d899286-3a73-4894-a6e0-eab529c92e65@gmx.net> <7d422a60-c581-485d-b5fd-4b2bb284b919@ewie.name>
Comments: In-reply-to Erik Wienhold <ewie@ewie.name>
	message dated "Sat, 18 May 2024 17:00:05 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2762581.1716045418.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sat, 18 May 2024 11:16:58 -0400
Message-ID: <2762582.1716045418@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2762582.1716045418%40sss.pgh.pa.us>
Precedence: bulk

Erik Wienhold <ewie@ewie.name> writes:
> But I wonder if the implicit cross join syntax ("FROM peoples, companies=
")
> should actually produce this error because the explicit cross join
> works:

>     SELECT p.lname, p.fname, p.job_title, p.company_nbr, p.email, c.comp=
any_name
>     FROM people as p
>         CROSS JOIN companies as c
>         LEFT JOIN companies ON c.company_nbr =3D p.company_nbr;

> But I'm not even sure if implicit and explicit cross join are
> semantically equivalent.

Well, they do the same thing, but JOIN binds tighter than comma.
So in one case you have effectively

    FROM people as p CROSS JOIN
         (companies as c LEFT JOIN companies ON c.company_nbr =3D p.compan=
y_nbr)

and "p" is not within the scope of the JOIN/ON clause.
The other way is effectively

    FROM (people as p CROSS JOIN companies as c)
         LEFT JOIN companies ON c.company_nbr =3D p.company_nbr;

which is syntactically legal, although it probably doesn't do
what you wanted.

If memory serves, MySQL got this basic syntactic detail wrong
for years, as a result of which there's (still) a tremendous amount
of confusion on the net about what is the syntactic precedence in
FROM clauses.

			regards, tom lane