Sub Queries:

Sub Queries are those query which comes within another query and are written after the WHERE clause.They are also known as Inner query or nested query.

It is used to return records that are used in the main query as a condition to filter the data to be fetched. It can be used with INSERT, SELECT, UPDATE, DELETE statements as well as with comparison operators like =, <, >, >=, <= . IN, NOT IN etc.

Example of Subquery with SELECT Statement:

SELECT max(salary) FROM Employee 
WHERE salary NOT IN (SELECT max(salary) FROM Employee);

The above is a sub query to find the second highest salary of Employees.

Example of Subquery with DELETE Statement:

DELETE FROM Employee   
WHERE salary <  (SELECT MIN(salary) FROM payroll);

The above sub query will delete those employee records whose salary are less than the minimum salary of Payroll table.