CODEWITHSUNDEEP
sqlpostgresqlunioncommon-table-expression
Bwyss
Bwyss

Reputation: 1834

Combine two SELECT queries in PostgreSQL

I would like to combine two select queries with UNION.
How can I use the result from the first SELECT in the second SELECT?

(SELECT carto_id_key FROM table1
    WHERE tag_id = 16)
UNION 
(SELECT * FROM table2
    WHERE carto_id_key = <the carto_id result from above> )

Upvotes: 25

Views: 54977

Answers (2)

Rocky
Rocky

Reputation: 1

This is simpler solution to just have a normal select and combine result using WITH :

WITH combinedResult AS (
SELECT
    (SELECT COUNT(t1) FROM table1 t1) as table1Count,
    (SELECT COUNT(t2) FROM table2 t2) AS table2Count 
                       )
SELECT table1Count, table2Count FROM combinedResult;

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659367

Use a CTE to reuse the result from a subquery in more than one SELECT.

WITH cte AS (SELECT carto_id_key FROM table1 WHERE tag_id = 16)

SELECT carto_id_key
FROM   cte

UNION ALL
SELECT t2.some_other_id_key
FROM   cte
JOIN   table2 t2 ON t2.carto_id_key = ctex.carto_id_key

You most probably want UNION ALL instead of UNION. Doesn't exclude duplicates and is faster this way.

Upvotes: 35

Related Questions