PL/SQL – subqueries

Subqueries are select queries which results are used as an input of another queries.
Subqueries can return single row result. Then following comparison operators can be used with them: <, >, =, <>.
Subqueries can return set of rows as a result. Then following operators can be used with them: IN, NOT IN, ANY, ALL.
Correlated subqueries are subqueries which result is dependent on outer query. Watch out: it has poor performance.

Examples:

-- selects all countries which belong to Europe
select * from countries where region_id =
(select region_id from regions where region_name = 'Europe'); --single-row subquery

-- selects all countries which not belong to Europe
select * from countries where region_id IN
(select region_id from regions where region_name <> 'Europe' ); --multi-row subquery

-- selects employees who earn more than all quys from specified department
select last_name from employees where salary > all
(select salary from employees where department_id=80); --multi-row subquery

-- selects employees who earn less than average in their departments
select e1.last_name, e1.salary from employees e1
where salary < (select avg(salary) from employees e2 where e2.department_id = e1.department_id);
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s