90 lines
5.6 KiB
HTML
90 lines
5.6 KiB
HTML
|
<?xml version="1.0" encoding="UTF-8"?>
|
||
|
|
<!DOCTYPE html
|
||
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
|
|
<html lang="en-us" xml:lang="en-us">
|
||
|
|
<head>
|
||
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
|
|
<meta name="security" content="public" />
|
||
|
|
<meta name="Robots" content="index,follow" />
|
||
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
||
|
|
<meta name="DC.Type" content="reference" />
|
||
|
|
<meta name="DC.Title" content="Example: Correlated subquery in a HAVING clause" />
|
||
|
|
<meta name="abstract" content="Suppose that you want a list of all the departments whose average salary is higher than the average salary of their area (all departments whose WORKDEPT begins with the same letter belong to the same area). To get this information, SQL must search the CORPDATA.EMPLOYEE table." />
|
||
|
|
<meta name="description" content="Suppose that you want a list of all the departments whose average salary is higher than the average salary of their area (all departments whose WORKDEPT begins with the same letter belong to the same area). To get this information, SQL must search the CORPDATA.EMPLOYEE table." />
|
||
|
|
<meta name="DC.subject" content="correlation, subqueries, example HAVING clause, subquery, correlated, example HAVING clause, examples, correlated subquery, HAVING clause" />
|
||
|
|
<meta name="keywords" content="correlation, subqueries, example HAVING clause, subquery, correlated, example HAVING clause, examples, correlated subquery, HAVING clause" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafycorrs.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="rbafyexsub2" />
|
||
|
|
<meta name="DC.Language" content="en-us" />
|
||
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
||
|
|
<!-- US Government Users Restricted Rights -->
|
||
|
|
<!-- Use, duplication or disclosure restricted by -->
|
||
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
|
|
<title>Example: Correlated subquery in a HAVING clause</title>
|
||
|
|
</head>
|
||
|
|
<body id="rbafyexsub2"><a name="rbafyexsub2"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Example: Correlated subquery in a HAVING clause</h1>
|
||
|
|
<div><p>Suppose that you want a list of all the departments whose average
|
||
|
|
salary is higher than the average salary of their area (all departments whose
|
||
|
|
WORKDEPT begins with the same letter belong to the same area). To get this
|
||
|
|
information, SQL must search the CORPDATA.EMPLOYEE table.</p>
|
||
|
|
<div class="section"><p>For each department in the table, SQL compares the department's
|
||
|
|
average salary to the average salary of the area. In the subquery, SQL calculates
|
||
|
|
the average salary for the area of the department in the current group. For
|
||
|
|
example:</p>
|
||
|
|
<pre> <strong>SELECT</strong> WORKDEPT, <strong>DECIMAL</strong>(<strong>AVG</strong>(SALARY),8,2)
|
||
|
|
<strong>FROM</strong> CORPDATA.EMPLOYEE X
|
||
|
|
<strong>GROUP BY</strong> WORKDEPT
|
||
|
|
<strong>HAVING AVG</strong>(SALARY) >
|
||
|
|
(<strong>SELECT AVG</strong>(SALARY)
|
||
|
|
<strong>FROM</strong> CORPDATA.EMPLOYEE
|
||
|
|
<strong>WHERE SUBSTR</strong>(X.WORKDEPT,1,1) = <strong>SUBSTR</strong>(WORKDEPT,1,1))</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Consider what happens when the subquery is executed for a given
|
||
|
|
department of CORPDATA.EMPLOYEE. Before it is executed, the occurrence of
|
||
|
|
X.WORKDEPT is replaced with the value of the WORKDEPT column for that group.
|
||
|
|
Suppose, for example, that the first group selected has A00 for the value
|
||
|
|
of WORKDEPT. The subquery executed for this group is: </p>
|
||
|
|
<pre> (<strong>SELECT AVG</strong>(SALARY)
|
||
|
|
<strong>FROM</strong> CORPDATA.EMPLOYEE
|
||
|
|
<strong>WHERE SUBSTR</strong>('A00',1,1) = <strong>SUBSTR</strong>(WORKDEPT,1,1))</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Thus, for the group considered, the subquery produces the average
|
||
|
|
salary for the area. This value is then compared in the outer statement to
|
||
|
|
the average salary for department 'A00'. For some other group for which WORKDEPT
|
||
|
|
is 'B01', the subquery results in the average salary for the area where department
|
||
|
|
B01 belongs.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>The result table produced by the query has the following values:</p>
|
||
|
|
|
||
|
|
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" frame="hsides" border="1" rules="all"><thead align="left"><tr><th valign="top" id="d0e95">WORKDEPT</th>
|
||
|
|
<th valign="top" id="d0e97">AVG SALARY</th>
|
||
|
|
</tr>
|
||
|
|
</thead>
|
||
|
|
<tbody><tr><td valign="top" headers="d0e95 ">D21</td>
|
||
|
|
<td valign="top" headers="d0e97 ">25668.57</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e95 ">E01</td>
|
||
|
|
<td valign="top" headers="d0e97 ">40175.00</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e95 ">E21</td>
|
||
|
|
<td valign="top" headers="d0e97 ">24086.66</td>
|
||
|
|
</tr>
|
||
|
|
</tbody>
|
||
|
|
</table>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbafycorrs.htm" title="You can write a subquery that SQL might need to re-evaluate as it examines each new row (WHERE clause) or group of rows (HAVING clause) in the outer-level SELECT. This is called a correlated subquery.">Correlated subqueries</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|