SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-42
Using Joins Instead of Subqueries
These examples show how to reformulate subqueries into join queries. Consider this
query, which contains a subquery:
SELECT employee.name
FROM employee
WHERE employee.dept_no IN
(SELECT dept.dept_no FROM dept
WHERE dept.name = "development")
ORDER BY employee.name ;
You can change this query into a join query as follows:
SELECT employee.name
FROM employee, dept
WHERE employee.dept_no = dept.dept_no
AND dept.name = "development"
ORDER BY employee.name ;
Although both formulations produce the same result, their performances are likely to be
very different: the second formulation always matches or outperforms the first one. The
use of the join minimizes I/O operations for separate SELECT statements. In addition,
in the first formulation, the user has dictated how the query is to be performed (perform
the subquery first and then perform the main query). In the second formulation, SQL
can determine the order of the join and is therefore able to choose the most efficient
way to execute the query.
For another example, this noncorrelated subquery:
SELECT emp_id, first_name, last_name, mgr_id
FROM employee emp1
WHERE emp_id IN (SELECT mgr_id
FROM employee emp2) ;
can be reformulated as a self join in this way:
SELECT emp1.emp_id, emp1.first_name,
emp1.last_name, emp1.mgr_id
FROM employee emp1, employee emp2
WHERE emp2.mgr_id = emp1.emp_id ;
The second query performs better because SQL obtains the entire result with a single
SELECT operation rather than two SELECT operations.
A correlated subquery allows you to take advantage of the EXISTS predicate, however.
If you just want to know whether some condition actually exists in the database or not,
a join can be more costly. Do not discard correlated subqueries unconditionally.
You can tell the type of subquery by looking at EXPLAIN output. For a correlated
subquery, the EXPLAIN utility lists “Executes once for each row retrieved.”
For more information about correlated and noncorrelated subqueries, see Section 1,
Retrieving Data: How to Write Queries.