Return to Answer

Assuming (like was added later) a separate table event with all relevant unique id - this helps performance a bit:

WITH cte(event_id, attribute) AS ( -- big SELECT query goes here instead of the VALUES expression VALUES (1, '2D'), (1, 'IMAX'), (2, 'IMAX'), (3, '3D') ) TABLE cte UNION ALL SELECT e.id, '2D' FROM event e LEFT JOIN cte ON cte.event_id = e.id AND cte.attribute IN ('2D','3D') WHERE cte.event_id IS NULL; 

If your query only returns a subset of all event_id, you can't use table event to simplify like this.

• Select rows which are not present in other table
• Optimize GROUP BY query to retrieve latest record per user
• Is there a shortcut for SELECT * FROM?

Assuming (like was added later) a separate table event with all relevant unique id - this helps performance a bit:

WITH cte(event_id, attribute) AS ( -- big SELECT query goes here instead of the VALUES expression VALUES (1, '2D'), (1, 'IMAX'), (2, 'IMAX'), (3, '3D') ) TABLE cte UNION ALL SELECT e.id, '2D' FROM event e LEFT JOIN cte ON cte.event_id = e.id AND cte.attribute IN ('2D','3D') WHERE cte.event_id IS NULL; 

Related:

• Select rows which are not present in other table
• Optimize GROUP BY query to retrieve latest record per user
• Is there a shortcut for SELECT * FROM?

If your query only returns a subset of all event_id, you can't use the table event to simplify like this. Alternative without table event:

WITH cte AS ( -- big SELECT query goes here instead of the VALUES expression VALUES (1, '2D'), (1, 'IMAX'), (2, 'IMAX'), (3, '3D') ) TABLE cte UNION ALL SELECT event_id, '2D' FROM cte GROUP BY 1 HAVING count(*) FILTER (WHERE attribute IN ('2D', '3D')) = 0; 

This is somewhat similar to what you answered yourself, just shorter and a bit faster. In particular the aggregate FILTER clause should be instrumental. Related:

• For absolute performance, is SUM faster or COUNT?

Since there are no indexes on the derived table from the CTE, the second query may be faster to begin with.

Assuming (like you commented) a separate table event with all relevant unique id - this helps performance a bit:

WITH cte(event_id, attribute) AS ( -- big SELECT query goes here instead of the VALUES expression VALUES (1, '2D'), (1, 'IMAX'), (2, 'IMAX'), (3, '3D') ) TABLE cte UNION ALL SELECT e.id, '2D' FROM event e LEFT JOIN cte ON cte.event_id = e.id AND cte.attribute IN ('2D','3D') WHERE cte.event_id IS NULL; 

If your query only returns a subset of all event_id, you can't use table event to simplify like this.

• Select rows which are not present in other table
• Optimize GROUP BY query to retrieve latest record per user
• Is there a shortcut for SELECT * FROM?

Assuming (like was added later) a separate table event with all relevant unique id - this helps performance a bit:

WITH cte(event_id, attribute) AS ( -- big SELECT query goes here instead of the VALUES expression VALUES (1, '2D'), (1, 'IMAX'), (2, 'IMAX'), (3, '3D') ) TABLE cte UNION ALL SELECT e.id, '2D' FROM event e LEFT JOIN cte ON cte.event_id = e.id AND cte.attribute IN ('2D','3D') WHERE cte.event_id IS NULL; 

If your query only returns a subset of all event_id, you can't use table event to simplify like this.

• Select rows which are not present in other table
• Optimize GROUP BY query to retrieve latest record per user
• Is there a shortcut for SELECT * FROM?

Assuming (like you commented) a separate table event with all relevant unique id - this helps performance a bit:

WITH cte(event_id, attribute) AS ( -- big SELECT query goes here VALUES (1, '2D'), (1, 'IMAX'), (2, 'IMAX'), (3, '3D') ) TABLE cte UNION ALL SELECT e.id, '2D' FROM event e LEFT JOIN cte ON cte.event_id = e.id AND cte.attribute IN ('2D','3D') WHERE cte.event_id IS NULL; 

If your query only returns a subset of all event_id, you can't use table event to simplify like this.

• Select rows which are not present in other table
• Optimize GROUP BY query to retrieve latest record per user

Assuming (like you commented) a separate table event with all relevant unique id - this helps performance a bit:

WITH cte(event_id, attribute) AS ( -- big SELECT query goes here instead of the VALUES expression VALUES (1, '2D'), (1, 'IMAX'), (2, 'IMAX'), (3, '3D') ) TABLE cte UNION ALL SELECT e.id, '2D' FROM event e LEFT JOIN cte ON cte.event_id = e.id AND cte.attribute IN ('2D','3D') WHERE cte.event_id IS NULL; 

If your query only returns a subset of all event_id, you can't use table event to simplify like this.

• Select rows which are not present in other table
• Optimize GROUP BY query to retrieve latest record per user
• Is there a shortcut for SELECT * FROM?