2

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.

3

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.