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