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.
