Is it possible to INTERSECT on a subset of columns?

Question

Short version

Per the title: would it be possible to have two SELECT statements with different columns, and call INTERSECT considering only the common columns?

Long version

Keeping the XY problem in mind, a short explanation. Let's say I have a table of interest with the parameters I am interested in, and a number of smaller tables describing additional properties of different types, or maybe referring to other tables:

CREATE TABLE document(
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title TEXT,
    author TEXT,
    property1 TEXT,
--  ...
)

CREATE TABLE document_subtype_X(
    document_id INT REFERENCES document.id,
    property_X TEXT,
--  ...
)

CREATE TABLE document_subtype_Y(
    document_id INT REFERENCES document.id,
    property_Y TEXT,
--  ...
)

CREATE TABLE document_topic(
    document_id INT REFERENCES document.id,
    topic REFERENCES topic.name,
    PRIMARY KEY (document_id, topic)
)

If I want to filter by multiple criteria residing in the child and joining tables, I could build one big de-normalized view and SELECT WHERE on all those criteria.

But let's say I am interested in filtering on the extra criteria, but I do not otherwise wish to SELECT them. I was thinking I could build several smaller queries and use INTERSECT, like:

SELECT document_id AS id FROM document_subtype_X WHERE property_X = 'Z'
INTERSECT
SELECT document_id AS id FROM document_subtype_Y WHERE property_Y = 'Z'
INTERSECT
SELECT document_id AS id FROM document_topic WHERE topic = 'Z'
INTERSECT -- hypothetical ... ON id ...
SELECT id, title, author, property1 from document;

Does this last step exist or would I have to do an INNER JOIN? Or pass on the ids as a list inside a Common Table Expression to an WHERE id IN (...) statement?

The reason I am thinking about this is basically to avoid multiple JOINs and make things more modular in respect to application code. Conceptually it seems like getting a bunch of ID lists, doing some set operations and only then obtaining results would be more elegant than generating a huge table to subset from. But please let me know if I am going down the wrong path.

Answer 1

No, this is not possible in Postgres. There are some DBMS that have implemented a (non-standard) SEMI JOIN operator but this is not part of the standard SQL so unlikely to be added to Postgres.

You can use normal JOIN or IN / EXISTS subqueries for what you want. Examples, using CTE:

WITH filtering (id) AS
( SELECT document_id FROM document_subtype_X WHERE property_X = 'X'
  INTERSECT
  SELECT document_id FROM document_subtype_Y WHERE property_Y = 'Y'
  INTERSECT
  SELECT document_id FROM document_topic WHERE topic = 'Z'
)
SELECT d.id, d.title, d.author, d.property1
FROM document AS d
     JOIN filtering USING (id) ;

or multiple joins - this is equivalent only if (document_id, <filtered_column>) is unique in every subtype table used in the joins/filters, as it is in your examples:

SELECT d.id, d.title, d.author, d.property1
FROM document AS d
     JOIN document_subtype_X AS dx
       ON dx.document_id = d.id AND dx.property_X = 'X'
     JOIN document_subtype_Y AS dy
       ON dy.document_id = d.id AND dy.property_Y = 'Y'
     JOIN document_topic     AS dt
       ON dt.document_id = d.id AND dt.property_Z = 'Z'  ;

or EXISTS subqueries:

SELECT d.id, d.title, d.author, d.property1
FROM document AS d
WHERE EXISTS
      ( SELECT 
        FROM document_subtype_X AS dx
        WHERE dx.document_id = d.id AND dx.property_X = 'X'
      )
  AND EXISTS
      ( SELECT 
        FROM document_subtype_Y AS dy
        WHERE dy.document_id = d.id AND dy.property_Y = 'Y'
      )
  AND EXISTS
      ( SELECT 
        FROM document_topic AS dt
        WHERE dt.document_id = d.id AND dt.property_Z = 'Z'
      )
 ;