Re: Left join syntax error - Mailing list pgsql-general

From Rich Shepard
Subject Re: Left join syntax error
Date
Msg-id 775a2a40-9353-9275-8d7e-1e4c1fccad21@appl-ecosys.com
Whole thread Raw
In response to Re: Left join syntax error  (Erik Wienhold <ewie@ewie.name>)
List pgsql-general
On Sat, 18 May 2024, Erik Wienhold wrote:

> Yes, Rich probably just wants the left join.

Eric,

You're correct: I want certain colums from the people table with their
company name from the companies table.

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

Aha! I ignored the cross join because I don't need all columns from both
tables. And it worked here (slowly) with a Ryzen7 2700 CPU and 64G RAM.

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

This didn't work as well; too many repeats for each row in people.

Thank you for a valuable lesson, Eric.

Best regards,

Rich



pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Left join syntax error
Next
From: Tom Lane
Date:
Subject: Re: utf8 vs UTF-8