A sub-query is a part of query. Sub-queries are useful to find unknown value(s).

Ex: To find details of employees who are getting maximum salary.

In this requirement the maximum salary not mentioned.

Solution:

To find maximum salary

select max(sal) from emp;

For that maximum salary the employee details

select * from emp where sal (select max(sal) from emp);

So in the above query select max(sal) from emp is also a query but it returns a value (unknown or not given) and compares with sal column. This query is called sub-query.

In is used to find in a range of values.

Eg: select empno ename from employee where department in ( SALES ACCOUNTS FINANCE HR );

Records from every department are fetched. Checks department whether in SALES or in ACCOUNTS or in FINANCE or in HR.

Exists just checks for the condition and returns true or false.

Eg: select empno ename from employee where exists(sal>0);