Combine two SELECT queries in PostgreSQL

sql postgresql union common-table-expression
Bwyss picture Bwyss · Apr 6, 2013 · Viewed 29.2k times · Source

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

Answer

Erwin Brandstetter picture Erwin Brandstetter · Apr 6, 2013

Use a CTE to reuse the result from a subquery in more than one SELECT.
You need PostgreSQL 8.4+ for that:

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

SELECT carto_id_key
FROM   x

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

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

Related questions

Combine multiple SELECT statements

I've used Excel to generate numerous SELECT statements from a list of the schema names from a database with a large number of identical schemas: select result from foo.table limit 1; select result from bar.table limit 1; select result from …

Read More
Combine two sql select queries (in postgres) with LIMIT statement

I've got a table and I want a query that returns the last 10 records created plus the record who's id is x. I'm trying to do - SELECT * FROM catalog_productimage ORDER BY date_modified LIMIT 10 UNION SELECT * FROM catalog_…

Read More
ERROR: failed to find conversion function from unknown to text

There is an error on PostgreSQL that it gives on one of my select statements. I searched the web for an answer and came out empty handed. The answer given in another question did not suit my problem. ERROR: failed …

Read More