MIME-Version: 1.0
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>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Sat, 18 May 2024 08:12:35 -0700
Message-ID: <CAKFQuwZs4ma+K4XS7x6waVbHa6J4=FzpPhK+_TLXMnyFJzdkZQ@mail.gmail.com>
Subject: Re: Left join syntax error
To: Erik Wienhold <ewie@ewie.name>
Cc: Shammat <shammat@gmx.net>, 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f5e6800618bbe5e5"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwZs4ma%2BK4XS7x6waVbHa6J4%3DFzpPhK%2B_TLXMnyFJzdkZQ%40mail.gmail.com>
Precedence: bulk

--000000000000f5e6800618bbe5e5
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Saturday, May 18, 2024, Erik Wienhold <ewie@ewie.name> wrote:

> On 2024-05-18 15:19 +0200, Shammat wrote:
> > Am 18.05.24 um 14:52 schrieb Rich Shepard:
> > > It's been a _very_ long time since I wrote a SQL script and, despite
> looking
> > > at my SQL books and web pages, I don't know how to fix the error.
> > >
> > > The three line script is:
> > > -----
> > > SELECT p.lname, p.fname, p.job_title, p.company_nbr, p.email,
> c.company_name
> > >    FROM people as p, companies as c
> > > LEFT JOIN companies ON c.company_nbr =3D p.company_nbr;
> > > -----
> > >
> > > and psql responds:
> > > ERROR:  invalid reference to FROM-clause entry for table "p"
> > > LINE 3: LEFT JOIN companies ON c.company_nbr =3D p.company_nbr;
> > >                                                 ^
> > > HINT:  There is an entry for table "p", but it cannot be referenced
> from this part of the query.
> >
> > Don't put the second table in the FROM part
> >
> > SELECT p.lname, p.fname, p.job_title, p.company_nbr, p.email,
> c.company_name
> > FROM people as p
> >   LEFT JOIN companies as c ON c.company_nbr =3D p.company_nbr
>
> Yes, Rich probably just wants the left join.
>
> 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.company_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.  The docs on FROM [1] sort of imply that:
>

Too lazy to find the docs right now but what you are observing is basically
an operator precedence effect.  The comma join hasn=E2=80=99t happened at t=
he time
the left join is evaluated and so other tables in the comma join cannot
appear in the on clause of the left join.  Placing everything inside a
single from slot and moving the conditions to the where clause removes
changes the precedence aspect so that the cross join does indeed evaluate
prior to the left join.

I=E2=80=99m content with not pointing out this possible gotcha in the docum=
entation.

David J.

--000000000000f5e6800618bbe5e5
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Saturday, May 18, 2024, Erik Wienhold &lt;<a href=3D"mailto:ewie@ewie.na=
me">ewie@ewie.name</a>&gt; wrote:<br><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On 2024=
-05-18 15:19 +0200, Shammat wrote:<br>
&gt; Am 18.05.24 um 14:52 schrieb Rich Shepard:<br>
&gt; &gt; It&#39;s been a _very_ long time since I wrote a SQL script and, =
despite looking<br>
&gt; &gt; at my SQL books and web pages, I don&#39;t know how to fix the er=
ror.<br>
&gt; &gt; <br>
&gt; &gt; The three line script is:<br>
&gt; &gt; -----<br>
&gt; &gt; SELECT p.lname, p.fname, p.job_title, p.company_nbr, p.email, c.c=
ompany_name<br>
&gt; &gt;=C2=A0 =C2=A0 FROM people as p, companies as c<br>
&gt; &gt; LEFT JOIN companies ON c.company_nbr =3D p.company_nbr;<br>
&gt; &gt; -----<br>
&gt; &gt; <br>
&gt; &gt; and psql responds:<br>
&gt; &gt; ERROR:=C2=A0 invalid reference to FROM-clause entry for table &qu=
ot;p&quot;<br>
&gt; &gt; LINE 3: LEFT JOIN companies ON c.company_nbr =3D p.company_nbr;<b=
r>
&gt; &gt;=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0<wbr>=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
 ^<br>
&gt; &gt; HINT:=C2=A0 There is an entry for table &quot;p&quot;, but it can=
not be referenced from this part of the query.<br>
&gt; <br>
&gt; Don&#39;t put the second table in the FROM part<br>
&gt; <br>
&gt; SELECT p.lname, p.fname, p.job_title, p.company_nbr, p.email, c.compan=
y_name<br>
&gt; FROM people as p<br>
&gt;=C2=A0 =C2=A0LEFT JOIN companies as c ON c.company_nbr =3D p.company_nb=
r<br>
<br>
Yes, Rich probably just wants the left join.<br>
<br>
But I wonder if the implicit cross join syntax (&quot;FROM peoples, compani=
es&quot;)<br>
should actually produce this error because the explicit cross join<br>
works:<br>
<br>
=C2=A0 =C2=A0 SELECT p.lname, p.fname, p.job_title, p.company_nbr, p.email,=
 c.company_name<br>
=C2=A0 =C2=A0 FROM people as p<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 CROSS JOIN companies as c<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 LEFT JOIN companies ON c.company_nbr =3D p.comp=
any_nbr;<br>
<br>
But I&#39;m not even sure if implicit and explicit cross join are<br>
semantically equivalent.=C2=A0 The docs on FROM [1] sort of imply that:<br>
</blockquote><div><br></div><div>Too lazy to find the docs right now but wh=
at you are observing is basically an operator precedence effect.=C2=A0 The =
comma join hasn=E2=80=99t happened at the time the left join is evaluated a=
nd so other tables in the comma join cannot appear in the on clause of the =
left join.=C2=A0 Placing everything inside a single from slot and moving th=
e conditions to the where clause removes changes the precedence aspect so t=
hat the cross join does indeed evaluate prior to the left join.</div><div><=
br></div><div>I=E2=80=99m content with not pointing out this possible gotch=
a in the documentation.</div><div><br></div><div>David J.</div><div><br></d=
iv><div><br></div>

--000000000000f5e6800618bbe5e5--