Subquery

This is a query in 'where' clause of a bigger query and serves as columns clause of the 'where' clause.

Syntax:

select t1.col1, t1.col2... from tab_1 t1 where t1.col1 =/in (select t2.col1, t2.col2... from tab_2 t2 where t2.col3 = t1.col3 and....);

Example - Find out all employees who are in Mumbai location.

select * from emp e where e.depid in (select d.depid from deptt d where d.depcity = 'Mumbai');