I want to know the meaning of below code which was written in sql

Question

One of colleague used this query, i want to know the meaning of inner select query (i.e, select 1 from table (:input_country_object)). Can anyone explain me in detail how it works.

Select 
       id, 
       country_name, 
       price, 
       section, 
       population, 
       diversity 
from Country co 
where exists (
               select 1 
               from table (:input_country_object) ico 
               where co.country_name = ico.country_name
              );

Thanks in Advance.

Answer 1

If you only care about what the inner select does, here you go:

select 1 
from table (:input_country_object) ico 
where co.country_name = ico.country_name

Your query basically just selects the value 1 if the where condition is met.

So if there is data for this condition: where co.country_name = ico.country_name in the :input_country_object table, then this select just returns 1.

This causes the exists (...) part of your query to return true and therefore perform the first part of your query Select id, country_name, price, section, population, diversity from Country co.

So overall, the query returns you these values:

id, country_name, price, section, population, diversity

from your

Country table,

for all countries which are also in the second table

:input_country_object

---

:input_country_object itself is a variable that the SQL-Developer will fill in, or you have to assign.