Subqueries | SQL
In SQL a subquery can be simply defined as a query within another query. In other words we can say that a Subquery is a query that is embedded in the WHERE clause of another SQL query.
Important rules for Subqueries,
You can place the Subquery in a number of SQL clauses: WHERE clause, HAVING clause, FROM clause. Subqueries can be used with SELECT, INSERT, UPDATE, DELETE statements along with expression operators. It could be equality operator or comparison operators such as =, >= , <= and LIKE operator.
A subquery is a query within a query. The outer query is called the main query and inner query is called Subquery.
The subquery generally executes first when the subquery does not have any co-relation with the main query, when there is a co-relation the parser takes the decision on the fly on which query to execute on the precedence and uses the output of subquery accordingly.
Subqueries must be enclosed in parenthesis.
Subqueries are on the right side of the comparison operator.
ORDER-BY command cannot be used in the subquery, GROUP-BY command can be used to perform same function as ORDER-BY command.
Use single-row operators with single row subqueries, multi-row operators with multiple row subqueries.
Syntax :
There is not any general syntax for subqueries. However, subqueries are seem to be used most frequently with the SELECT statement as shown below :
Example : DATABASE
Query :
To display NAME, LOCATION and PHONE_NUMBER of the students from DATABASE table whose SECTION is 'A'.
Action :
SELECT NAME, LOCATION, PHONE_NUMBER from DATABASE
WHERE ROLL_NO IN
(SELECT ROLL_NO FROM STUDENT WHERE SECTION = 'A' );
Explanation :
The subquery executes first, SELECT ROLL_NO FROM STUDENT WHERE SECTION = 'A' , it returns the ROLL-NO from STUDENT table who's SECTION is 'A'. Then the main query executes SELECT NAME, LOCATION, PHONE_NUMBER from DATABASE, it returns the NAME, LOCATION and PHONE_NUMBER of the student who's ROLL_NO returned by the subquery.
OUTPUT :
