Specifying Subqueries in Search Conditions
Purpose
Permits a more sophisticated and detailed query of a database through the use of nested SELECT queries.
Syntax : IN, NOT IN, ANY, SOME, and ALL Logical Predicates
where:
|
Syntax element … |
Specifies … |
|
expression |
any valid SQL expression. To specify multiple expressions, enclose them within PARENTHESIS characters and separate each expression with a COMMA character. |
|
comparison_operator |
any valid SQL comparison operator. See SQL Functions, Operators, Expressions, and Predicates for information about the SQL comparison operators. |
|
IN (in_list) NOT IN (in_list) |
that expression is either IN or NOT IN the parenthetically delimited list of values. See SQL Functions, Operators, Expressions, and Predicates for information about the IN and NOT IN logical predicates and the valid expressions that you can specify in an in_list. |
|
ANY |
the ANY logical quantifier. See SQL Functions, Operators, Expressions, and Predicates for more information about the ANY logical quantifier. |
|
SOME |
the SOME logical quantifier. See SQL Functions, Operators, Expressions, and Predicates for more information about the SOME logical quantifier. |
|
ALL |
the ALL logical quantifier. See SQL Functions, Operators, Expressions, and Predicates for more information about the ALL logical quantifier. |
|
subquery |
any valid SQL subquery. |
Syntax : EXISTS Logical Expression
where:
|
Syntax element … |
Specifies … |
|
EXISTS |
the EXISTS (∃) logical quantifier. See SQL Functions, Operators, Expressions, and Predicates for details. |
|
subquery |
any valid SQL subquery. |
ANSI Compliance
Subqueries are ANSI SQL:2011-compliant.
Specifying Subqueries in an SQL DML Statement
The rules and restrictions are:
This limit is established using the DBS Control record flag MaxParseTreeSegs. The value for MaxParseTreeSegs must be set to 256 or higher define an upper limit of 64 subquery nesting levels.
See Utilities: Volume 1 (A-K) for information about how to change the MaxParseTreeSegs setting in the DBS Control record.
Example : Simple Subquery Using the IN Logical Predicate
This example selects the names and department locations of those employees who report to manager Aguilar (whose employee number is 10007).
SELECT name, loc
FROM employee, department
WHERE employee.dept_no = department.dept_no
AND employee.dept_no IN (SELECT dept_no
FROM department
WHERE mgr_no = 10007);
Example : Simple Subquery Using an AVG Aggregate Expression
The following SELECT statement finds every employee in the employee table with a salary that is greater than the average salary of all employees in the table.
SELECT name, dept_no, jobtitle, salary
FROM employee
WHERE salary > (SELECT AVG(salary)
FROM employee)
ORDER BY name;
The statement returns this results table.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Vice President |
|
Example : Simple Subquery Using the ALL Logical Predicate
The following SELECT statement retrieves the employees with the highest salary and the most years of experience:
SELECT emp_no, name, job_title, salary, yrs_exp
FROM employee
WHERE (salary,yrs_exp) >= ALL (SELECT salary,yrs_exp
FROM employee);
|
The query returns this many rows … |
WHEN this condition evaluates to TRUE … |
|
0 |
employees with the highest salary do not also have the most years experience. |
|
1 |
there is only one employee with the highest salary and years experience. |
|
multiple |
there is more than one employee with the highest salary and years experience. |
For the current data in the employee table, the result looks like this:
EmpNo Name JobTitle Salary YrsExp
----- --------- --------- --------- -------
10018 Russell S President 65,000.00 25
The result shows that one employee has both the highest salary and the most years of experience.
Example : Subquery Using the SUM and COUNT Aggregate Functions
The following statement uses a nested ordered analytical function and a HAVING clause to find those items that appear in the top 1 percent of profitability in more than 20 stores:
SELECT item, COUNT(store)
FROM (SELECT store,item,profit,QUANTILE(100,profit) AS percentile
FROM (SELECT ds.store, it.item, SUM(sales)-
COUNT(sales) * it.item_cost AS profit
FROM daily_sales AS ds, items AS it
WHERE ds.item = it.item
GROUP BY ds.store, it.item, it.item_cost) AS item_profit
GROUP BY store, item, profit
QUALIFY percentile = 0) AS top_one_percent
GROUP BY item
HAVING COUNT(store) >= 20;
The results of this query might look like this:
Item Count(Store)
---------------- ------------
Chilean sea bass 99
Mackerel 149
Tako 121
Herring 120
Salmon 143
Example : Subqueries Using the RANK Functions and QUALIFY Clauses
The following example uses nested RANK functions and QUALIFY clauses to report the top 100 items by profitability and the top items by revenue, matching those that appear in both lists using an OUTER JOIN:
SELECT *
FROM (SELECT item, profit, RANK(profit) AS profit_rank
FROM item, sales
QUALIFY profit_rank <= 100 AS p)
FULL OUTER JOIN
(SELECT item, revenue, RANK(revenue) AS revenue_rank
FROM item, sales
QUALIFY revenue_rank <= 100 AS r)
ON p.item = r.item;
The results of this query might look something like the following table:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
? |
? |
? |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
For More Information
For more information related to simple subqueries, see: