Combine two SELECT queries in PostgreSQL

Question

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

what do you actually want to achieve, this can be with a simple JOIN or a Subquery (if you just wish to get the required details), also the columns both of your queries dont match !! — Akash
– Akash, Commented Apr 6, 2013 at 10:14
I want to get all the records in table2 that meet the condition of the first SELECT — Bwyss
– Bwyss, Commented Apr 6, 2013 at 10:26
if that's the only requirement, read postgresql.org/docs/8.3/static/tutorial-join.html — Akash
– Akash, Commented Apr 6, 2013 at 10:31
After looking at that documentation I found the best solution to be: SELECT * FROM tabl1 INNER JOIN table2 ON (table1. carto_id_key = table2. carto_id_key) WHERE table2.tag_id = 16 thanks for the help — Bwyss
– Bwyss, Commented Apr 6, 2013 at 14:20

Erwin Brandstetter · Accepted Answer · 2021-03-12 01:38:35Z

35

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.

edited Mar 12, 2021 at 1:38

answered Apr 6, 2013 at 11:32

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Over a year ago

Nice. I just tried this and found that for UNION ALL to work, both of the SELECT statements had to return the same number of columns, with the same data types.

Rocky · Accepted Answer · 2024-08-06 09:56:10Z

0

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;

answered Aug 6, 2024 at 9:56

Rocky

1

Collectives™ on Stack Overflow

Combine two SELECT queries in PostgreSQL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related