DB2 SQL SUBQUERIES

SUBQUERIES

  1. SQL SubQuery is a query which inside another query.
  2. 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
  1. Uncorrelated 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.

  2. Correlated subqueries

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *