- SQL SubQuery is a query which inside another query.
- You can include a subquery in a WHERE or HAVING clause by using a basic or quantified comparison,the IN or the EXISTS keyword.
Here is an example of SubQuery using IN Keyword.
SELECT EMPNO, LASTNAME, JOB FROM EMPLOYEE WHERE EMPNO IN (SELECT EMPNO FROM EMPPROJACT WHERE PROJNO = ’QSYS400’)
Using EXISTS keyword
SELECT EMPNO,LASTNAME FROM EMPLOYEE WHERE EXISTS (SELECT * FROM PROJECT WHERE PRSTDATE > ’1982-01-01’);
SubQuery in HAVING clause
SELECT WORKDEPT, DECIMAL(AVG(SALARY),8,2) FROM EMPLOYEE X GROUP BY WORKDEPT HAVING AVG(SALARY) > (SELECT AVG(SALARY) FROM EMPLOYEE WHERE SUBSTR(X.WORKDEPT,1,1) = SUBSTR(WORKDEPT,1,1))
There are 2 variants of SubQueries
Uncorrelated subqueries are where subquery does not use any reference of the outer query. For example
SELECT * from STUDENT WHERE class_Id in (SELECT id from STUDENT_CLASS WHERE class_teacher = “JAMES”)
In this query “select id from STUDENT_CLASS” is an uncorrelated subquery as this does not use any reference from the outer query “Select * from STUDNT”. In this case system first executes the subquery and get the result set and use that result set to complete the execution of outer query. Simply speaking in this case subquery is executed only once and before outer query.
In correlated subqueries a reference (called correlated references) of outer query is used by subquery. This mean subquery need some value from outer query to complete its execution. For example
SELECT* from EMPLOYE E1 WHERE E1.manager_id = (SELECT M1.id from MANAGER M1 WHERE M1.department = E1.department)
In this case subquery “Select M1.id from MANAGER M1“ uses a reference of “E1.department” from outer query “Select * from EMPLOYE E1”. So to complete the execution of subquery SQL need value of “department” column for each record of outer query. Means Subquery is directly dependent on outer query.