Understanding sQL Subquery
SQL Subquery is a mechanism implemented on SELECT statement whenever a condition's expression is unknown. SQL Subquery is a query within another SQL query. It is a mechanism where the output returned by the subquery is used by the outer query to produce the final result. It is also called as inner query or nested query.
A subquery has two parts:
- Inner Query: It evaluates a condition and provides the output to the outer query.
- Outer Query: It accepts the output from the inner query and produces the output.
Syntax: SELECT <EXPRESSION> FROM <TABLES> WHERE <COLUMN> <OPERATOR> (SELECT <COLUMN> FROM <TABLE> WHERE <CONDITION>);
Depending on the output returned by the inner query to the outer query, a subquery can be categorized into two types:
- Single-Row Subquery
- Multi-Row Subquery
Single row subquery is a type of subquery where inner subquery returns a single value to outer query for processing.
The syntax of single row subquery & multi row subquery is same, only difference is the operator used. Single-row subquery operators are: >, <, >=, <=, <>, =.
Consider the below table for the examples shown:
//Display those records whose salary is greater than Nick’s salary. SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME='NICK'); //Display those records whose DEPTID is same as Alan's job but it should not display Alan’s record. SELECT * FROM EMP WHERE DEPTID = (SELECT DEPTID FROM EMP WHERE ENAME='ALAN') AND ENAME <> ‘ALAN’; //Display those records whose salary is greater as Paul's salary but is less than Jack’s record. SELECT * FROM EMP WHERE SAL BETWEEN (SELECT SAL FROM EMP WHERE ENAME='PAUL') AND (SELECT SAL FROM EMP WHERE ENAME='JACK');
Multi-row subquery is the subquery where inner query returns more than one value to outer query for processing. Multi-row subquery operators are: IN, =ANY, <ANY, >ANY, <ALL, >.
'IN' and '=ANY' operation, both are similar (perform the same task) in case of multi-row subquery.
//Display those records where salary is greater than all salary of dept 2. SELECT * FROM EMP WHERE SAL > ALL (SELECT SAL FROM EMP WHERE DEPTID=2);
The above example will display those records that is grater than the maximum salary of dept 2
Note: If >ALL is replaced with >ANY, It displays those records that are greater the the minimum salary of dept 2.
Note: Order By Clause is not supported in the inner query. The below statement is invalid.
SELECT * FROM EMP WHERE SAL >ANY (SELECT SAL FROM EMP WHERE DEPTNO=30 ORDER BY SAL);