A Tutorial on SQL Nested Queries

The nested SQL query is a form of the SELECT query that is inside another SQL query.
The nested SQL query is also called a subquery. The outside SQL statement is called the parent statement and the inside SQL statement is the nested or subquery. The nested query obtains a result set and the SELECT statement (parent statement) uses this results set for additional processing.

Things You'll Need

• Computer
• Database

Instructions

1. You can use the subquery for the following purposes:
   - defining a set of row that need to be inserted into a targeted table.
   - defining a results set that will be used to create a view or snapshot.
   - defining one or more values for an update statement.
   - providing values for WHERE, HAVING and START WITH clauses for SELECT, UPDATE and DELETE statements.

2. The SQL statement obtains information from a table in a particular database. For this example the database name is emp (for employee), the ename is the name of the employee and deptno is department number. You want to obtain all of the employee names in Smith's department. You want to determine in which department 'SMITH' works and then use that answer to list all of the employee's names in that department:

   SELECT ename, deptno
   FROM emp
   WHERE deptno =
   (SELECT deptno
   FROM emp
   WHERE ename = 'SMITH')

3. The nested query returns the department number (deptno) associated with employee 'SMITH' and the parent query will use the results set for obtaining the name and department number of all employees who work in Smith's department.

   Write your query with the final results in mind and use the nested query to obtain the information necessary for the parent query to return the final results set.

References

• SQL Statements
• SQL queries
• subqueries