You can add subqueries to your query to retrieve a value or set of values from one table so you can select data to display from another table. A subquery is a complete query that appears in the WHERE or HAVING clause of another query.
You can specify up to 16 subqueries within a single query, and you can specify subqueries within a subquery. Subqueries run from last to first within the overall query.
Rules for creating a subquery:
The following query displays the names and IDs of employees who work in Boston. The subquery (in parentheses) finds the department number for the location of BOSTON in the Q.ORG table. Then, the main query selects the names of the employees in that department from the Q.STAFF table.
SELECT NAME, ID
FROM Q.STAFF
WHERE DEPT=(SELECT DEPTNUMB
FROM Q.ORG
WHERE LOCATION='BOSTON')
In the next example, the subquery and main query retrieve data from the same table. The subquery calculates the average salary for all the employees in the Q.STAFF table. Then, the main query selects the salespeople whose salaries are equal to or greater than the average salary.
SELECT ID, NAME, SALARY
FROM Q.STAFF
WHERE JOB = 'SALES' AND
SALARY >= (SELECT AVG(SALARY)
FROM Q.STAFF)
Usually a subquery selects only one column and returns only one value to the query. However, you can create a subquery that returns a set of values using the ANY or ALL keywords used with the comparison operators =, ¬=, >, >=, <, or <=. In addition, just as you use the IN keyword in place of multiple OR statements in a query, you can also use IN in place of the ANY keyword in a subquery.
The query in Figure 67 selects any employee who works in the Eastern division. The subquery finds the department numbers in the Eastern division, and then the main query selects the employees who work in any of these departments.
Use the ANY keyword for this query, because it is likely that the subquery will find more than one department in the Eastern Division. If you use the ALL keyword instead of the ANY keyword, no data is selected, because no employee works in all departments of the Eastern division.
Figure 67. This SQL query contains a subquery using the ANY keyword.
SELECT NAME, ID
FROM Q.STAFF
WHERE DEPT = ANY
(SELECT DEPTNUMB
FROM Q.ORG
WHERE DIVISION='EASTERN')
|
The query in Figure 68 selects the department with the highest average salary. The subquery finds the average salary for each department, and then the main query selects the department with the highest average salary.
Use the ALL keyword for this subquery. The department selected by the query must have an average salary greater than or equal to all the average salaries of the other departments.
Figure 68. This SQL query contains a subquery using the ALL keyword.
SELECT DEPT, AVG(SALARY)
FROM Q.STAFF
GROUP BY DEPT
HAVING AVG(SALARY) >= ALL
(SELECT AVG(SALARY)
FROM Q.STAFF
GROUP BY DEPT)
|
The query in Figure 69 selects all salespeople and their salaries who work for managers who earn more than $20,000 a year. The subquery finds the managers who earn more than $20,000 a year, and then the main query selects the salespeople who work for those managers.
Use the IN keyword for this subquery, because you need to find values from more than one department.
Figure 69. This SQL query contains a subquery using the IN keyword.
SELECT ID, NAME, SALARY
FROM Q.STAFF
WHERE JOB = 'SALES'
AND DEPT IN
(SELECT DISTINCT DEPT
FROM Q.STAFF
WHERE JOB = 'MGR'
AND SALARY > 20000)
|
In the previous examples, you learned how to use a subquery to return a value to the query. You can also use a subquery to check for rows that satisfy a certain row condition using a WHERE EXISTS clause.
The query in Figure 70 selects employees from the Q.STAFF table who have a salary of less than $14,000, and who work in a department where at least one other employee with the same job earns a salary greater than $14,000. The subquery checks for other employees in the department with the same job, but who earn a salary greater than $14,000.
Figure 70. This subquery checks for rows that satisfy a condition.
SELECT NAME, DEPT, JOB, SALARY
FROM Q.STAFF S
WHERE S.SALARY < 14000 AND
EXISTS (SELECT * FROM Q.STAFF
WHERE S.DEPT=DEPT AND SALARY >14000
AND S.JOB=JOB)
ORDER BY S.DEPT
|
You can specify NOT IN in a subquery to select information from one table when corresponding information does not exist in the other table.
You can specify a correlation name in a subquery to evaluate every row that is selected by the query for the condition that is specified in the subquery.
The query in Figure 71 selects the department, name, and salary of the employees who have the highest salary in their departments. The subquery calculates the maximum salary for each department that is selected by the main query. The correlation name, Y, compares each row that is selected by the query to the maximum salary that is calculated for the department in the subquery.
Figure 71. This subquery specifies a correlation name.
SELECT DEPT, NAME, SALARY
FROM Q.STAFF Y
WHERE SALARY = (SELECT MAX (SALARY)
FROM Q.STAFF
WHERE DEPT = Y.DEPT)
|