How to use the Output of one SQL Statement in another

I have the following statement:

select region_id from regions
where region_name = 'Europe' 

I need th开发者_Python百科e Output from this in the following statement where 'bla' is:

select count(*) from countries
where region_id = 'bla'

How can I do that?

---

Try the IN clause

select distinct * from countries
where region_id IN (select region_id from regions
where region_name = 'Europe')

---

Subqueries to the rescue!

select distinct * 
from countries
where region_id=(select top 1 ir.region_id 
                 from regions ir
                 where ir.region_name = 'Europe' )

Alternatively, you can use in and give it a list of items returned from your query.

---

Using EXISTS:

SELECT c.* 
  FROM COUNTRIES c
 WHERE EXISTS (SELECT NULL 
                 FROM REGIONS r
                WHERE r.region_id = c.region_id
                  AND r.region_name = 'Europe')

My preference is to use EXISTS rather than IN because:

• IN in Oracle has a limit of 1,000 values
• EXISTS allows you to match on more than one column if necessary
• EXISTS returns true on the first match, which can be faster than IN/etc depending on needs

Most mistake EXISTS as a correlated subquery, but it executes differently & doesn't evaluate the SELECT clause - you can test using:

SELECT c.* 
  FROM COUNTRIES c
 WHERE EXISTS (SELECT 1/0
                 FROM REGIONS r
                WHERE r.region_id = c.region_id
                  AND r.region_name = 'Europe')