PostgreSQL intersect alleen als beide sets niet leeg zijn

[Snippo]

Mijn vraag
...
Ik wil graag de INTERSECT van twee queries hebben, maar als één van de twee geen resultaten bevat wil ik de set die wel gedefinieerd is als resultaat.
Het lijkt zo makkelijk maar ik kom er niet uit .

Relevante software en hardware die ik gebruik
...
Postgres

Wat ik al gevonden of geprobeerd heb
...
Authors:
id | name | city
1 | John | New York

Books
id | title | genre | author_id
1 | Full Text Search | Education | 1

code:

    with main as (
      select 
        b.id, 
        b.author_id, 
        b.title, 
        b.genre 
      from 
        books b 
        inner join authors a on b.author_id = a.id 
      where 
        b.fulltext @@ to_tsquery(
          'english', 'John | Full | Text | Educative'
        )
    ), relation as (
      select 
        b.id, 
        b.author_id, 
        b.title, 
        b.genre 
      from 
        books b 
        inner join authors a on b.author_id = a.id 
      where 
        a.fulltext @@ to_tsquery(
          'english', 'John | Full | Text | Educative'
        )
    )
    select * from main intersect select * from relation;

Als een boek dus een match heeft met een bepaalde schrijver wil ik alleen die als resultaat. Is er geen match dan wil ik of alle boeken, of alle schrijvers.

[Diumelia]

Je gebruikt op het eerste zicht inner joins. Daarmee ga je enkel resultaten terugkrijgen als ze in beide tabellen zitten. Volgens mij ben je op zoek naar full joins: https://www.postgresqltut...stgresql-full-outer-join/

[luukvr]

Wat is je exacte probleem? Krijg je niks terug? Wat krijg je terug als je alleen 1 van de intersected queries draait? Omdat beiden intersected queries hetzelfde zijn zouden alle waardes die je terug krijgt teruggegeven moeten worden.

[Kalentum]

Je zoekt dus boeken die in de fulltext van books of authors voldoen aan die to_tsquery? Kun je dan niet gewoon OR gebruiken?

Edit: oh nevermind verkeerd gelezen, je wilt alle boeken of alle schrijvers als er geen match is. Ik snap de use case niet helemaal: wanneer wil je dan alle schrijvers en wanneer wil je dan alle boeken? Wat betekent 'als er geen match is wil ik alle'?

Ik denk dat dit makkelijker in je applicatielaag op te lossen valt dan. "als geen resultaten dan toon alle boeken"

SQL:

      select 
        b.id, 
        b.author_id, 
        b.title, 
        b.genre 
      from 
        books b 
        inner join authors a on b.author_id = a.id 
      where 
        b.fulltext @@ to_tsquery(
          'english', 'John | Full | Text | Educative'
        )
        or  a.fulltext @@ to_tsquery(
          'english', 'John | Full | Text | Educative'
        )

[ Voor 25% gewijzigd door Kalentum op 17-03-2023 13:48 ]

[Snippo]

De individuele queries werken prima zoals je zou verwachten. Het probleem met de intersect is dat als maar één van de twee queries een resultaat heeft, er geen resultaat terug komt uit de intersect.
Ik wil dus één van de twee resultaten, -of- de intersect van beide (zodat de zoekopdracht specifieker is).

Vanmorgen schoot me opeens de DISTINCT statement te binnen en daarmee lijkt het te zijn opgelost i.c.m. met een UNION:

code:

    with main as (
      select 
        b.id, 
        b.author_id, 
        b.title, 
        b.genre 
      from 
        books b 
        inner join authors a on b.author_id = a.id 
      where 
        b.fulltext @@ to_tsquery(
          'english', 'John'
        )
    ), relation as (
      select 
        b.id, 
        b.author_id, 
        b.title, 
        b.genre 
      from 
        books b 
        inner join authors a on b.author_id = a.id 
      where 
        a.fulltext @@ to_tsquery(
          'english', 'John'
        )
    )
    select distinct on (id) * from main union select * from relation;

[SPee]

Een intersect geeft alleen het resultaat terug dat in beide voorkomt.
En als een van de twee geen resultaat heeft, dan heb je dus niets dat overeenkomt ("intersect") en is je resultaat dus leeg.

Jouw nieuwe query doet (helaas) geen intersect, maar geeft alleen alle unieke resultaten terug.
Dus met resultaten main = (A,B,C) en relation = (B,C,D) geeft een intersect (B,C), maar jouw query (A,B,C,D).
Hint: waarschijnlijk ligt dit aan je data dat je geen goede intersect heb kunnen doen

Jouw effectieve query is dus gelijk aan

SQL:

select 
    b.id, 
    b.author_id, 
    b.title, 
    b.genre 
from 
    books b 
    inner join authors a on b.author_id = a.id 
where 
    a.fulltext @@ to_tsquery(
          'english', 'John'
    )
    OR b.fulltext @@ to_tsquery(
          'english', 'John'
    )

Ik zou eerst een query maken die het resultaat van beide condities berekend (AND ipv OR) en als die leeg is dan de andere queries uitvoeren (WHERE NOT EXISTS (...resultaat...) ).
Of gewoon alle matches teruggeven, maar met een GROUP BY aangeven wat de score is (COUNT(id) met: 2=beide condities, 1=maar op een) en dan sorteren zodat je alle "2" scores als eerste krijgt.

[Snippo]

De reden voor mijn aanpak wordt hier uitgelegd:
https://www.cybertec-post...on-in-postgresql-queries/
(n.a.v. https://dba.stackexchange...t-search-across-relations)

Ik heb er zelf weinig verstand van, en het lijkt inderdaad onnodig ingewikkeld maar ik vertrouw maar op StackExchange.