Date: Sat, 18 May 2024 08:18:34 -0700 (PDT)
From: Rich Shepard <rshepard@appl-ecosys.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Left join syntax error
In-Reply-To: <7d422a60-c581-485d-b5fd-4b2bb284b919@ewie.name>
Message-ID: <775a2a40-9353-9275-8d7e-1e4c1fccad21@appl-ecosys.com>
References: <2c877258-61cc-dd2d-fac7-4f2a5c6293e7@appl-ecosys.com> <9d899286-3a73-4894-a6e0-eab529c92e65@gmx.net> <7d422a60-c581-485d-b5fd-4b2bb284b919@ewie.name>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
Archived-At: <https://www.postgresql.org/message-id/775a2a40-9353-9275-8d7e-1e4c1fccad21%40appl-ecosys.com>
Precedence: bulk

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