Sub queries: The SQL query is written within a main Query. This is called as Nested Inner/Sub Query.The sub query is executed first and the results of sub query are used as the condition for main query.
The sub query must follow the below rules:
- Subqueries are always written within the parentheses.
- Always place the Subquery on the right side of the comparison operator.
- ORDER BY clause is not used in sub query, since Subqueries cannot manipulate the results internally.
Consider the Employee table with the fields EmpID, Name, Age and Salary.
In the below Query, we use sub query in an SELECT statement.
SELECT * from Employee
where EmpID IN (SELECT EmpID from Employee WHERE Salary < 20000);
First, the inner query is executed. As a result EmpID 101 and 103 are retrieved. Now the external or outer query is executed. Internally the query is SELECT * from Employee where EmpID IN(101,103) and the output is drawn below.
Select Record List
Similarly the subqueries are used with INSERT, UPDATE and DELETE.
