SQL Subqueries - Used in various ways

If you master the concepts of subqueries, you would gain a lot of flexibility in writing complex SQL queries. Let's not get into Correlated subqueries, let us see the different versions of nested subquery with some simple examples.
I am attaching the EMP table data as a document in my profile, pls download it and have it inserted in your DB.

What is a Subquery?
A query within a query is Subquery.

For Eg:
Select * from EMP;

To find out all the employees who work in the department of 'Nilesh'
Select * from emp where deptid in (Select deptid from emp where ename='Nilesh');

Further, maximum of 255 nested subquery levels are allowed in Oracle in WHERE clause.

Adding to that you can use subquery in HAVING clause, FROM Clause and SELECT clause also, just like in WHERE Clause.

Eg. for HAVING CLAUSE subquery:
To find out the departments whose department-wise average salary is more than the avg salary of the whole organization.
Select Deptid,count(*),avg(sal) from EMP group by Deptid having avg(sal)>(Select avg(sal) from emp);

Eg. for FROM CLAUSE subquery:
To find out what are the employees salary and minimum salary in their department.
Select Empid,Ename,e.Deptid, Sal, MinSal.min_sal from emp e, (Select deptid,min(sal) min_sal from emp group by deptid) MinSal
where e.deptid = minsal.deptid Order by e.deptid,e.sal

Another common example for FROM CLAUSE Subquery is to find out the 5 most well paid employees.
Select * FROM (Select * from EMP ORDER BY SAL DESC) WHERE Rownum<6

Eg. for SELECT CLAUSE subquery:
To find out the difference between an employee salary and his department avg salary for all the employees.

Select Empid,Ename,Deptid, Sal, Sal-(Select avg(sal) from emp where deptid=e.deptid) DIFF from emp e order by deptid;

The example queries used here can be written in a better and efficient way, but my purpose here is to make it easier to understand for the reader.
Also, we have used only one table. In real life scenarios, you would face lots of tables and more complex situations.
Another importan thing is that joins always work faster than subqueries. But it again depends on optimizer, if it is smart enough and generates the same execution plan for both the queries, you will get the same response time.