90 lines
6.5 KiB
HTML
90 lines
6.5 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="Multiple join types for a query" />
|
|
<meta name="abstract" content="Even though multiple join types (inner, left outer, right outer, left exception, and right exception) can be specified in the query using the JOIN syntax, the iSeries Licensed Internal Code can only support one join type of inner, left outer, or left exception join type for the entire query. This requires the optimizer to determine what the overall join type for the query should be and to reorder files to achieve the correct semantics." />
|
|
<meta name="description" content="Even though multiple join types (inner, left outer, right outer, left exception, and right exception) can be specified in the query using the JOIN syntax, the iSeries Licensed Internal Code can only support one join type of inner, left outer, or left exception join type for the entire query. This requires the optimizer to determine what the overall join type for the query should be and to reorder files to achieve the correct semantics." />
|
|
<meta name="DC.Relation" scheme="URI" content="perf24.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="rzajqmultijoin" />
|
|
<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>Multiple join types for a query</title>
|
|
</head>
|
|
<body id="rzajqmultijoin"><a name="rzajqmultijoin"><!-- --></a>
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
|
<h1 class="topictitle1">Multiple join types for a query</h1>
|
|
<div><p>Even though multiple join types (inner, left outer, right outer,
|
|
left exception, and right exception) can be specified in the query using the
|
|
JOIN syntax, the <span class="keyword">iSeries™</span> Licensed
|
|
Internal Code can only support one join type of inner, left outer, or left
|
|
exception join type for the entire query. This requires the optimizer to determine
|
|
what the overall join type for the query should be and to reorder files to
|
|
achieve the correct semantics. </p>
|
|
<div class="section"><div class="p"><div class="note"><span class="notetitle">Note:</span> This section does not apply to SQE or OPNQRYF.</div>
|
|
</div>
|
|
</div>
|
|
<div class="section"><p>The optimizer will evaluate the join criteria along with any row
|
|
selection that may be specified in order to determine the join type for each
|
|
dial and for the entire query. Once this information is known the optimizer
|
|
will generate additional selection using the relative row number of the tables
|
|
to simulate the different types of joins that may occur within the query.</p>
|
|
</div>
|
|
<div class="section"><p>Since null values are returned for any unmatched rows for either
|
|
a left outer or an exception join, any isolatable selection specified for
|
|
that dial, including any additional join criteria that may be specified in
|
|
the WHERE clause, will cause all of the unmatched rows to be eliminated (unless
|
|
the selection is for an IS NULL predicate). This will cause the join type
|
|
for that dial to be changed to an inner join (or an exception join) if the
|
|
IS NULL predicate was specified.</p>
|
|
</div>
|
|
<div class="section"><p>In the following example a left outer join is specified between
|
|
the tables EMPLOYEE and DEPARTMENT. In the WHERE clause there are two selection
|
|
predicates that also apply to the DEPARTMENT table. </p>
|
|
<pre> <strong>SELECT</strong> EMPNO, LASTNAME, DEPTNAME, PROJNO
|
|
<strong>FROM</strong> CORPDATA.EMPLOYEE XXX <strong>LEFT OUTER JOIN</strong> CORPDATA.DEPARTMENT YYY
|
|
<strong>ON</strong> XXX.WORKDEPT = YYY.DEPTNO
|
|
<strong>LEFT OUTER JOIN</strong> CORPDATA.PROJECT ZZZ
|
|
<strong>ON</strong> XXX.EMPNO = ZZZ.RESPEMP
|
|
<strong>WHERE</strong> XXX.EMPNO = YYY.MGRNO <strong>AND</strong>
|
|
YYY.DEPTNO <strong>IN</strong> ('A00', 'D01', 'D11', 'D21', 'E11')</pre>
|
|
</div>
|
|
<div class="section"><p>The first selection predicate, XXX.EMPNO = YYY.MGRNO, is an additional
|
|
join condition that will be added to the join criteria and evaluated as an
|
|
"inner join" join condition. The second is an isolatable selection predicate
|
|
that will eliminate any unmatched rows. Either one of these selection predicates
|
|
will cause the join type for the DEPARTMENT table to be changed from a left
|
|
outer join to an inner join.</p>
|
|
</div>
|
|
<div class="section"><div class="p">Even though the join between the EMPLOYEE and the DEPARTMENT table
|
|
was changed to an inner join the entire query will still need to remain a
|
|
left outer join to satisfy the join condition for the PROJECT table. <div class="note"><span class="notetitle">Note:</span> Care
|
|
must be taken when specifying multiple join types since they are supported
|
|
by appending selection to the query for any unmatched rows. This means that
|
|
the number of resulting rows that satisfy the join criteria can become quite
|
|
large before any selection is applied that will either select or omit the
|
|
unmatched rows based on that individual dial's join type.</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
<div>
|
|
<div class="familylinks">
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="perf24.htm" title="A join operation is a complex function that requires special attention in order to achieve good performance. This section describes how DB2 Universal Database for iSeries implements join queries and how optimization choices are made by the query optimizer. It also describes design tips and techniques which help avoid or solve performance problems.">Join optimization</a></div>
|
|
</div>
|
|
</div>
|
|
</body>
|
|
</html> |