Nested Queries (SQL)

A complete Select Query (SQL), otherwise known as a nested query, can be specified within the WHERE clause of another query (called the outer query).

There are different ways we can nest queries:

• IN Clause (SQL)

  The comparison operator IN compares a value $v$ with a set (or multi-set) of values $V$ and evaluates to TRUE if $v$ is one of the elements in $V$:

  SELECT NAME, ADDRESS
  FROM   EMPLOYEE
  WHERE  DNO IN (
  	SELECT DNUMBER
  	FROM   DEPARTMENT
  	WHERE  DNAME = 'Research'
  )

  The nested query selects the number of the ‘Research’ department, the other query selects an employee if its department number is in the result of the nested query.

  In this example, the nested query is not correlated with the outer query.

  Link to original

• Correlated Nested Query (SQL)

  A correlated nested query is where the condition in the WHERE clause of a nested query references an attribute of a relation declared in the outer query. The result of a correlated nested query is different for each tuple of the relation in the outer query.

  SELECT E.name
  FROM   EMPLOYEE AS E
  WHERE  E.ssn IN (
  	SELECT essn
  	FROM   DEPENDENT
  	WHERE  essn=E.ssn
  	  AND  E.name=dependent_name
  )

  Although these can still be re-written as a single block query with a simple Join Condition (SQL).

  Link to original

• Exists Function (SQL)

  EXISTS is used to check whether the result of a Correlated Nested Query (SQL) is empty or not.

  SELECT name
  FROM   EMPLOYEE E
  WHERE  EXISTS (
  	SELECT *
  	FROM   DEPENDENT
  	WHERE  E.ssn=essn
        AND  E.name=dependent_name
  )

  Similarly, you can use NOT EXISTS to check for the reverse condition.

  Link to original

• All Comparison Operator (SQL)

  The comparison operator ALL compares a single value to a set or multi-set (nested query).

  SELECT name
  FROM   employee
  WHERE  salary > ALL(
  	SELECT salary
  	FROM   employee
  	WHERE  department_number=5
  )

  Link to original