132 lines
8.1 KiB
HTML
132 lines
8.1 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="Include subqueries in WHERE or HAVING clauses" />
|
||
|
|
<meta name="abstract" content="Here are several ways you can use to include a subquery in either a WHERE or HAVING clause." />
|
||
|
|
<meta name="description" content="Here are several ways you can use to include a subquery in either a WHERE or HAVING clause." />
|
||
|
|
<meta name="DC.subject" content="examples, subquery, basic comparisons, basic comparison, quantified comparison, SOME, ANY, ALL, comparisons, IN, IN keyword, IN keyword, subquery, use in, EXISTS keyword, EXISTS keyword, use in subquery, keyword, EXISTS" />
|
||
|
|
<meta name="keywords" content="examples, subquery, basic comparisons, basic comparison, quantified comparison, SOME, ANY, ALL, comparisons, IN, IN keyword, IN keyword, subquery, use in, EXISTS keyword, EXISTS keyword, use in subquery, keyword, EXISTS" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafysubq.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="rbafyhowsub" />
|
||
|
|
<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>Include subqueries in WHERE or HAVING clauses</title>
|
||
|
|
</head>
|
||
|
|
<body id="rbafyhowsub"><a name="rbafyhowsub"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Include subqueries in WHERE or HAVING clauses</h1>
|
||
|
|
<div><p>Here are several ways you can use to include a subquery in either
|
||
|
|
a WHERE or HAVING clause.</p>
|
||
|
|
<div class="section"><ul><li>Basic comparisons</li>
|
||
|
|
<li>Quantified comparisons (ALL, ANY, and SOME)</li>
|
||
|
|
<li>IN keyword</li>
|
||
|
|
<li>EXISTS keyword</li>
|
||
|
|
</ul>
|
||
|
|
</div>
|
||
|
|
<div class="section"><h4 class="sectiontitle">Basic comparisons</h4><div class="p"><img src="./delta.gif" alt="Start of change" />You can use a
|
||
|
|
subquery before or after any of the comparison operators. The subquery can
|
||
|
|
return only one row. It can return multiple values for the row if the equal
|
||
|
|
or not equal operators are used. SQL compares each value from the subquery
|
||
|
|
row with the corresponding value on the other side of the comparison operator.
|
||
|
|
For example, suppose you want to find the employee numbers, names, and salaries
|
||
|
|
for employees whose education level is higher than the average education level
|
||
|
|
throughout the company. <pre> <strong>SELECT</strong> EMPNO, LASTNAME, SALARY
|
||
|
|
<strong>FROM</strong> CORPDATA.EMPLOYEE
|
||
|
|
<strong>WHERE</strong> EDLEVEL >
|
||
|
|
(<strong>SELECT AVG</strong>(EDLEVEL)
|
||
|
|
<strong>FROM</strong> CORPDATA.EMPLOYEE)</pre>
|
||
|
|
<img src="./deltaend.gif" alt="End of change" /></div>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>SQL first evaluates the subquery and then substitutes the result
|
||
|
|
in the WHERE clause of the SELECT statement. In this example, the result is
|
||
|
|
the company-wide average educational level. Besides returning a single row,
|
||
|
|
a subquery can return no rows. If it does, the result of the compare is unknown.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><h4 class="sectiontitle">Quantified comparisons (ALL, ANY, and SOME)</h4><p>You
|
||
|
|
can use a subquery after a comparison operator followed by the keyword ALL,
|
||
|
|
ANY, or SOME. When used in this way, the subquery can return zero, one, or
|
||
|
|
many rows, including null values. You can use ALL, ANY, and SOME in the following
|
||
|
|
ways:</p>
|
||
|
|
<ul><li>Use ALL to indicate that the value you supplied must compare in the indicated
|
||
|
|
way to <strong>ALL</strong> the rows the subquery returns. For example, suppose you
|
||
|
|
use the greater-than comparison operator with ALL: <pre>… <strong>WHERE</strong> expression > <strong>ALL</strong> (subquery)</pre>
|
||
|
|
<p>To
|
||
|
|
satisfy this WHERE clause, the value of the expression must be greater than
|
||
|
|
the result for each of the rows (that is, greater than the highest value)
|
||
|
|
returned by the subquery. If the subquery returns an empty set (that is, no
|
||
|
|
rows were selected), the condition is satisfied.</p>
|
||
|
|
</li>
|
||
|
|
<li>Use ANY or SOME to indicate that the value you supplied must compare in
|
||
|
|
the indicated way to <em>at least one</em> of the rows the subquery
|
||
|
|
returns. For example, suppose you use the greater-than comparison operator
|
||
|
|
with <strong>ANY</strong>: <pre>… <strong>WHERE</strong> expression > <strong>ANY</strong> (subquery)</pre>
|
||
|
|
<p>To
|
||
|
|
satisfy this WHERE clause, the value in the expression must be greater than
|
||
|
|
at least one of the rows (that is, greater than the lowest value) returned
|
||
|
|
by the subquery. If what the subquery returns is the empty set, the condition
|
||
|
|
is not satisfied.</p>
|
||
|
|
</li>
|
||
|
|
</ul>
|
||
|
|
<div class="note"><span class="notetitle">Note:</span> The results when a subquery returns one or more null values may
|
||
|
|
surprise you, unless you are familiar with formal logic.</div>
|
||
|
|
</div>
|
||
|
|
<div class="section"><h4 class="sectiontitle">IN keyword</h4><p>You can use IN to say that the value
|
||
|
|
in the expression must be among the rows returned by the subquery. Using IN
|
||
|
|
is equivalent to using <em>=ANY</em> or <em>=SOME</em>. Using ANY and SOME were
|
||
|
|
previously described. You can also use the IN keyword with the NOT keyword
|
||
|
|
in order to select rows when the value is not among the rows returned by the
|
||
|
|
subquery. For example, you can use:</p>
|
||
|
|
<pre>… <strong>WHERE</strong> WORKDEPT <strong>NOT IN</strong> (<strong>SELECT</strong> …)</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><h4 class="sectiontitle">EXISTS keyword</h4><p>In the subqueries presented so far,
|
||
|
|
SQL evaluates the subquery and uses the result as part of the WHERE clause
|
||
|
|
of the outer-level SELECT. In contrast, when you use the keyword EXISTS, SQL
|
||
|
|
checks whether the subquery returns one or more rows. If it does, the condition
|
||
|
|
is satisfied. If it returns no rows, the condition is not satisfied. For example:</p>
|
||
|
|
<pre> <strong>SELECT</strong> EMPNO,LASTNAME
|
||
|
|
<strong>FROM</strong> CORPDATA.EMPLOYEE
|
||
|
|
<strong>WHERE EXISTS</strong>
|
||
|
|
(<strong>SELECT</strong> *
|
||
|
|
<strong>FROM</strong> CORPDATA.PROJECT
|
||
|
|
<strong>WHERE</strong> PRSTDATE > '1982-01-01');</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>In the example, the search condition is true if any project represented
|
||
|
|
in the CORPDATA.PROJECT table has an estimated start date that is later than
|
||
|
|
January 1, 1982. This example does not show the full power of EXISTS, because
|
||
|
|
the result is always the same for every row examined for the outer-level SELECT.
|
||
|
|
As a consequence, either every row appears in the results, or none appear.
|
||
|
|
In a more powerful example, the subquery itself would be correlated, and change
|
||
|
|
from row to row.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>As shown in the example, you do not need to specify column names
|
||
|
|
in the select-list of the subquery of an EXISTS clause. Instead, you should
|
||
|
|
code SELECT *.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>You can also use the EXISTS keyword with the NOT keyword in order
|
||
|
|
to select rows when the data or condition you specify does not exist. You
|
||
|
|
can use the following:</p>
|
||
|
|
<pre>… <strong>WHERE NOT EXISTS</strong> (<strong>SELECT</strong> …)</pre>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbafysubq.htm" title="Subqueries can help you to further refine your search conditions.">Subqueries in SELECT statements</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|