Edit C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\odbc\7.0.1\help\reference\sqlsupport.12.23.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 xml:lang="en" lang="en" xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <!-- MOTW-DISABLED saved from url=(0014)about:internet --> <title>Select</title> <link rel="StyleSheet" href="css/sqlsupport.css" type="text/css" media="all" /> <link rel="StyleSheet" href="css/webworks.css" type="text/css" media="all" /> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/context.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/towwhdir.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/wwhpagef.js"></script> <script type="text/javascript" language="JavaScript1.2"> <!-- var WebWorksRootPath = ""; // --> </script> <script type="text/javascript" language="JavaScript1.2"> <!-- // Set reference to top level help frame // var WWHFrame = WWHGetWWHFrame("", true); // --> </script> <script type="text/javascript" language="JavaScript1.2" src="scripts/expand.js"></script> </head> <body class="" style="background-color: #FFFFEE;" onload="WWHUpdate();" onunload="WWHUnload();" onkeydown="WWHHandleKeyDown((document.all||document.getElementById||document.layers)?event:null);" onkeypress="WWHHandleKeyPress((document.all||document.getElementById||document.layers)?event:null);" onkeyup="WWHHandleKeyUp((document.all||document.getElementById||document.layers)?event:null);"> <br /> <div class="WebWorks_Breadcrumbs" style="text-align: left;"> <a class="WebWorks_Breadcrumb_Link" href="sqlsupport.12.01.html#107999">10 SQL Statements and Extensions for the Salesforce Driver</a> : Select</div> <hr align="left" /> <blockquote> <div class="head_1"><a name="107999">Select</a></div> <div class="Body"><a name="150056">Use the Select statement to fetch results from one or more tables. The Select statement can </a>operate on local and remote tables in any combination.</div> <div class="gutter_text_right"><a name="150057">Grammar</a></div> <div class="syntax_first"><a name="150058">SELECT </a><span class="EquationVariables">select_clause</span></div> <div class="syntax"><span class="EquationVariables"><a name="150059">from_clause</a></span> </div> <div class="syntax"><a name="150060">[</a><span class="EquationVariables">where_clause</span>] </div> <div class="syntax"><a name="150061">[</a><span class="EquationVariables">groupby_clause</span>] </div> <div class="syntax"><a name="150062">[</a><span class="EquationVariables">having_clause</span>]</div> <div class="syntax"><a name="150063">[{UNION [ALL | DISTINCT] | </a></div> <div class="syntax"><a name="150670"> {MINUS [DISTINCT] | EXCEPT [DISTINCT]} | </a></div> <div class="syntax"><a name="150673"> INTERSECT [DISTINCT]} </a><span class="EquationVariables">select_statement</span>]</div> <div class="syntax"><a name="150067">[</a><span class="EquationVariables">orderby_clause</span>]</div> <div class="syntax"><a name="150068">[</a><span class="EquationVariables">limit_clause</span>]</div> <div class="Body"><a name="150069">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="150070">select_clause</a></span> specifies the columns from which results are to be returned by the query. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.23.html#150114', '');">â??Select Clauseâ??</a></span> for a complete explanation.</div> <div class="Body"><span class="EquationVariables"><a name="150074">from_clause</a></span> specifies one or more tables on which the other clauses in the query operate. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.23.html#150219', '');">â??From Clauseâ??</a></span> for a complete explanation.</div> <div class="Body"><span class="EquationVariables"><a name="150078">where_clause</a></span> is optional and restricts the results that are returned by the query. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.23.html#150264', '');">â??Where Clauseâ??</a></span> for a complete explanation.</div> <div class="Body"><span class="EquationVariables"><a name="150082">groupby_clause</a></span> is optional and allows query results to be aggregated in terms of groups. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.23.html#150284', '');">â??Group By Clauseâ??</a></span> for a complete explanation.</div> <div class="Body"><span class="EquationVariables"><a name="150086">having_clause</a></span> is optional and specifies conditions for groups of rows (for example, display only the departments that have salaries totaling more than $200,000). See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.23.html#150299', '');">â??Having Clauseâ??</a></span> for a complete explanation.</div> <div class="Body"><span class="Syntax"><a name="150090">UNION</a></span> is an optional operator that combines the results of the left and right Select statements into a single result. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.23.html#150314', '');">â??Union Operatorâ??</a></span> for a complete explanation.</div> <div class="Body"><span class="Syntax"><a name="150094">INTERSECT</a></span> is an optional operator that returns a single result by keeping any distinct values from the results of the left and right Select statements. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.23.html#150331', '');">â??Intersect Operatorâ??</a></span> for a complete explanation.</div> <div class="Body"><span class="Syntax"><a name="150098">EXCEPT</a></span> and <span class="Syntax">MINUS</span> are synonymous optional operators that returns a single result by taking the results of the left Select statement and removing the results of the right Select statement. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.23.html#150349', '');">â??Except and Minus Operatorsâ??</a></span> for a complete explanation.</div> <div class="Body"><span class="EquationVariables"><a name="150104">orderby_clause</a></span> is optional and sorts the results that are returned by the query. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.23.html#150400', '');">â??Order By Clauseâ??</a></span> for a complete explanation.</div> <div class="Body"><span class="EquationVariables"><a name="150108">limit_clause</a></span> is optional and places an upper bound on the number of rows returned in the result. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.23.html#150420', '');">â??Limit Clauseâ??</a></span> for a complete explanation.</div> <div class="head_2_no_TOC"><a name="150114">Select Clause</a></div> <div class="Body"><a name="150115">Use the Select clause to specify with a list of column expressions that identify columns of </a>values that you want to retrieve or an asterisk (*) to retrieve the value of all columns.</div> <div class="gutter_text_right"><a name="150116">Grammar</a></div> <div class="syntax_first"><a name="150117">SELECT [{LIMIT </a><span class="EquationVariables">offset</span> <span class="EquationVariables">number</span> | TOP <span class="EquationVariables">number</span>}] [ALL | DISTINCT] {* | <span class="EquationVariables">column_expression</span> [[AS] <span class="EquationVariables">column_alias</span>] [,<span class="EquationVariables">column_expression</span> [[AS] <span class="EquationVariables">column_alias</span>], ...]}</div> <div class="syntax"><a name="150118">[INTO [DISK | TEMP] </a><span class="EquationVariables">new_table</span>]</div> <div class="Body"><a name="150123">where:</a></div> <div class="Body"><span class="Syntax"><a name="150124">LIMIT</a></span><span class="EquationVariables"> offset number</span> creates the result set for the Select statement first and then discards the first number of rows specified by <span class="EquationVariables">offset</span> and returns the number of remaining rows specified by <span class="EquationVariables">number</span>. To not discard any of the rows, specify <span class="Syntax">0</span> for <span class="EquationVariables">offset</span>, for example, <span class="Syntax">LIMIT</span><span class="Syntax"> </span><span class="Syntax">0 </span><span class="EquationVariables">number. </span>To discard the first <span class="EquationVariables">offset</span> number of rows and return all the remaining rows, specify <span class="Syntax">0</span> for <span class="EquationVariables">number</span>, for example, LIMIT <span class="EquationVariables">offset</span> <span class="Syntax">0</span>. </div> <div class="Body"><span class="Syntax"><a name="152124">TOP</a></span> <span class="EquationVariables">number</span> is equivalent to <span class="Syntax">LIMIT 0</span> <span class="EquationVariables">number</span>.</div> <div class="Body"><span class="EquationVariables"><a name="150129">column_expression</a></span> can be simply a column name (for example, <span class="Syntax">last_name</span>). More complex expressions may include mathematical operations or string manipulation (for example, <span class="Syntax">salary * 1.05</span>). See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.27.html#115587', '');">â??SQL Expressionsâ??</a></span> for details. <span class="EquationVariables">column_expression</span> can also include aggregate functions. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.23.html#150143', '');">â??Aggregate Functionsâ??</a></span> for details.</div> <div class="Body"><span class="EquationVariables"><a name="150133">column_alias</a></span> can be used to give the column a descriptive name. For example, to assign the alias department to the column dep:</div> <div class="syntax_first"><a name="150134">SELECT dep AS department FROM emp</a></div> <div class="Body"><a name="150135">Separate multiple column expressions with commas (for example, </a><span class="Syntax">SELECT last_name, </span><span class="Syntax">first_name, hire_date</span>).</div> <div class="Body"><a name="150136">Column names can be prefixed with the table name or table alias. For example, </a><span class="Syntax">SELECT </span><span class="Syntax">emp.last_name or e.last_name</span>, where <span class="Syntax">e</span> is the alias for the table <span class="Syntax">emp</span>.</div> <div class="Body"><a name="150137">The </a><span class="Syntax">DISTINCT</span> operator can precede the first column expression. This operator eliminates duplicate rows from the result of a query. For example:</div> <div class="syntax_first"><a name="150138">SELECT DISTINCT dep FROM emp</a></div> <div class="Body"><a name="150139">NULL values are not treated as distinct from each other. The default behavior is that all </a>result rows be returned, which can be made explicit with the keyword <span class="Syntax">ALL</span>.</div> <div class="Body"><a name="150140">The </a><span class="Syntax">INTO</span> clause copies the result set into <span class="EquationVariables">new_table</span>. <span class="Syntax">INTO DISK </span>creates the new table in cached memory. <span class="Syntax">INTO TEMP</span> creates a temporary table.</div> <div class="head_3"><a name="150143">Aggregate Functions</a></div> <div class="Body"><a name="150144">The result of a query can be the result of one or more aggregate functions. Aggregate </a>functions return a single value from a set of rows. An aggregate can be used with a column name (for example, <span class="Syntax">AVG(salary)</span>) or in combination with a more complex column expression (for example, <span class="Syntax">AVG(salary * 1.07)</span>). The column expression can be preceded by the <span class="Syntax">DISTINCT</span> operator. The <span class="Syntax">DISTINCT</span> operator eliminates duplicate values from an aggregate expression. </div> <div class="Body"><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.23.html#150163', '');" name="150159">Table 10-3</a></span> lists supported aggregate functions.</div> <table class="Format_E" cellspacing="0" summary=""> <caption> <div class="table_title_wide">Table 10-3. <a name="150163">Aggregate Functions</a> </div> </caption> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="150167">Aggregate</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="150169">Returns</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="margin-left: 18pt;"><a name="150171">AVG</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="150173">The average of the values in a numeric column expression. For example, </a><span class="Syntax">AVG(salary)</span> returns the average of all salary column values.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="margin-left: 18pt;"><a name="150175">COUNT</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="150177">The number of values in any column expression. For example, </a><span class="Syntax">COUNT(name)</span> returns the number of name values. When using <span class="Syntax">COUNT</span> with a column name, <span class="Syntax">COUNT</span> returns the number of non-NULL column values. A special example is <span class="Syntax">COUNT(*)</span>, which returns the number of rows in the set, including rows with NULL values.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="margin-left: 18pt;"><a name="150183">MAX</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="150185">The maximum value in any column expression. For example, </a><span class="Syntax">MAX(salary)</span> returns the maximum salary column value.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="margin-left: 18pt;"><a name="150187">MIN</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="150189">The minimum value in any column expression. For example, </a><span class="Syntax">MIN(salary)</span> returns the minimum salary column value.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="margin-left: 18pt;"><a name="150203">SUM</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="150205">The total of the values in a numeric column expression. For example, </a><span class="Syntax">SUM(salary)</span> returns the sum of all salary column values.</div> </td> </tr> </table> <div class="Body"><a name="150216">Except for </a><span class="Syntax">COUNT(*)</span>, all aggregate functions exclude NULL values. The returned value type for <span class="Syntax">COUNT</span> is INTEGER and for <span class="Syntax">MIN</span>, <span class="Syntax">MAX,</span> and <span class="Syntax">AVG</span> it is the same type as the column.</div> <div class="gutter_text_right"><a name="152189">Examples</a></div> <div class="hanging_label_head"><a name="152195">Example A</a></div> <div class="Body"><a name="152225">In the following example, only distinct last name values are counted. The default behavior is </a>that all duplicate values be returned, which can be made explicit with <span class="Syntax">ALL</span>.</div> <div class="syntax_first"><a name="152272">COUNT (DISTINCT last_name)</a></div> <div class="hanging_label_head"><a name="152244">Example B</a></div> <div class="Body"><a name="152338">The following example uses the </a><span class="Syntax">COUNT</span>, <span class="Syntax">MAX</span>, and <span class="Syntax">AVG</span> aggregate functions:</div> <div class="syntax_first"><a name="152339">SELECT </a></div> <div class="syntax"><a name="152340"> COUNT(amount) AS numOpportunities, </a></div> <div class="syntax"><a name="152341"> MAX(amount) AS maxAmount, </a></div> <div class="syntax"><a name="152342"> AVG(amount) AS avgAmount </a></div> <div class="syntax"><a name="152343">FROM opportunity o INNER JOIN user u </a></div> <div class="syntax"><a name="152344"> ON o.ownerId = u.id </a></div> <div class="syntax"><a name="152345">WHERE o.isClosed = 'false' AND </a></div> <div class="syntax"><a name="152336"> u.name = 'MyName'</a></div> <div class="head_2_no_TOC"><a name="150219">From Clause</a></div> <div class="Body"><a name="150220">The From clause indicates the tables to be used in the Select statement.</a></div> <div class="gutter_text_right"><a name="150221">Grammar</a></div> <div class="syntax_first"><a name="150222">FROM </a><span class="EquationVariables">table_name</span> [<span class="EquationVariables">table_alias</span>] [,...]</div> <div class="Body"><a name="150223">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="150224">table_name</a></span> is the name of a table or a subquery. Multiple tables define an implicit inner join among those tables. Multiple table names must be separated by a comma. For example:</div> <div class="syntax_first"><span class="Syntax"><a name="150225">SELECT * FROM emp, dep</a></span> </div> <div class="Body"><a name="150226">Subqueries can be used instead of table names. Subqueries must be enclosed in </a>parentheses. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.23.html#150255', '');">â??Subquery in a From Clauseâ??</a></span> for an example.</div> <div class="Body"><span class="EquationVariables"><a name="150230">table_alias</a></span> is a name used to refer to a table in the rest of the Select statement. When you specify an alias for a table, you can prefix all column names of that table with the table alias. </div> <div class="gutter_text_right"><a name="152661">Example</a></div> <div class="Body"><a name="150231">The following example specifies two table aliases, </a><span class="Syntax">e</span> for <span class="Syntax">emp</span> and <span class="Syntax">d</span> for <span class="Syntax">dep</span>:</div> <div class="syntax_first"><a name="152754">SELECT e.name, d.deptName </a></div> <div class="syntax"><a name="150232">FROM emp e, dep d</a></div> <div class="syntax"><a name="150233">WHERE e.deptId = d.id </a></div> <div class="Body"><a name="150238">The equal sign (=) includes only matching rows in the results.</a></div> <div class="head_4"><a name="150240">Join in a From Clause</a></div> <div class="Body"><a name="150241">You can use a Join as a way to associate multiple tables within a Select statement. Joins </a>may be either explicit or implicit. For example, the following is the example from the previous section restated as an explicit inner join:</div> <div class="syntax_first"><a name="150243">SELECT e.name, d.deptName </a></div> <div class="syntax"><a name="150244">FROM emp e INNER JOIN dep d ON e.deptId = d.id;</a></div> <div class="gutter_text_right"><a name="150247">Grammar</a></div> <div class="syntax_first"><a name="150248">FROM </a><span class="EquationVariables">table_name</span> {RIGHT OUTER | INNER | LEFT OUTER | CROSS} JOIN <span class="EquationVariables">table.key</span> ON <span class="EquationVariables">search-condition</span></div> <div class="gutter_text_right"><a name="150249">Example</a></div> <div class="Body"><a name="150250">In this example, two tables are joined using </a><span class="Syntax">LEFT OUTER JOIN</span>. <span class="Syntax">T1</span>, the first table named includes nonmatching rows.</div> <div class="syntax_first"><a name="150251">SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.key = T2.key</a></div> <div class="Body"><a name="150252">If you use a </a><span class="Syntax">CROSS JOIN</span>, no <span class="Syntax">ON</span> expression is allowed for the join.</div> <div class="head_4"><a name="150255">Subquery in a From Clause</a></div> <div class="Body"><a name="150256">Subqueries can be used in the From clause in place of table references (</a><span class="EquationVariables">table_name</span>). For example:</div> <div class="syntax_first"><a name="150257">SELECT * FROM (SELECT * FROM emp WHERE sal > 10000) new_emp, dept WHERE </a>new_emp.deptno = dept.deptno</div> <div class="Body"><a name="150260">For more information about subqueries, see </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.28.html#94298', '');">â??Subqueriesâ??</a></span>.</div> <div class="head_3"><a name="150264">Where Clause</a></div> <div class="Body"><a name="150265">The Where clause specifies the conditions that rows must meet to be retrieved. </a></div> <div class="gutter_text_right"><a name="150266">Grammar</a></div> <div class="syntax_first"><a name="150267">WHERE </a><span class="EquationVariables">expr1</span> <span class="EquationVariables">rel_operator</span> <span class="EquationVariables">expr2</span></div> <div class="Body"><a name="150268">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="150269">expr1</a></span> is either a column name, literal, or expression.</div> <div class="Body"><span class="EquationVariables"><a name="150270">expr2</a></span> is either a column name, literal, expression, or subquery. Subqueries must be enclosed in parentheses. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.28.html#94298', '');">â??Subqueriesâ??</a></span> for complete information about subqueries.</div> <div class="Body"><span class="EquationVariables"><a name="150274">rel_operator</a></span> is the relational operator that links the two expressions. </div> <div class="Body"><a name="152820">See </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.27.html#115587', '');">â??SQL Expressionsâ??</a></span> for details about expressions.</div> <div class="gutter_text_right"><a name="152821">Example</a></div> <div class="Body"><a name="152822">The following Select statement retrieves the first and last names of employees that make at </a>least $20,000.</div> <div class="syntax_first"><a name="150281">SELECT last_name, first_name FROM emp WHERE salary >= 20000</a></div> <div class="head_3"><a name="150284">Group By Clause</a></div> <div class="Body"><a name="150285">The Group By clause specifies the names of one or more columns by which the returned </a>values are grouped. This clause is used to return a set of aggregate values.</div> <div class="gutter_text_right"><a name="150286">Grammar</a></div> <div class="syntax_first"><a name="150287">GROUP BY </a><span class="EquationVariables">column_expression </span>[,...]</div> <div class="Body"><a name="150288">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="150289">column_expression</a></span> is either a column name or a SQL expression (see <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.27.html#115587', '');">â??SQL Expressionsâ??</a></span> for details). Multiple values must be separated by a comma. If <span class="Emphasis">column_expression</span> is a column name, it must match one of the column names specified in the Select clause. Also, the Group By clause must include all non-aggregate columns specified in the Select list.</div> <div class="gutter_text_right"><a name="150293">Example</a></div> <div class="Body"><a name="150294">The following example totals the salaries in each department:</a></div> <div class="syntax_first"><a name="150295">SELECT dept_id, sum(salary) FROM emp GROUP BY dept_id</a></div> <div class="Body"><a name="150296">This statement returns one row for each distinct department ID. Each row contains the </a>department ID and the sum of the salaries of the employees in the department.</div> <div class="head_3"><a name="150299">Having Clause</a></div> <div class="Body"><a name="150300">The Having clause specifies conditions for groups of rows (for example, display only the </a>departments that have salaries totaling more than $200,000). This clause is valid only if you have already defined a Group By clause. </div> <div class="gutter_text_right"><a name="150301">Grammar</a></div> <div class="syntax_first"><a name="150302">HAVING </a><span class="EquationVariables">expr1</span> <span class="EquationVariables">rel_operator</span> <span class="EquationVariables">expr2</span></div> <div class="Body"><a name="150303">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="150304">expr1</a></span> and <span class="EquationVariables">expr2</span> can be column names, constant values, or expressions. These expressions do not have to match a column expression in the Select clause. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.27.html#115587', '');">â??SQL Expressionsâ??</a></span> for details regarding SQL expressions.</div> <div class="Body"><span class="EquationVariables"><a name="150308">rel_operator</a></span> is the relational operator that links the two expressions. </div> <div class="gutter_text_right"><a name="150309">Example</a></div> <div class="Body"><a name="152829">The following example returns only the departments that have salaries totaling more than </a>$200,000:</div> <div class="syntax_first"><a name="150310">SELECT dept_id, sum(salary) FROM emp</a></div> <div class="syntax"><a name="150311">GROUP BY dept_id HAVING sum(salary) > 200000</a></div> <div class="head_3"><a name="150314">Union Operator</a></div> <div class="Body"><a name="150315">The Union operator combines the results of two Select statements into a single result. The </a>single result is all the returned rows from both Select statements. By default, duplicate rows are not returned. To return duplicate rows, use the All keyword (<span class="Syntax">UNION ALL</span>).</div> <div class="gutter_text_right"><a name="150316">Grammar</a></div> <div class="syntax_first" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-transform: none; vertical-align: baseline;"><span class="EquationVariables"><a name="150317">select_statement</a></span> </div> <div class="syntax"><a name="150318">UNION [ALL | DISTINCT] | {MINUS [DISTINCT] | EXCEPT [DISTINCT]} | INTERSECT </a>[DISTINCT]</div> <div class="syntax" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-transform: none; vertical-align: baseline;"><span class="EquationVariables"><a name="150319">select_statement</a></span> </div> <div class="Body"><a name="150320">When using the Union operator, the Select lists for each Select statement must have the </a>same number of column expressions with the same data types and must be specified in the same order. </div> <div class="gutter_text_right"><a name="150321">Examples</a></div> <div class="hanging_label_head"><a name="150322">Example A</a></div> <div class="Body"><a name="151696">The following example has the same number of column expressions, and each column </a>expression, in order, has the same data type.</div> <div class="syntax_first"><a name="150323">SELECT last_name, salary, hire_date FROM emp</a></div> <div class="syntax"><a name="150324">UNION</a></div> <div class="syntax"><a name="150325">SELECT name, pay, birth_date FROM person</a></div> <div class="hanging_label_head"><a name="150326">Example B</a></div> <div class="Body"><a name="151700">The following example is </a><span class="Emphasis">not</span> valid because the data types of the column expressions are different (<span class="Syntax">salary FROM emp</span> has a different data type than <span class="Syntax">last_name FROM raises</span>). This example does have the same number of column expressions in each Select statement but the expressions are not in the same order by data type.</div> <div class="syntax_first"><a name="150327">SELECT last_name, salary FROM emp</a></div> <div class="syntax"><a name="150328">UNION</a></div> <div class="syntax"><a name="150329">SELECT salary, last_name FROM raises</a></div> <div class="head_3"><a name="150331">Intersect Operator</a></div> <div class="Body"><a name="150332">Intersect operator returns a single result set. The result set contains rows that are returned </a>by both Select statements. Duplicates are returned unless the Distinct operator is added.</div> <div class="gutter_text_right"><a name="150333">Grammar</a></div> <div class="syntax_first"><span class="EquationVariables"><a name="150334">select_statement</a></span> </div> <div class="syntax"><a name="150335">INTERSECT [DISTINCT]</a></div> <div class="syntax" style="color: #000000; font-style: italic; font-variant: normal; font-weight: normal; text-transform: none; vertical-align: baseline;"><span class="EquationVariables"><a name="150336">select_statement</a></span><span class="EquationVariables" style="font-style: normal;"> </span></div> <div class="Body"><a name="150337">The Distinct operator eliminates duplicate rows from the results.</a></div> <div class="Body"><a name="150338">When using the Intersect operator, the Select lists for each Select statement must have the </a>same number of column expressions with the same data types and must be specified in the same order. </div> <div class="gutter_text_right"><a name="150339">Examples</a></div> <div class="hanging_label_head"><a name="150340">Example A</a></div> <div class="Body"><a name="151736">The following example has the same number of column expressions, and each column </a>expression, in order, has the same data type.</div> <div class="syntax_first"><a name="150341">SELECT last_name, salary, hire_date FROM emp</a></div> <div class="syntax"><a name="150342">INTERSECT [DISTINCT]</a></div> <div class="syntax"><a name="150343">SELECT name, pay, birth_date FROM person</a></div> <div class="hanging_label_head"><a name="150344">Example B</a></div> <div class="Body"><a name="151740">The following example is </a><span class="Emphasis">not</span> valid because the data types of the column expressions are different (<span class="Syntax">salary FROM emp</span> has a different data type than <span class="Syntax">last_name FROM raises</span>). This example does have the same number of column expressions in each Select statement but the expressions are not in the same order by data type.</div> <div class="syntax_first"><a name="150345">SELECT last_name, salary FROM emp</a></div> <div class="syntax"><a name="150346">INTERSECT</a></div> <div class="syntax"><a name="150347">SELECT salary, last_name FROM raises</a></div> <div class="head_3"><a name="150349">Except and Minus Operators</a></div> <div class="Body"><a name="150350">The Except and Minus are synonymous operators that return the rows from the left Select </a>statement that are not included in the result of the right Select statement. </div> <div class="gutter_text_right"><a name="150351">Grammar</a></div> <div class="syntax_first"><span class="EquationVariables"><a name="150352">select_statement</a></span> </div> <div class="syntax"><a name="150353">{EXCEPT [DISTINCT] | MINUS [DISTINCT]}</a></div> <div class="syntax" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-transform: none; vertical-align: baseline;"><span class="EquationVariables"><a name="150354">select_statement</a></span><span class="EquationVariables" style="font-style: normal;"> </span></div> <div class="Body"><a name="150355">The </a><span class="Syntax">DISTINCT</span> operator eliminates duplicate rows from the results.</div> <div class="Body"><a name="150356">When using one of these operators, the Select lists for each Select statement must have the </a>same number of column expressions with the same data types and must be specified in the same order. </div> <div class="gutter_text_right"><a name="150357">Examples</a></div> <div class="hanging_label_head"><a name="150358">Example A</a></div> <div class="Body"><a name="151776">The following example has the same number of column expressions, and each column </a>expression, in order, has the same data type.</div> <div class="syntax_first"><a name="150359">SELECT last_name, salary, hire_date FROM emp</a></div> <div class="syntax"><a name="150360">EXCEPT</a></div> <div class="syntax"><a name="150361">SELECT name, pay, birth_date FROM person</a></div> <div class="hanging_label_head"><a name="150362">Example B</a></div> <div class="Body"><a name="151780">The following example is </a><span class="Emphasis">not</span> valid because the data types of the column expressions are different (<span class="Syntax">salary FROM emp</span> has a different data type than <span class="Syntax">last_name FROM raises</span>). This example does have the same number of column expressions in each Select statement but the expressions are not in the same order by data type.</div> <div class="syntax_first"><a name="150363">SELECT last_name, salary FROM emp</a></div> <div class="syntax"><a name="150364">EXCEPT</a></div> <div class="syntax"><a name="150365">SELECT salary, last_name FROM raises</a></div> <div class="head_3"><a name="150400">Order By Clause</a></div> <div class="Body"><a name="150401">The Order By clause specifies how the rows are to be sorted. </a></div> <div class="gutter_text_right"><a name="150402">Grammar</a></div> <div class="syntax_first"><a name="150403">ORDER BY </a><span class="EquationVariables">sort_expression</span> [DESC | ASC] [,...]</div> <div class="Body"><a name="150404">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="150405">sort_expression</a></span> is either the name of a column, a column alias, a SQL expression, or the positioned number of the column or expression in the select list to use. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.27.html#115587', '');">â??SQL Expressionsâ??</a></span> for details regarding SQL expressions.</div> <div class="Body"><a name="150409">The default is to perform an ascending (</a><span class="Syntax">ASC</span>) sort.</div> <div class="gutter_text_right"><a name="150410">Example</a></div> <div class="Body"><a name="150411">For example, to sort by </a><span class="Syntax">last_name</span> and then by <span class="Syntax">first_name</span>, you could use either of the following Select statements:</div> <div class="syntax_first"><a name="150412">SELECT emp_id, last_name, first_name FROM emp </a></div> <div class="syntax"><a name="150413">ORDER BY last_name, first_name</a></div> <div class="Body"><a name="150414">or</a></div> <div class="syntax_first"><a name="150415">SELECT emp_id, last_name, first_name FROM emp </a></div> <div class="syntax"><a name="150416">ORDER BY 2,3</a></div> <div class="Body"><a name="150417">In the second example, </a><span class="Syntax">last_name</span> is the second item in the Select list, so <span class="Syntax">ORDER BY 2,3</span> sorts by <span class="Syntax">last_name</span> and then by <span class="Syntax">first_name</span>.</div> <div class="head_3"><a name="150420">Limit Clause</a></div> <div class="Body"><a name="150421">The Limit clause places an upper bound on the number of rows returned in the result.</a></div> <div class="gutter_text_right"><a name="150422">Grammar</a></div> <div class="syntax_first"><a name="150423">LIMIT </a><span class="EquationVariables">number_of_rows </span>[OFFSET <span class="EquationVariables">offset_number</span>]</div> <div class="Body"><a name="150424">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="150425">number_of_rows</a></span> specifies a maximum number of rows in the result. A negative number indicates no upper bound. </div> <div class="Body"><a name="150426">The </a><span class="Syntax">OFFSET</span> operator specifies how many rows to skip at the beginning of the result set. <span class="EquationVariables">offset_number</span> is the number of rows to skip. </div> <div class="Body"><a name="150427">In a compound query, the Limit clause can appear only on the final Select statement. The </a>limit is applied to the entire query, not to the individual Select statement to which it is attached.</div> <div class="gutter_text_right"><a name="150428">Example</a></div> <div class="Body"><a name="150429">The following example returns a maximum of 20 rows.</a></div> <div class="syntax_first"><a name="151861">SELECT last_name, first_name FROM emp WHERE salary > 20000 ORDER BY dept_id </a>LIMIT 20</div> <script type="text/javascript" language="JavaScript1.2"> <!-- // Clear related topics // WWHClearRelatedTopics(); document.writeln(WWHRelatedTopicsInlineHTML()); // --> </script> </blockquote> <script type="text/javascript" language="JavaScript1.2"> <!-- document.write(WWHRelatedTopicsDivTag() + WWHPopupDivTag() + WWHALinksDivTag()); // --> </script> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de