public inbox for [email protected]
help / color / mirror / Atom feedFrom: David G. Johnston <[email protected]>
To: Erik Wienhold <[email protected]>
Cc: Shammat <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Left join syntax error
Date: Sat, 18 May 2024 08:12:35 -0700
Message-ID: <CAKFQuwZs4ma+K4XS7x6waVbHa6J4=FzpPhK+_TLXMnyFJzdkZQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
On Saturday, May 18, 2024, Erik Wienhold <[email protected]> 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 = 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 = 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 = 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 = 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’t happened at the 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’m content with not pointing out this possible gotcha in the documentation.
David J.
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Left join syntax error
In-Reply-To: <CAKFQuwZs4ma+K4XS7x6waVbHa6J4=FzpPhK+_TLXMnyFJzdkZQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox