Re: Outer Right Join?

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: "Bee(dot)Lists" <bee(dot)lists(at)gmail(dot)com>
Cc: Submit Postgresql Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Outer Right Join?
Date: 2020-02-20 16:28:00
Message-ID: CAKFQuwbTVEF0rToY57BFVLkd3dY=mjPGRs=8cbkq=NPy9ArNhA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On Thu, Feb 20, 2020 at 1:05 AM Bee.Lists <bee(dot)lists(at)gmail(dot)com> wrote:

> "Show me the queried product (i.e.: Screwdriver) listings with their
> company names that DO NOT have contracts"
>
> SELECT DISTINCT ON ("listings"."product")
>

You should almost never need to use DISTINCT, and even the "ON" variant is
very specialized. Mark's comments about your data model come into play
here - I do not know or care to dig into whether this specific
situation warrants the DISTINCT, but it problem does not if you don't go
and add unnecessary tables to the FROM clause.

"listings"."product", "companies"."name"
> FROM "listings"
> RIGHT OUTER JOIN "contracts" ON ("contracts"."companyid" =
> "listings"."companyid")
>

Your comments suggest you do not understand what the word "RIGHT" is
communicating here (the word OUTER is just noise, you can omit it like you
did for LEFT JOIN below)

listing RIGHT JOIN contracts -- list every contract in the system, and if
there is related listing information show that as well. You said there are
no contracts so it should not be surprising that this join returns zero
records.

> LEFT JOIN "companies" ON ("companies"."scid" = "listings"."companyid")
>

Actually, I can never remember whether sequential joins bind left-to-right
or right-to-left but "contracts LEFT JOIN companies" is likewise an empty
set so you get the same result.

> The result works without the RIGHT OUTER JOIN in there. When the RIGHT
> OUTER JOIN is in there, I get a hitlist of zero. Currently I have no
> contracts in that table, so those two queries should be the same. They are
> not.
>
> To repeat, I want any company’s products that are in the contracts table,
> to not show up. “products with no contract”.
>
>
You want records where corresponding records in the contracts table do "not
exist". There is an SQL expression, written "NOT EXISTS (subquery)" that
does exactly this.

SELECT *
FROM company
WHERE NOT EXISTS (SELECT 1 FROM contracts WHERE company.company_id =
contracts.company_id);

Here you get company records, and only company table columns, for companies
where their id is not present in the contracts table. The use of "1" in
the select list is a convention I learned in college, it basically means "I
don't care what columns this returns I only care whether a row exists or
not - i.e., the where clause is what matters).

David J.

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message mimble9 2020-02-21 11:18:47 Confused about how to enable backups (e.g. Write Ahead Log).
Previous Message Mark Wallace 2020-02-20 16:06:22 Re: Outer Right Join?