A sub-query is also called an inner query or a nested query is embeded within the WHERE clause.
A subquery is used to return the data that will be used in main query as a condition to further restrict the data to be retrieved.
Subqueries can be used with the SELECT,INSERT,UPDATE, and DELETE statements along with the operators like =,<,>,>=,>=,IN,BETWEEN,etc.
There are few rules that subqueries must follow-
- subqueries must be enclosed within paranthesis.
- A subquery can have only one coloumn in the SELECT clause,unless multiple coloumns are in main query for the subquery to compare its selected coloumns.
- An ORDER BY command cannot be used in a subquery,although the main query can use an ORDER BY.The GROUP BY command can be used to perform the same function as the ORDER BY in a subquery.
- Subqueries that return more than one row can only be used with multiple value operators such as the N operator.
- The SELECT list cannot introduce any references to values that evaluate to a BLOB,ARRAY,CLOB, or NCLOB.
- A subquery cannot be immediatley enclosed in a set function.
- The BETWEEN operator cannot be used with a subquery.However, the BETWEEN operator can be used within the subquery.
Use of a subquery:
A subquery can be used anywhere an an expression is allowed .
we can use sub queries in the UPDATE statement as below:
SET SALARY =SALARY + (SALARY * 0.1)
WHERE DEPT_ID = (SELECT DEPT_ID FROM DEPT WHERE _NAME =‘DESIGN’);
The above query is written to update the salary of DESIGN employees by 10%.
we can use subquery in the DELETE statement as below:
WHERE DEPT_ID = (SELECT DEPT_ID FROM DEPT WHERE DEPT_NAME=‘DESIGN’);
Subqueries can be used to compare the values using SOME clause .SOME clause is used when we have to compare the list of values using OR clause.if any one in the list value is matching,the result will be displayed.
subqueries can be used to compare the values using ALL clauseALL clause is used when we have to compare the list of values using AND clause.
subqueries can be used along with UNIQUE clause to check if the subquery returns duplicant values.