Re: Left join syntax error

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:

"If multiple sources are specified, the result is the Cartesian product
 (cross join) of all the sources."

Maybe it's only meant that both syntaxes are equivalent regarding the
result, and that it does not extend to aliases of those FROM items.

If you just move the LEFT JOIN condition to the WHERE clause it works as
well, which indicates that the aliases from the implicit cross join do
work as if it has been an explicit cross join:

    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 true
    WHERE c.company_nbr = p.company_nbr;

[1] https://www.postgresql.org/docs/current/sql-select.html#SQL-FROM

-- 
Erik