| <html> |
| <head> |
| <META http-equiv="Content-Type" content="text/html; charset=UTF-8"> |
| <title>Chapter 7. Data Access and Change</title> |
| <link href="../docbook.css" rel="stylesheet" type="text/css"> |
| <meta content="DocBook XSL-NS Stylesheets V1.74.0" name="generator"> |
| <meta name="keywords" content="Hsqldb, HyperSQL, SQL"> |
| <meta name="keywords" content="Hsqldb, HyperSQL, Database, JDBC, Java"> |
| <link rel="home" href="index.html" title="HyperSQL User Guide"> |
| <link rel="up" href="index.html" title="HyperSQL User Guide"> |
| <link rel="prev" href="accesscontrol-chapt.html" title="Chapter 6. Access Control"> |
| <link rel="next" href="sqlroutines-chapt.html" title="Chapter 8. SQL-Invoked Routines"> |
| </head> |
| <body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"> |
| <div class="navheader"> |
| <table summary="Navigation header" width="100%"> |
| <tr> |
| <td align="left" width="30%"><a accesskey="p" href="accesscontrol-chapt.html"><img src="../images/db/prev.png" alt="Prev"></a> </td><td align="center" width="40%" style="font-weight:bold;">Chapter 7. Data Access and Change</td><td align="right" width="30%"> <a accesskey="n" href="sqlroutines-chapt.html"><img src="../images/db/next.png" alt="Next"></a></td> |
| </tr> |
| <tr> |
| <td valign="top" align="left" width="30%">Chapter 6. Access Control </td><td align="center" width="40%"><a accesskey="h" href="index.html"><img src="../images/db/home.png" alt="Home"></a></td><td valign="top" align="right" width="30%"> Chapter 8. SQL-Invoked Routines</td> |
| </tr> |
| </table> |
| </div> |
| <HR> |
| <div class="chapter" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title"> |
| <a name="dataaccess-chapt"></a>Chapter 7. Data Access and Change</h2> |
| </div> |
| <div> |
| <div class="authorgroup"> |
| <div class="author"> |
| <h3 class="author"> |
| <span class="firstname">Fred</span> <span class="surname">Toussi</span> |
| </h3> |
| <div class="affiliation"> |
| <span class="orgname">The HSQL Development Group<br> |
| </span> |
| </div> |
| </div> |
| </div> |
| </div> |
| <div> |
| <p class="releaseinfo">$Revision: 3601 $</p> |
| </div> |
| <div> |
| <div class="legalnotice"> |
| <a name="N11998"></a> |
| <p>Copyright 2010 Fred Toussi. Permission is granted to distribute |
| this document without any alteration under the terms of the HSQLDB |
| license. Additional permission is granted to the HSQL Development Group |
| to distribute this document with or without alterations under the terms |
| of the HSQLDB license.</p> |
| </div> |
| </div> |
| <div> |
| <p class="pubdate">$Date: 2010-05-31 20:17:47 -0400 (Mon, 31 May 2010) $</p> |
| </div> |
| </div> |
| </div> |
| <div class="toc"> |
| <p> |
| <b>Table of Contents</b> |
| </p> |
| <dl> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N1199B">Overview</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N119A0">Cursors And Result Sets</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N119BD">Columns and Rows</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N119D8">Navigation</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N119EA">Updatability</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N11A28">Sensitivity</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N11A31">Holdability</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N11A44">Autocommit</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N11A4C">JDBC Overview</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N11A59">JDBC Parameters</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N11A68">JDBC Returned Values</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N11A71">Syntax Elements</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N11A76">Literals</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N11B9C">References, etc.</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N11BF8">Value Expression</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N11E1B">Predicates</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N1205E">Other Syntax Elements</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N12147">Data Access Statements</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N1217F">Table</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N12199">Query Specification</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N121DF">Table Expression</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N12246">Table Primary</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N12278">Joined Table</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N122E5">Selection</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N122EA">Projection</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N122F1">Computed Columns</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N122F6">Naming</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N12338">Grouping Operations</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N12346">Aggregation</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N1234D">Set Operations</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N12371">Query Expression</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N123B0">Ordering</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N123C8">Slicing</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N123DA">Data Change Statements</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N123DD">Delete Statement</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N123FD">Truncate Statement</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N1241F">Insert Statement</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N1246A">Update Statement</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="dataaccess-chapt.html#N124B8">Merge Statement</a></span> |
| </dt> |
| </dl> |
| </dd> |
| </dl> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="N1199B"></a>Overview</h2> |
| </div> |
| </div> |
| </div> |
| <p>HyperSQL data access and data change statements are fully compatible |
| with the latest SQL:2008 Standard. There are a few extensions and some |
| relaxation of rules, but these do not affect statements that are written |
| to the Standard syntax. There is full support for classic SQL, as |
| specified by SQL-92, and many enhancements added in later versions of the |
| standard.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="N119A0"></a>Cursors And Result Sets</h2> |
| </div> |
| </div> |
| </div> |
| <p>An SQL statement can executed in two ways. One way is to use the |
| <code class="classname">java.sql.Statement</code> interface. The Statement object |
| can be reused to execute completely different SQL statements. |
| Alternatively a <code class="classname">PreparedStatment</code> can be used to |
| execute an SQL statement repeatedly, and the statements can be |
| parameterized. Using either form, if the SQL statement is a query |
| expression, a <code class="classname">ResultSet</code> is returned.</p> |
| <p>In SQL, when a query expression (SELECT or similar SQL statement) is |
| executed, an ephemeral table is created. When this table is returned to |
| the application program, it is returned as a result set, which is accessed |
| row-by-row by a cursor. A JDBC <code class="classname">ResultSet</code> represents |
| an SQL result set and its cursor.</p> |
| <p>The minimal definition of a cursor is a list of rows with a position |
| that can move forward. Some cursors also allow the position to move |
| backwards or jump to any position in the list.</p> |
| <p>An SQL cursor has several attributes. These attributes depend on the |
| query expression. Some of these attributes can be overridden by specifying |
| qualifiers in the SQL statement or by specifying values for the parameters |
| of the JDBC <code class="classname">Statement</code> or |
| <code class="classname">PreparedStatement</code>.</p> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N119BD"></a>Columns and Rows</h3> |
| </div> |
| </div> |
| </div> |
| <p>The columns of the rows of the result set are determined by the |
| query expression. The number of columns and the type and name |
| characteristics of each column are known when the query expression is |
| compiled and before its execution. This metadata information remains |
| constant regardless of changes to the contents of the tables used in the |
| query expression. The metadata for the JDBC |
| <code class="classname">ResultSet</code> is in the form of a |
| <code class="classname">ResultSetMetaData</code> object. Various methods of the |
| <code class="classname">ResultSetMetaData</code> interface return different |
| properties of each column of the |
| <code class="classname">ResultSet</code>.</p> |
| <p>A result set may contain 0 or more rows. The rows are determined |
| by the execution of the query expression.</p> |
| <p>The <code class="methodname">setMaxRows(int)</code> method of JDBC |
| <code class="classname">Statement</code> allows limiting the number of rows |
| returned by the statement. This limit is conceptually applied after the |
| result has been built, and the excess rows are discarded.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N119D8"></a>Navigation</h3> |
| </div> |
| </div> |
| </div> |
| <p>A cursor is either scrollable or not. Scrollable cursors allow |
| accessing rows by absolute or relative positioning. No-scroll cursors |
| only allow moving to the next row. The cursor can be optionally declared |
| with the SQL qualifiers SCROLL, or NO SCROLL. The JDBC statement |
| parameter can be specified as: TYPE_FORWARD_ONLY and |
| TYPE_SCROLL_INSENSITIVE. The JDBC type TYPE_SCROLL_SENSITIVE is not |
| supported by HSQLDB.</p> |
| <p>The default is NO SCROLL or TYPE_FORWARD_ONLY.</p> |
| <p>When a JDBC <code class="classname">ResultSet</code> is opened, it is |
| positioned before the first row. Using the |
| <code class="methodname">next()</code> method the position is moved to the |
| first row. While the <code class="classname">ResultSet</code> is positioned on a |
| row, various getter methods can be used to access the columns of the |
| row.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N119EA"></a>Updatability</h3> |
| </div> |
| </div> |
| </div> |
| <p>The result returned by some query expressions is updatable. HSQLDB |
| supports core SQL updatability features, plus some enhancements from the |
| SQL optional features.</p> |
| <p>A query expression is updatable if it is a SELECT from a single |
| underlying base table (or updatable view) either directly or indirectly. |
| A SELECT statement featuring DISTINCT or GROUP BY or FETCH, LIMIT, |
| OFFSET is not updatable. In an updatable query expression, one or more |
| columns are updatable. An updatable column is a column that can be |
| traced directly to the underlying table. Therefore, columns that contain |
| expressions are not updatable. Examples of updatable query expressions |
| are given below. The view V is updatable when its query expression is |
| updatable. The SELECT statement from this view is also updatable:</p> |
| <pre class="programlisting">SELECT A, B FROM T WHERE C > 5 |
| SELECT A, B FROM (SELECT * FROM T WHERE C > 10) AS TT WHERE TT.B <10 |
| CREATE VIEW V(X,Y) AS SELECT A, B FROM T WHERE C > 0 AND B < 10 |
| SELECT X FROM V WHERE Y = 5 |
| </pre> |
| <p>If a cursor is declared with the SQL qualifier, <code class="literal">FOR |
| UPDATE OF <column name list></code>, then only the stated |
| columns in the result set become updatable. If any of the stated columns |
| is not actually updatable, then the cursor declaration will not |
| succeed.</p> |
| <p>If the SQL qualifier, FOR UPDATE is used, then all the updatable |
| columns of the result set become updatable.</p> |
| <p>If a cursor is declared with FOR READ ONLY, then it is not |
| updatable.</p> |
| <p>In HSQLDB, if FOR READ ONLY or FOR UPDATE is not used then all the |
| updatable columns of the result set become updatable. This relaxes the |
| SQL standard rule that in this case limits updatability to only simply |
| updatable SELECT statements (where all columns are updatable).</p> |
| <p>In JDBC, CONCUR_READ_ONLY or CONCUR_UPDATABLE can be specified for |
| the <code class="classname">Statement</code> parameter. CONCUR_UPDATABLE is |
| required if the returning ResultSet is to be updatable. If |
| CONCUR_READ_ONLY, which is the default, is used, then even an updatable |
| ResultSet becomes read-only.</p> |
| <p>When a <code class="classname">ResultSet</code> is updatable, various |
| setter methods can be used to modify the column values. The names of the |
| setter methods begin with "update". After all the updates on a row are |
| done, the <code class="methodname">updateRow()</code> method must be called to |
| finalise the row update.</p> |
| <p>An updatable <code class="classname">ResultSet</code> may or may not be |
| insertable-into. In an insertable <code class="classname">ResultSet</code>, all |
| columns of the result are updatable and any column of the base table |
| that is not in the result must be a generated column or have a default |
| value.</p> |
| <p>In the <code class="classname">ResultSet</code> object, a special |
| pseudo-row, called the insert row, is used to populate values for |
| insertion into the <code class="classname">ResultSet</code> (and consequently, |
| into the base table). The setter methods must be used on all the |
| columns, followed by a call to |
| <code class="methodname">insertRow()</code>.</p> |
| <p>Individual rows from all updatable result sets can be deleted one |
| at a time. The <code class="methodname">deleteRow()</code> is called when the |
| <code class="classname">ResultSet</code> is positioned on a row.</p> |
| <p>While using an updatable ResultSet to modify data, it is |
| recommended not to change the same data using another ResultSet and not |
| to execute SQL data change statements that modify the same data.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N11A28"></a>Sensitivity</h3> |
| </div> |
| </div> |
| </div> |
| <p>The sensitivity of the cursor relates to visibility of changes |
| made to the data by the same transaction but without using the given |
| cursor. While the result set is open, the same transaction may use |
| statements such as INSERT or UPDATE, and change the data of the tables |
| from which the result set data is derived. A cursor is SENSITIVE if it |
| reflects those changes. It is INSENSITIVE if it ignores such changes. It |
| is ASENSITIVE if behaviour is implementation dependent.</p> |
| <p>The SQL default is ASENSITIVE, i.e., implantation |
| dependent.</p> |
| <p>In HSQLDB all cursors are INSENSITIVE. They do not reflect changes |
| to the data made by other statements.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N11A31"></a>Holdability</h3> |
| </div> |
| </div> |
| </div> |
| <p>A cursor is holdable if the result set is not automatically closed |
| when the current transaction is committed. Holdability can be specified |
| in the cursor declaration using the SQL qualifiers WITH HOLD or WITHOUT |
| HOLD.</p> |
| <p>In JDBC, hodability is specified using either of the following |
| values for the Statement parameter: HOLD_CURSORS_OVER_COMMIT, or |
| CLOSE_CURSORS_AT_COMMIT.</p> |
| <p>The SQL default is WITHOUT HOLD.</p> |
| <p>The JDBC default for HSQLDB result sets is WITH HOLD for read-only |
| result sets and WITHOUT HOLD for updatable result sets.</p> |
| <p>If the holdability of a <code class="classname">ResultSet</code> is |
| specified in a conflicting manner in the SQL statement and the JDBC |
| <code class="classname">Statement</code> object, the JDBC setting takes |
| precedence.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N11A44"></a>Autocommit</h3> |
| </div> |
| </div> |
| </div> |
| <p>The autocommit property of a connection is a feature of JDBC and |
| ODBC and is not part of the SQL Standard. In autocommit mode, all |
| transactional statements are followed by an implicit commit. In |
| autocommit mode, all <code class="classname">ResultSet</code> objects are |
| read-only and holdable.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N11A4C"></a>JDBC Overview</h3> |
| </div> |
| </div> |
| </div> |
| <p>The JDBC settings, ResultSet.CONCUR_READONLY and |
| ResultSet.CONCUR_UPDATABLE are the alternatives for read-only or |
| updatability. The default is ResultSet.CONCUR_READONLY.</p> |
| <p>The JDBC settings, ResultSet.TYPE_FORWARD_ONLY, |
| ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.TYPE_SCROLL_SENSITIVE are |
| the alternatives for both scrollability (navigation) and sensitivity. |
| HyperSQL does not support ResultSet.TYPE_SCROLL_SENSITIVE. The two other |
| alternatives can be used for both updatable and read-only result |
| sets.</p> |
| <p>The JDBC settings ResultSet.CLOSE_CURSORS_AT_COMMIT and |
| ResultSet.HOLD_CURSORS_OVER_COMMIT are the alternatives for the lifetime |
| of the result set. The default is ResultSet.CLOSE_CURSORS_AT_COMMIT. The |
| other setting can only be used for read-only result sets.</p> |
| <p>Examples of creating statements for updatable result sets are |
| given below:</p> |
| <pre class="programlisting">Connection c = newConnection(); |
| Statement st; |
| c.setAutoCommit(false); |
| st = c.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); |
| st = c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);</pre> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N11A59"></a>JDBC Parameters</h3> |
| </div> |
| </div> |
| </div> |
| <p>When a JDBC PreparedStatement or CallableStatement is used with an |
| SQL statement that contains dynamic parameters, the data types of the |
| parameters are resolved and determined by the engine when the statement |
| is prepared. The SQL Standard has detailed rules to determine the data |
| types and imposes limits on the maximum length or precision of the |
| parameter. HyperSQL applies the standard rules with two exceptions for |
| parameters with String and BigDecimal Java types. HyperSQL ignores the |
| limits when the parameter value is set, and only enforces the necessary |
| limits when the PreparedStatement is executed. In all other cases, |
| parameter type limits are checked and enforce when the parameter is |
| set.</p> |
| <p>In the example below the setString() calls do not raise an |
| exception, but one of the execute() statements does.</p> |
| <pre class="programlisting">// table definition: CREATE TABLE T (NAME VARCHAR(12), ...) |
| Connection c = newConnection(); |
| PreparedStatement st = c.prepareStatement("SELECT * FROM T WHERE NAME = ?"); |
| // type of the parameter is VARCHAR(12), which limits length to 12 characters |
| st.setString(1, "Eyjafjallajokull"); // string is longer than type, but no exception is raised here |
| set.execute(); // executes with no exception and does not find any rows |
| // but if an UPDATE is attempted, an exception is raised |
| st = c.prepareStatement("UPDATE T SET NAME = ? WHERE ID = 10"); |
| st.setString(1, "Eyjafjallajokull"); // string is longer than type, but no exception is raised here |
| st.execute(); // exception is thrown when HyperSQL checks the value for update |
| |
| </pre> |
| <p>All of the above also applies to setting the values in new and |
| updated rows in updatable ResultSet objects.</p> |
| <p>JDBC parameters can be set with any compatible type, as supported |
| by the JDBC specification. For CLOB and BLOB types, you can use streams, |
| or create instances of BLOB or CLOB before assigning them to the |
| parameters. You can even use CLOB or BLOB objects returned from |
| connections to other RDBMS servers. The Connection.createBlob() and |
| createClob() methods can be used to create the new LOBs. For very large |
| LOB's the stream methods are preferable as they use less memory.</p> |
| <p>For array parameters, you must use a java.sql.Array object that |
| contains the array elements before assigning to JDBC parameters. The |
| Connection.createArrayOf(...) method can be used to create a new object, |
| or you can use an Array returned from connections to other RDBMS |
| servers.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N11A68"></a>JDBC Returned Values</h3> |
| </div> |
| </div> |
| </div> |
| <p>The methods of the JDBC ResultSet interface can be used to return |
| values and to convert value to different types as supported by the JDBC |
| specification.</p> |
| <p>When a CLOB and BLOB object is returned from a ResultSet, no data |
| is transferred until the data is read by various methods of |
| java.sql.CLOB and java.sql.BLOB. Data is streamed in large blocks to |
| avoid excessive memory use.</p> |
| <p>Array objects are returned as instances of java.sql.Array.</p> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="N11A71"></a>Syntax Elements</h2> |
| </div> |
| </div> |
| </div> |
| <p>The syntax elements that can be used in data access and data change |
| statements are described in this section. The SQL Standard has a very |
| extensive set of definitions for these elements. The BNF definitions given |
| here are sometimes simplified.</p> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N11A76"></a>Literals</h3> |
| </div> |
| </div> |
| </div> |
| <p>Literals are used to express constant values. The general type of |
| a literal is known by its format. The specific type is based on |
| conventions.</p> |
| <a name="N11A7B" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>unicode escape |
| elements</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>unicode escape elements</em></span> |
| </p> |
| <p> |
| <code class="literal"><Unicode escape specifier> ::= [ UESCAPE |
| <quote><Unicode escape character><quote> ] |
| </code> |
| </p> |
| <p> |
| <code class="literal"><Unicode escape value> ::= <Unicode 4 digit |
| escape value> | <Unicode 6 digit escape value> | <Unicode |
| character escape value></code> |
| </p> |
| <p> |
| <code class="literal"><Unicode 4 digit escape value> ::= <Unicode |
| escape |
| character><hexit><hexit><hexit><hexit></code> |
| </p> |
| <p> |
| <code class="literal"><Unicode 6 digit escape value> ::= <Unicode |
| escape character><plus sign> |
| <hexit><hexit><hexit><hexit><hexit><hexit></code> |
| </p> |
| <p> |
| <code class="literal"><Unicode character escape value> ::= <Unicode |
| escape character><Unicode escape character></code> |
| </p> |
| <p> |
| <code class="literal"><Unicode escape character> ::= a single |
| character than a <hexit> (a-f, A-F, 0-9), <plus sign>, |
| <quote>, <double quote>, or <white |
| space></code> |
| </p> |
| <a name="N11A99" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>character literal</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>character literal</em></span> |
| </p> |
| <p> |
| <code class="literal"><character string literal> ::= [ |
| <introducer><character set specification> ] <quote> [ |
| <character representation>... ] <quote> [ { |
| <separator> <quote> [ <character representation>... ] |
| <quote> }... ]</code> |
| </p> |
| <p> |
| <code class="literal"><introducer> ::= |
| <underscore></code> |
| </p> |
| <p> |
| <code class="literal"><character representation> ::= <nonquote |
| character> | <quote symbol></code> |
| </p> |
| <p> |
| <code class="literal"><nonquote character> ::= any character apart |
| from the quote symbol.</code> |
| </p> |
| <p> |
| <code class="literal"><quote symbol> ::= |
| <quote><quote></code> |
| </p> |
| <p> |
| <code class="literal"><national character string literal> ::= N |
| <quote> [ <character representation>... ] <quote> [ { |
| <separator> <quote> [ <character representation>... ] |
| <quote> }... ]</code> |
| </p> |
| <p> |
| <code class="literal"><Unicode character string literal> ::= [ |
| <introducer><character set specification> ] |
| U<ampersand><quote> [ <Unicode representation>... ] |
| <quote> [ { <separator> <quote> [ <Unicode |
| representation>... ] <quote> }... ] <Unicode escape |
| specifier></code> |
| </p> |
| <p> |
| <code class="literal"><Unicode representation> ::= <character |
| representation> | <Unicode escape value></code> |
| </p> |
| <p>The type of a character literal is CHARACTER. The length of the |
| string literal is the character length of the type. If the quote |
| character is used in a string, it is represented with two quote |
| characters. Long literals can be divided into multiple quoted strings, |
| separated with a space or end-of-line character.</p> |
| <p>Unicode literals start with U& and can contain ordinary |
| characters and unicode escapes. A unicode escape begins with the |
| backslash ( \ ) character and is followed by four hexadecimal characters |
| which specify the character code.</p> |
| <p>Example of character literals are given below:</p> |
| <pre class="programlisting">'a literal' ' string seperated' ' into parts' |
| 'a string''s literal form with quote character' |
| U&'Unicode string with Greek delta \0394 and phi \03a6 letters' |
| </pre> |
| <a name="N11AC5" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>binary literal</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>binary literal</em></span> |
| </p> |
| <p> |
| <code class="literal"><binary string literal> ::= X <quote> [ |
| <space>... ] [ { <hexit> [ <space>... ] <hexit> |
| [ <space>... ] }... ] <quote> [ { <separator> |
| <quote> [ <space>... ] [ { <hexit> [ <space>... |
| ] <hexit> [ <space>... ] }... ] <quote> }... |
| ]</code> |
| </p> |
| <p> |
| <code class="literal"><hexit> ::= <digit> | A | B | C | D | E | |
| F | a | b | c | d | e | f</code> |
| </p> |
| <p>The type of a binary literal is BINARY. The octect length of |
| the binary literal is the length of the type. Case-insensitive |
| hexadecimal characters are used in the binary string. Each pair of |
| characters in the literal represents a byte in the binary string. Long |
| literals can be divided into multiple quoted strings, separated with a |
| space or end-of-line character.</p> |
| <pre class="programlisting">X'1abACD34' 'Af'</pre> |
| <a name="N11ADB" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>bit literal</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>bit literal</em></span> |
| </p> |
| <p> |
| <code class="literal"><bit string literal> ::= B <quote> [ |
| <bit> ... ] <quote> [ { <separator> <quote> [ |
| <bit>... ] <quote> }... ]</code> |
| </p> |
| <p> |
| <code class="literal"><bit> ::= 0 | 1</code> |
| </p> |
| <p>The type of a binary literal is BIT. The bit length of the bit |
| literal is the length of the type. Digits 0 and 1 are used to represent |
| the bits. Long literals can be divided into multiple quoted strings, |
| separated with a space or end-of-line character.</p> |
| <pre class="programlisting">B'10001001' '00010'</pre> |
| <a name="N11AF1" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>numeric literal</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>numeric literal</em></span> |
| </p> |
| <p> |
| <code class="literal"><signed numeric literal> ::= [ <sign> ] |
| <unsigned numeric literal></code> |
| </p> |
| <p> |
| <code class="literal"><unsigned numeric literal> ::= <exact numeric |
| literal> | <approximate numeric literal></code> |
| </p> |
| <p> |
| <code class="literal"><exact numeric literal> ::= <unsigned |
| integer> [ <period> [ <unsigned integer> ] ] | |
| <period> <unsigned integer></code> |
| </p> |
| <p> |
| <code class="literal"><sign> ::= <plus sign> | <minus |
| sign></code> |
| </p> |
| <p> |
| <code class="literal"><approximate numeric literal> ::= |
| <mantissa> E <exponent></code> |
| </p> |
| <p> |
| <code class="literal"><mantissa> ::= <exact numeric |
| literal></code> |
| </p> |
| <p> |
| <code class="literal"><exponent> ::= <signed |
| integer></code> |
| </p> |
| <p> |
| <code class="literal"><signed integer> ::= [ <sign> ] |
| <unsigned integer></code> |
| </p> |
| <p> |
| <code class="literal"><unsigned integer> ::= |
| <digit>...</code> |
| </p> |
| <p>The type of an exact numeric literal without a decimal point is |
| INTEGER, BIGINT, or DECIMAL, depending on the value of the literal (the |
| smallest type that can represent the value is the type).</p> |
| <p>The type of an exact numeric literal with a decimal point is |
| DECIMAL. The precision of a decimal literal is the total number of |
| digits of the literal. The scale of the literal is the total number of |
| digits to the right of the decimal point.</p> |
| <p>The type of an approximate numeric literal is DOUBLE. An |
| approximate numeric literal always includes the mantissa and exponent, |
| separated by E.</p> |
| <pre class="programlisting">12 |
| 34.35 |
| +12E-2 |
| </pre> |
| <a name="N11B20" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>boolean literal</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>boolean literal</em></span> |
| </p> |
| <p> |
| <code class="literal"><boolean literal> ::= TRUE | FALSE | |
| UNKNOWN</code> |
| </p> |
| <p>The boolean literal is one of the specified keywords.</p> |
| <a name="N11B31" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>datetime and interval |
| literal</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>datetime and interval literal</em></span> |
| </p> |
| <p> |
| <code class="literal"><datetime literal> ::= <date literal> | |
| <time literal> | <timestamp literal></code> |
| </p> |
| <p> |
| <code class="literal"><date literal> ::= DATE <date |
| string></code> |
| </p> |
| <p> |
| <code class="literal"><time literal> ::= TIME <time |
| string></code> |
| </p> |
| <p> |
| <code class="literal"><timestamp literal> ::= TIMESTAMP <timestamp |
| string></code> |
| </p> |
| <p> |
| <code class="literal"><date string> ::= <quote> <unquoted |
| date string> <quote></code> |
| </p> |
| <p> |
| <code class="literal"><time string> ::= <quote> <unquoted |
| time string> <quote></code> |
| </p> |
| <p> |
| <code class="literal"><timestamp string> ::= <quote> |
| <unquoted timestamp string> <quote></code> |
| </p> |
| <p> |
| <code class="literal"><time zone interval> ::= <sign> <hours |
| value> <colon> <minutes value></code> |
| </p> |
| <p> |
| <code class="literal"><date value> ::= <years value> <minus |
| sign> <months value> <minus sign> <days |
| value></code> |
| </p> |
| <p> |
| <code class="literal"><time value> ::= <hours value> |
| <colon> <minutes value> <colon> <seconds |
| value></code> |
| </p> |
| <p> |
| <code class="literal"><interval literal> ::= INTERVAL [ <sign> ] |
| <interval string> <interval qualifier></code> |
| </p> |
| <p> |
| <code class="literal"><interval string> ::= <quote> <unquoted |
| interval string> <quote></code> |
| </p> |
| <p> |
| <code class="literal"><unquoted date string> ::= <date |
| value></code> |
| </p> |
| <p> |
| <code class="literal"><unquoted time string> ::= <time value> [ |
| <time zone interval> ]</code> |
| </p> |
| <p> |
| <code class="literal"><unquoted timestamp string> ::= <unquoted |
| date string> <space> <unquoted time |
| string></code> |
| </p> |
| <p> |
| <code class="literal"><unquoted interval string> ::= [ <sign> ] |
| { <year-month literal> | <day-time literal> |
| }</code> |
| </p> |
| <p> |
| <code class="literal"><year-month literal> ::= <years value> [ |
| <minus sign> <months value> ] | <months |
| value></code> |
| </p> |
| <p> |
| <code class="literal"><day-time literal> ::= <day-time interval> |
| | <time interval></code> |
| </p> |
| <p> |
| <code class="literal"><day-time interval> ::= <days value> [ |
| <space> <hours value> [ <colon> <minutes value> |
| [ <colon> <seconds value> ] ] ]</code> |
| </p> |
| <p> |
| <code class="literal"><time interval> ::= <hours value> [ |
| <colon> <minutes value> [ <colon> <seconds |
| value> ] ] | <minutes value> [ <colon> <seconds |
| value> ] | <seconds value></code> |
| </p> |
| <p> |
| <code class="literal"><years value> ::= <datetime |
| value></code> |
| </p> |
| <p> |
| <code class="literal"><months value> ::= <datetime |
| value></code> |
| </p> |
| <p> |
| <code class="literal"><days value> ::= <datetime |
| value></code> |
| </p> |
| <p> |
| <code class="literal"><hours value> ::= <datetime |
| value></code> |
| </p> |
| <p> |
| <code class="literal"><minutes value> ::= <datetime |
| value></code> |
| </p> |
| <p> |
| <code class="literal"><seconds value> ::= <seconds integer |
| value> [ <period> [ <seconds fraction> ] |
| ]</code> |
| </p> |
| <p> |
| <code class="literal"><seconds integer value> ::= <unsigned |
| integer></code> |
| </p> |
| <p> |
| <code class="literal"><seconds fraction> ::= <unsigned |
| integer></code> |
| </p> |
| <p> |
| <code class="literal"><datetime value> ::= <unsigned |
| integer></code> |
| </p> |
| <p>The type of a datetime or interval type is specified in the |
| literal. The fractional second precision is the number of digits in the |
| fractional part of the literal. Details are described in the <a class="link" href="sqlgeneral-chapt.html" title="Chapter 2. SQL Language">SQL Language</a> |
| chapter</p> |
| <pre class="programlisting">DATE '2008-08-08' |
| TIME '20:08:08' |
| TIMESTAMP '2008-08-08 20:08:08.235' |
| |
| INTERVAL '10' DAY |
| INTERVAL -'08:08' MINUTE TO SECOND |
| </pre> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N11B9C"></a>References, etc.</h3> |
| </div> |
| </div> |
| </div> |
| <p>References are identifier chains, which can be a single |
| identifiers or identifiers chains composed of single identifiers chained |
| together with the period symbol.</p> |
| <a name="N11BA1" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>identifier chain</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>identifier chain</em></span> |
| </p> |
| <p> |
| <code class="literal"><identifier chain> ::= <identifier> [ { |
| <period> <identifier> }... ]</code> |
| </p> |
| <p> |
| <code class="literal"><basic identifier chain> ::= <identifier |
| chain></code> |
| </p> |
| <p>A period-separated chain of identifiers. The identifiers in an |
| identifier chain can refer to database objects in a hierarchy. The |
| possible hierarchies are as follows. In each hierarchy, elements from |
| the start or the end can be missing, but the order of elements cannot be |
| changed.</p> |
| <p>catalog, schema, database object</p> |
| <p>catalog, schema, table, column</p> |
| <p>correlation name, column</p> |
| <p>Examples of identifier chain are given below:</p> |
| <pre class="programlisting">SELECT MYCAT.MYSCHEMA.MYTABLE.MYCOL FROM MYCAT.MYSCHEMA.MYTABLE |
| DROP TABLE MYCAT.MYSCHEMA.MYTABLE CASCADE |
| ALTER SEQUENCE MYCAT.MYSCHEMA.MYSEQUENCE RESTART WITH 100 |
| </pre> |
| <a name="N11BBF" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>column reference</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>column reference</em></span> |
| </p> |
| <p> |
| <code class="literal"><column reference> ::= <basic identifier |
| chain> | MODULE <period> <qualified identifier> |
| <period> <column name></code> |
| </p> |
| <p>Reference a column or a routine variable.</p> |
| <a name="N11BD0" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SQL parameter |
| reference</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>SQL parameter reference</em></span> |
| </p> |
| <p> |
| <code class="literal"><SQL parameter reference> ::= <basic |
| identifier chain></code> |
| </p> |
| <p>Reference an SQL routine parameter.</p> |
| <a name="N11BE1" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>contextually typed value |
| specification</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>contextually typed value |
| specification</em></span> |
| </p> |
| <p> |
| <code class="literal"><contextually typed value specification> ::= |
| <null specification> | <default |
| specification></code> |
| </p> |
| <p> |
| <code class="literal"><null specification> ::= |
| NULL</code> |
| </p> |
| <p> |
| <code class="literal"><default specification> ::= |
| DEFAULT</code> |
| </p> |
| <p>Specify a value whose data type or value is inferred from its |
| context. DEFAULT is used for assignments to table columns that have a |
| default value, or to table columns that are generated either as an |
| IDENTITY value or as an expression. NULL can be used only in a context |
| where the type of the value is known. For example, a NULL can be |
| assigned to a column of the table in an INSERT or UPDATE statement, |
| because the type of the column is known. But if NULL is used in a SELECT |
| list, it must be used in a CAST statement.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N11BF8"></a>Value Expression</h3> |
| </div> |
| </div> |
| </div> |
| <p>Value expression is a general name for all expressions that return |
| a value. Different types of expressions are allowed in different |
| contexts.</p> |
| <a name="N11BFD" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>value expression |
| primary</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>value expression primary</em></span> |
| </p> |
| <p> |
| <code class="literal"><value expression primary> ::= <parenthesized |
| value expression> | <nonparenthesized value expression |
| primary></code> |
| </p> |
| <p> |
| <code class="literal"><parenthesized value expression> ::= <left |
| paren> <value expression> <right |
| paren></code> |
| </p> |
| <p> |
| <code class="literal"><nonparenthesized value expression primary> ::= |
| <unsigned value specification> | <column reference> | |
| <set function specification> | <scalar subquery> | <case |
| expression> | <cast specification> | <next value |
| expression> | <routine invocation></code> |
| </p> |
| <p>Specify a value that is syntactically self-delimited.</p> |
| <a name="N11C14" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>value specification</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>value specification</em></span> |
| </p> |
| <p> |
| <code class="literal"><value specification> ::= <literal> | |
| <general value specification></code> |
| </p> |
| <p> |
| <code class="literal"><unsigned value specification> ::= <unsigned |
| literal> | <general value specification></code> |
| </p> |
| <p> |
| <code class="literal"><target specification> ::= <host parameter |
| specification> | <SQL parameter reference> | <column |
| reference> | <dynamic parameter |
| specification></code> |
| </p> |
| <p> |
| <code class="literal"><simple target specification> ::= <host |
| parameter specification> | <SQL parameter reference> | |
| <column reference> | <embedded variable |
| name></code> |
| </p> |
| <p> |
| <code class="literal"><host parameter specification> ::= <host |
| parameter name> [ <indicator parameter> ]</code> |
| </p> |
| <p> |
| <code class="literal"><dynamic parameter specification> ::= |
| <question mark></code> |
| </p> |
| <p>Specify one or more values, host parameters, SQL parameters, |
| dynamic parameters, or host variables.</p> |
| <a name="N11C34" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>row value expression</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>row value expression</em></span> |
| </p> |
| <p> |
| <code class="literal"><row value expression> ::= <row value special |
| case> | <explicit row value constructor> </code> |
| </p> |
| <p> |
| <code class="literal"><row value predicand> ::= <row value special |
| case> | <row value constructor predicand></code> |
| </p> |
| <p> |
| <code class="literal"><row value special case> ::= |
| <nonparenthesized value expression primary></code> |
| </p> |
| <p> |
| <code class="literal"><explicit row value constructor> ::= <left |
| paren> <row value constructor element> <comma> <row |
| value constructor element list> <right paren> |
| |</code> |
| </p> |
| <p> |
| <code class="literal"> ROW <left paren> <row value constructor |
| element list> <right paren> | <row |
| subquery></code> |
| </p> |
| <p>Specify a row consisting of one or more elements. A comma |
| separated list of expressions, enclosed in brackets, with the optional |
| keyword ROW. In SQL, a row containing a single element can often be used |
| where a single value is expected.</p> |
| <a name="N11C51" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>set function |
| specification</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>set function specification</em></span> |
| </p> |
| <p> |
| <code class="literal"><set function specification> ::= <aggregate |
| function> | <grouping operation></code> |
| </p> |
| <p> |
| <code class="literal"><grouping operation> ::= GROUPING <left |
| paren> <column reference> [ { <comma> <column |
| reference> }... ] <right paren></code> |
| </p> |
| <p>Specify a value derived by the application of a function to an |
| argument. Early releases of HyperSQL 2.0 do not support |
| <code class="literal"><grouping operation></code> .</p> |
| <a name="N11C68" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>COALESCE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>coalesce expression</em></span> |
| </p> |
| <p> |
| <code class="literal"><coalesce expression> := COALESCE <left |
| paren> <value expression> { <comma> <value |
| expression> }... <right paren></code> |
| </p> |
| <p>Replace null values with another value. The coalesce expression |
| has two or more instances of <value expression>. If the first |
| <value expression> evaluates to a non-null value, it is returned |
| as the result of the coalesce expression. If it is null, the next |
| <code class="literal"><value expression></code> is evaluated and if it |
| evaluates to a non-non value, it is returned, and so on.</p> |
| <p>The type of the return value of a COALESCE expression is the |
| aggregate type of the types of all the <code class="literal"><value |
| expression></code> instances. Therefore, any value returned is |
| implicitly cast to this type. HyperSQL also features built-in functions |
| with similar functionality.</p> |
| <a name="N11C81" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>NULLIF</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>nullif expression</em></span> |
| </p> |
| <p> |
| <code class="literal"><nullif expression> := NULLIF <left paren> |
| <value expression> <comma> <value expression> |
| <right paren></code> |
| </p> |
| <p>Return NULL if two values are equal. If the result of the first |
| <code class="literal"><value expression></code> is not equal to the result |
| of the second, then it is returned, otherwise NULL is returned. The type |
| of the return value is the type of the first <code class="literal"><value |
| expression></code>.</p> |
| <pre class="programlisting">SELECT i, NULLIF(n, 'not defined') FROM t</pre> |
| <a name="N11C9A" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CASE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>case specification</em></span> |
| </p> |
| <p> |
| <code class="literal"><case specification> ::= <simple case> | |
| <searched case></code> |
| </p> |
| <p> |
| <code class="literal"><simple case> ::= CASE <case operand> |
| <simple when clause>... [ <else clause> ] |
| END</code> |
| </p> |
| <p> |
| <code class="literal"><searched case> ::= CASE <searched when |
| clause>... [ <else clause> ] END</code> |
| </p> |
| <p> |
| <code class="literal"><simple when clause> ::= WHEN <when operand |
| list> THEN <result></code> |
| </p> |
| <p> |
| <code class="literal"><searched when clause> ::= WHEN <search |
| condition> THEN <result></code> |
| </p> |
| <p> |
| <code class="literal"><else clause> ::= ELSE |
| <result></code> |
| </p> |
| <p> |
| <code class="literal"><case operand> ::= <row value predicand> | |
| <overlaps predicate part 1></code> |
| </p> |
| <p> |
| <code class="literal"><when operand list> ::= <when operand> [ { |
| <comma> <when operand> }... ]</code> |
| </p> |
| <p> |
| <code class="literal"><when operand> ::= <row value predicand> | |
| <comparison predicate part 2> | <between predicate part 2> | |
| <in predicate part 2> | <character like predicate part 2> | |
| <octet like predicate part 2> | <similar predicate part 2> | |
| <regex like predicate part 2> | <null predicate part 2> | |
| <quantified comparison predicate part 2> | <match predicate |
| part 2> | <overlaps predicate part 2> | <distinct predicate |
| part 2></code> |
| </p> |
| <p> |
| <code class="literal"><result> ::= <result expression> | |
| NULL</code> |
| </p> |
| <p> |
| <code class="literal"><result expression> ::= <value |
| expression></code> |
| </p> |
| <p>Specify a conditional value. The result of a case expression is |
| always a value. All the values introduced with THEN must be of the same |
| type.</p> |
| <p>An (simple) example of the CASE statement is given below. It |
| returns 'Britain', 'Germany', or 'Other country' depending on the value |
| of dialcode'</p> |
| <pre class="programlisting">CASE dialcode WHEN 44 THEN 'Britain' WHEN 49 THEN 'Germany' ELSE 'Other country' END</pre> |
| <p>The case statement can be far more complex and involve several |
| conditions.</p> |
| <a name="N11CCF" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CAST</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>cast specification</em></span> |
| </p> |
| <p> |
| <code class="literal"><cast specification> ::= CAST <left paren> |
| <cast operand> AS <cast target> <right |
| paren></code> |
| </p> |
| <p> |
| <code class="literal"><cast operand> ::= <value expression> | |
| <implicitly typed value specification></code> |
| </p> |
| <p> |
| <code class="literal"><cast target> ::= <domain name> | <data |
| type></code> |
| </p> |
| <p>Specify a data conversion. Data conversion takes place |
| automatically among variants of a general type. For example numeric |
| values are freely converted from one type to another in |
| expressions.</p> |
| <p>Explicit type conversion is necessary in two cases. One case is |
| to determine the type of a NULL value. The other case is to force |
| conversion for special purposes. Values of data types can be cast to a |
| character type. The exception is BINARY and OTHER types. The result of |
| the cast is the literal expression of the value. Conversely, a value of |
| a character type can be converted to another type if the character value |
| is a literal representation of the value in the target type. Special |
| conversions are possible between numeric and interval types, which are |
| described in the section covering interval types.</p> |
| <p>The examples below show examples of cast with their |
| result:</p> |
| <pre class="programlisting">CAST (NULL AS TIMESTAMP) |
| CAST (' 199 ' AS INTEGER) = 199 |
| CAST ('tRue ' AS BOOLEAN) = TRUE |
| CAST (INTERVAL '2' DAY AS INTEGER) = 2 |
| CAST ('1992-04-21' AS DATE) = DATE '1992-04-21' |
| </pre> |
| <a name="N11CEC" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>NEXT VALUE FOR</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>next value expression</em></span> |
| </p> |
| <p> |
| <code class="literal"><next value expression> ::= NEXT VALUE FOR |
| <sequence generator name></code> |
| </p> |
| <p>Return the next value of a sequence generator. This expression |
| can be used as a select list element in queries, or in assignments to |
| table columns in data change statements. If the expression is used more |
| than once in a single row that is being evaluated, the same value is |
| returned for each invocation. After evaluation of the particular row is |
| complete, the sequence generator will return a different value from the |
| old value. The new value is generated by the sequence generator by |
| adding the increment to the last value it generated. In the example |
| below:</p> |
| <pre class="programlisting">INSERT INTO MYTABLE(COL1, COL2) VALUES 2, NEXT VALUE FOR MYSEQUENCE |
| </pre> |
| <a name="N11CFF" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>value expression</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>value expression</em></span> |
| </p> |
| <p> |
| <code class="literal"><value expression> ::= <numeric value |
| expression> | <string value expression> | <datetime value |
| expression> | <interval value expression> | <boolean value |
| expression> | <row value expression></code> |
| </p> |
| <p>An expression that returns a value. The value can be a single |
| value, or a row consisting more than one value.</p> |
| <a name="N11D10" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>numeric value |
| expression</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>numeric value expression</em></span> |
| </p> |
| <p> |
| <code class="literal"><numeric value expression> ::= <term> | |
| <numeric value expression> <plus sign> <term> | |
| <numeric value expression> <minus sign> |
| <term></code> |
| </p> |
| <p> |
| <code class="literal"><term> ::= <factor> | <term> |
| <asterisk> <factor> | <term> <solidus> |
| <factor></code> |
| </p> |
| <p> |
| <code class="literal"><factor> ::= [ <sign> ] <numeric |
| primary></code> |
| </p> |
| <p> |
| <code class="literal"><numeric primary> ::= <value expression |
| primary> | <numeric value function></code> |
| </p> |
| <p>Specify a numeric value. The BNF indicates that |
| <code class="literal"><asterisk></code> and |
| <code class="literal"><solidus></code> (the operators for multiplication and |
| division) have precedence over <code class="literal"><minus sign></code> and |
| <code class="literal"><plus sign></code>.</p> |
| <a name="N11D36" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>numeric value |
| function</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>numeric value function</em></span> |
| </p> |
| <p> |
| <code class="literal"><numeric value function> ::= <position |
| expression> | <extract expression> | <length expression> |
| ...</code> |
| </p> |
| <p>Specify a function yielding a value of type numeric. The |
| supported numeric value functions are listed and described in the <a class="link" href="builtinfunctions-chapt.html" title="Chapter 10. Built In Functions">Built In Functions</a> chapter.</p> |
| <a name="N11D4B" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>string value |
| expression</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>string value expression</em></span> |
| </p> |
| <p> |
| <code class="literal"><string value expression> ::= <string |
| concatenation> | <string factor></code> |
| </p> |
| <p> |
| <code class="literal"><string factor> ::= <value expression |
| primary> | <string value function></code> |
| </p> |
| <p> |
| <code class="literal"><string concatenation> ::= <string value |
| expression> <concatenation operator> <string |
| factor></code> |
| </p> |
| <p> |
| <code class="literal"><concatenation operator> ::= |
| ||</code> |
| </p> |
| <p>Specify a character string value, a binary string value, or a |
| bit string value. The BNF indicates that a string value expression can |
| be formed by concatenation of two or more <code class="literal"><value expression |
| primary></code>. The types of the <code class="literal"><value expression |
| primary></code> elements must be compatible, that is, all must be |
| string, or binary or bit string values.</p> |
| <a name="N11D6B" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>character value |
| function</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>string value function</em></span> |
| </p> |
| <p> |
| <code class="literal"><string value function> ::= |
| ...</code> |
| </p> |
| <p>Specify a function that returns a character string or binary |
| string. The supported character value functions are listed and described |
| in the <a class="link" href="builtinfunctions-chapt.html" title="Chapter 10. Built In Functions">Built In Functions</a> chapter.</p> |
| <a name="N11D80" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>datetime value |
| expression</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>datetime value expression</em></span> |
| </p> |
| <p> |
| <code class="literal"><datetime value expression> ::= <datetime |
| term> | <interval value expression> <plus sign> |
| <datetime term> | <datetime value expression> <plus |
| sign> <interval term> | <datetime value expression> |
| <minus sign> <interval term></code> |
| </p> |
| <p> |
| <code class="literal"><datetime term> ::= <datetime |
| factor></code> |
| </p> |
| <p> |
| <code class="literal"><datetime factor> ::= <datetime primary> [ |
| <time zone> ]</code> |
| </p> |
| <p> |
| <code class="literal"><datetime primary> ::= <value expression |
| primary> | <datetime value function></code> |
| </p> |
| <p> |
| <code class="literal"><time zone> ::= AT <time zone |
| specifier></code> |
| </p> |
| <p> |
| <code class="literal"><time zone specifier> ::= LOCAL | TIME ZONE |
| <interval primary></code> |
| </p> |
| <p>Specify a datetime value. Details are described in the <a class="link" href="sqlgeneral-chapt.html" title="Chapter 2. SQL Language">SQL Language</a> |
| chapter.</p> |
| <a name="N11DA4" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>datetime value |
| function</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>datetime value function</em></span> |
| </p> |
| <p> |
| <code class="literal"><datetime value function> ::= |
| ...</code> |
| </p> |
| <p>Specify a function that returns a datetime value. The supported |
| datetime value functions are listed and described in the <a class="link" href="builtinfunctions-chapt.html" title="Chapter 10. Built In Functions">Built In Functions</a> chapter.</p> |
| <a name="N11DBA" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>interval term</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>interval value expression</em></span> |
| </p> |
| <p> |
| <code class="literal"><interval value expression> ::= <interval |
| term> | <interval value expression 1> <plus sign> |
| <interval term 1> | <interval value expression 1> <minus |
| sign> <interval term 1> | <left paren> <datetime value |
| expression> <minus sign> <datetime term> <right |
| paren> <interval qualifier></code> |
| </p> |
| <p> |
| <code class="literal"><interval term> ::= <interval factor> | |
| <interval term 2> <asterisk> <factor> | <interval |
| term 2> <solidus> <factor> | <term> |
| <asterisk> <interval factor></code> |
| </p> |
| <p> |
| <code class="literal"><interval factor> ::= [ <sign> ] |
| <interval primary></code> |
| </p> |
| <p> |
| <code class="literal"><interval primary> ::= <value expression |
| primary> [ <interval qualifier> ] | <interval value |
| function></code> |
| </p> |
| <p> |
| <code class="literal"><interval value expression 1> ::= <interval |
| value expression></code> |
| </p> |
| <p> |
| <code class="literal"><interval term 1> ::= <interval |
| term></code> |
| </p> |
| <p> |
| <code class="literal"><interval term 2> ::= <interval |
| term></code> |
| </p> |
| <p>Specify an interval value. Details are described in the <a class="link" href="sqlgeneral-chapt.html" title="Chapter 2. SQL Language">SQL Language</a> |
| chapter.</p> |
| <a name="N11DE1" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>interval absolute value |
| function</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>interval value function</em></span> |
| </p> |
| <p> |
| <code class="literal"><interval value function> ::= <interval |
| absolute value function></code> |
| </p> |
| <p> |
| <code class="literal"><interval absolute value function> ::= ABS |
| <left paren> <interval value expression> <right |
| paren></code> |
| </p> |
| <p>Specify a function that returns the absolute value of an |
| interval. If the interval is negative, it is negated, otherwise the |
| original value is returned.</p> |
| <a name="N11DF5" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>boolean value |
| expression</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>boolean value expression</em></span> |
| </p> |
| <p> |
| <code class="literal"><boolean value expression> ::= <boolean |
| term> | <boolean value expression> OR <boolean |
| term></code> |
| </p> |
| <p> |
| <code class="literal"><boolean term> ::= <boolean factor> | |
| <boolean term> AND <boolean factor></code> |
| </p> |
| <p> |
| <code class="literal"><boolean factor> ::= [ NOT ] <boolean |
| test></code> |
| </p> |
| <p> |
| <code class="literal"><boolean test> ::= <boolean primary> [ IS |
| [ NOT ] <truth value> ]</code> |
| </p> |
| <p> |
| <code class="literal"><truth value> ::= TRUE | FALSE | |
| UNKNOWN</code> |
| </p> |
| <p> |
| <code class="literal"><boolean primary> ::= <predicate> | |
| <boolean predicand></code> |
| </p> |
| <p> |
| <code class="literal"><boolean predicand> ::= <parenthesized |
| boolean value expression> | <nonparenthesized value expression |
| primary></code> |
| </p> |
| <p> |
| <code class="literal"><parenthesized boolean value expression> ::= |
| <left paren> <boolean value expression> <right |
| paren></code> |
| </p> |
| <p>Specify a boolean value.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N11E1B"></a>Predicates</h3> |
| </div> |
| </div> |
| </div> |
| <p>Predicates are conditions with two sides and evaluate to a |
| boolean value. The left side of the predicate, the <code class="literal"><row |
| value predicand></code>, is the common element of all predicates. |
| This element is a generalisation of both <code class="literal"><value |
| expression></code>, which is a scalar, and of |
| <code class="literal"><explicit row value constructor></code>, which is a |
| row. The two sides of a predicate can be split in CASE statements where |
| the <code class="literal"><row value predicand></code> is part of multiple |
| predicates.</p> |
| <p>The number of fields in all <code class="literal"><row value |
| predicand></code> used in predicates must be the same and the |
| types of the fields in the same position must be compatible for |
| comparison. If either of these conditions does not hold, an exception is |
| raised. The number of fields in a row is called the |
| <em class="glossterm">degree</em>.</p> |
| <p>In many types of predicates (but not all of them), if the |
| <code class="literal"><row value predicand></code> evaluates to NULL, the |
| result of the predicate is UNKNOWN. If the <code class="literal"><row value |
| predicand></code> has more than one element, and one or more of |
| the fields evaluate to NULL, the result depends on the particular |
| predicate.</p> |
| <a name="N11E3C" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>comparison predicand</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>comparison predicate</em></span> |
| </p> |
| <p> |
| <code class="literal"><comparison predicate> ::= <row value |
| predicand> <comp op> <row value |
| predicand></code> |
| </p> |
| <p> |
| <code class="literal"><comp op> ::= <equals operator> | <not |
| equals operator> | <less than operator> | <greater than |
| operator> | <less than or equals operator> | <greater than |
| or equals operator></code> |
| </p> |
| <p>Specify a comparison of two row values. If either |
| <code class="literal"><row value predicand></code> evaluates to NULL, the |
| result of <code class="literal"><comparison predicate></code> is UNKNOWN. |
| Otherwise, the result is TRUE, FALSE or UNKNOWN.</p> |
| <p>If the <em class="glossterm">degree</em> of <code class="literal"><row value |
| predicand></code> is larger than one, comparison is performed |
| between each field and the corresponding field in the other |
| <code class="literal"><row value predicand></code> from left to right, one |
| by one.</p> |
| <p>When comparing two elements, if either field is NULL then the |
| result is UNKNOWN.</p> |
| <p>For <code class="literal"><equals operator></code>, if the result |
| of comparison is TRUE for all field, the result of the predicate is |
| TRUE. If the result of comparison is FALSE for one field, the result of |
| predicate is FALSE. Otherwise the result is UNKNOWN.</p> |
| <p>The <code class="literal"><not equals operator></code> is |
| translated to <code class="literal">NOT (<row value predicand> = <row value |
| predicand>)</code>.</p> |
| <p>The <code class="literal"><less than or equals operator></code> is |
| translated to <code class="literal">(<row value predicand> = <row value |
| predicand>) OR (<row value predicand> < <row value |
| predicand>)</code>. The <code class="literal"><greater than or equals |
| operator></code> is translated similarly.</p> |
| <p>For the <code class="literal"><less than operator></code> and |
| <code class="literal"><greater than operator></code>, if two fields at a |
| given position are equal, then comparison continues to the next field. |
| Otherwise, the result of the last performed comparison is returned as |
| the result of the predicate. This means that if the first field is NULL, |
| the result is always UNKNOWN.</p> |
| <p>The logic that governs NULL values and UNKNOWN result is as |
| follows: Suppose the NULL values were substituted by arbitrary real |
| values. If substitution cannot change the result of the predicate, then |
| the result is TRUE or FALSE, based on the existing non-NULL values, |
| otherwise the result of the predicate is UNKNOWN.</p> |
| <p>The examples of comparison given below use literals, but the |
| literals actually represent the result of evaluation of some |
| expression.</p> |
| <pre class="programlisting">((1, 2, 3, 4) = (1, 2, 3, 4)) IS TRUE |
| ((1, 2, 3, 4) = (1, 2, 3, 5)) IS FALSE |
| ((1, 2, 3, 4) < (1, 2, 3, 4)) IS FALSE |
| ((1, 2, 3, 4) < (1, 2, 3, 5)) IS TRUE |
| ((NULL, 1, NULL) = (NULL, 1, NULL)) IS UNKNOWN |
| ((NULL, 1, NULL) = (NULL, 2, NULL)) IS FALSE |
| ((NULL, 1, NULL) <> (NULL, 2, NULL)) IS TRUE |
| ((NULL, 1, 2) <all operators> (NULL, 1, 2)) IS UNKNOWN |
| ((1, NULL, ...) < (1, 2, ...)) IS UNKNOWN |
| ((1, NULL, ...) < (2, NULL, ...)) IS TRUE |
| ((2, NULL, ...) < (1, NULL, ...)) IS FALSE |
| </pre> |
| <a name="N11E89" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>BETWEEN</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>between predicate</em></span> |
| </p> |
| <p> |
| <code class="literal"><between predicate> ::= <row value |
| predicand> <between predicate part 2></code> |
| </p> |
| <p> |
| <code class="literal"><between predicate part 2> ::= [ NOT ] BETWEEN [ |
| ASYMMETRIC | SYMMETRIC ] <row value predicand> AND <row value |
| predicand></code> |
| </p> |
| <p>Specify a range comparison. The default is ASYMMETRIC. The |
| expression <code class="literal">X BETWEEN Y AND Z</code> is equivalent to |
| <code class="literal">(X >= Y AND X <= Z)</code>. Therefore if Y > Z, the |
| BETWEEN expression is never true. The expression <code class="literal">X BETWEEN |
| SYMMETRIC Y AND Z</code> is equivalent to <code class="literal">(X >= Y AND X |
| <= Z) OR (X >= Z AND X <= Y)</code>. The expression |
| <code class="literal">Z NOT BETWEEN ...</code> is equivalent to <code class="literal">NOT (Z |
| BETWEEN ...)</code>. If any of the three <code class="literal"><row value |
| predicand></code> evaluates to NULL, the result is |
| UNKNOWN.</p> |
| <a name="N11EB2" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>IN</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>in predicate</em></span> |
| </p> |
| <p> |
| <code class="literal"><in predicate> ::= <row value predicand> [ |
| NOT ] IN <in predicate value></code> |
| </p> |
| <p> |
| <code class="literal"><in predicate value> ::= <table subquery> |
| | <left paren> <in value list> <right paren> |
| </code> |
| </p> |
| <p> |
| <code class="literal">| <left paren> UNNEST <left paren> |
| <array value expression> <right paren> <right |
| paren></code> |
| </p> |
| <p> |
| <code class="literal"><in value list> ::= <row value expression> |
| [ { <comma> <row value expression> }... |
| ]</code> |
| </p> |
| <p>Specify a quantified comparison. The expression <code class="literal">X NOT |
| IN Y is</code> equivalent to <code class="literal">NOT (X IN Y)</code>. The |
| <code class="literal">( <in value list> )</code> is converted into a table |
| with one or more rows. The expression <code class="literal">X IN Y</code> is |
| equivalent to <code class="literal">X = ANY Y</code>, which is a |
| <code class="literal"><quantified comparison predicate></code>.</p> |
| <p>If the <code class="literal"><table subquery></code> returns no |
| rows, the result is FALSE. Otherwise the <code class="literal"><row value |
| predicand></code> is compared one by one with each row of the |
| <code class="literal"><table subquery></code>.</p> |
| <p>If the comparison is TRUE for at least one row, the result is |
| TRUE. If the comparison is FALSE for all rows, the result is FALSE. |
| Otherwise the result is UNKNOWN.</p> |
| <p>HyperSQL supports an extension to the SQL Standard to allow an |
| array to be used in the <in predicate value>. This is intended to |
| be used with prepared statements where a variable length array of values |
| can be used as the parameter value for each call. The example below |
| shows how this is used.</p> |
| <pre class="programlisting">SELECT * from customer where firstname in ( UNNEST(?) ) |
| </pre> |
| <a name="N11EEF" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>LIKE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>like predicate</em></span> |
| </p> |
| <p> |
| <code class="literal"><like predicate> ::= <character like |
| predicate> | <octet like predicate></code> |
| </p> |
| <p> |
| <code class="literal"><character like predicate> ::= <row value |
| predicand> [ NOT ] LIKE <character pattern> [ ESCAPE <escape |
| character> ]</code> |
| </p> |
| <p> |
| <code class="literal"><character pattern> ::= <character value |
| expression></code> |
| </p> |
| <p> |
| <code class="literal"><escape character> ::= <character value |
| expression></code> |
| </p> |
| <p> |
| <code class="literal"><octet like predicate> ::= <row value |
| predicand> [ NOT ] LIKE <octet pattern> [ ESCAPE <escape |
| octet> ]</code> |
| </p> |
| <p> |
| <code class="literal"><octet pattern> ::= <binary value |
| expression></code> |
| </p> |
| <p> |
| <code class="literal"><escape octet> ::= <binary value |
| expression></code> |
| </p> |
| <p>Specify a pattern-match comparison for character or binary |
| strings. The <code class="literal"><row value predicand></code> is always a |
| <code class="literal"><string value expression></code> of character or |
| binary type. The <code class="literal"><character pattern></code> or |
| <code class="literal"><octet pattern></code> is a <code class="literal"><string value |
| expression></code> in which the underscore and percent characters |
| have special meanings. The underscore means match any one character, |
| while the percent means match a sequence of zero or more characters. The |
| <code class="literal"><escape character></code> or <code class="literal"><escape |
| octet></code> is also a <code class="literal"><string value |
| expression></code> that evaluates to a string of exactly one |
| character length. If the underscore or the percent is required as normal |
| characters in the pattern, the specified <code class="literal"><escape |
| character></code> or <code class="literal"><escape octet></code> can |
| be used in the pattern before the underscore or the percent. The |
| <code class="literal"><row value predicand></code> is compared with the |
| <code class="literal"><character pattern></code> and the result of |
| comparison is returned. If any of the expressions in the predicate |
| evaluates to NULL, the result of the predicate is UNKNOWN. The |
| expression <code class="literal">A NOT LIKE B</code> is equivalent to <code class="literal">NOT |
| (A LIKE B)</code>. If the length of the escape is not 1 or it is used |
| in the pattern not immediately before an underscore or a percent |
| character, an exception is raised.</p> |
| <a name="N11F3C" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>IS NULL</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>null predicate</em></span> |
| </p> |
| <p> |
| <code class="literal"><null predicate> ::= <row value predicand> |
| IS [ NOT ] NULL</code> |
| </p> |
| <p>Specify a test for a null value. The expression <code class="literal">X IS |
| NOT NULL</code> is NOT equivalent to <code class="literal">NOT (X IS |
| NULL)</code>if the degree of the <code class="literal"><row value |
| predicand></code> is larger than 1. The rules are: If all fields |
| are null, <code class="literal">X IS NULL</code> is TRUE and <code class="literal">X IS NOT |
| NULL</code> is FALSE. If only some fields are null, both <code class="literal">X |
| IS NULL</code> and <code class="literal">X IS NOT NULL</code> are FALSE. If all |
| fields are not null, <code class="literal">X IS NULL</code> is FALSE and |
| <code class="literal">X IS NOT NULL</code> is TRUE.</p> |
| <a name="N11F68" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ALL and ANY</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>quantified comparison predicate</em></span> |
| </p> |
| <p> |
| <code class="literal"><quantified comparison predicate> ::= <row |
| value predicand> <comp op> <quantifier> <table |
| subquery></code> |
| </p> |
| <p> |
| <code class="literal"><quantifier> ::= <all> | |
| <some></code> |
| </p> |
| <p> |
| <code class="literal"><all> ::= ALL</code> |
| </p> |
| <p> |
| <code class="literal"><some> ::= SOME | ANY</code> |
| </p> |
| <p>Specify a quantified comparison. For a quantified comparison, |
| the <code class="literal"><row value predicand></code> is compared one by |
| one with each row of the <code class="literal"><table sub |
| query></code>.</p> |
| <p>If the <code class="literal"><table subquery></code> returns no |
| rows, then if <code class="literal">ALL</code> is specified the result is TRUE, |
| but if <code class="literal">SOME</code> or <code class="literal">ANY</code> is specified |
| the result is FALSE.</p> |
| <p>If <code class="literal">ALL</code> is specified, if the comparison is |
| TRUE for all rows, the result of the predicate is TRUE. If the |
| comparison is FALSE for at least one row, the result is FALSE. Otherwise |
| the result is UNKNOWN.</p> |
| <p>If <code class="literal">SOME</code> or <code class="literal">ANY</code> is |
| specified, if the comparison is TRUE for at least one row, the result is |
| TRUE. If the comparison is FALSE for all rows, the result is FALSE. |
| Otherwise the result is UNKNOWN. Note that the IN predicate is |
| equivalent to the SOME or ANY predicate using the <code class="literal"><equals |
| operator></code>.</p> |
| <p>In the examples below, the date of an invoice is compared to |
| holidays in a given year. In the first example the invoice date must |
| equal one of the holidays, in the second example it must be later than |
| all holidays (later than the last holiday), in the third example it must |
| be on or after some holiday (on or after the first holiday), and in the |
| fourth example, it must be before all holidays (before the first |
| holiday).</p> |
| <pre class="programlisting">invoice_date = SOME (SELECT holiday_date FROM holidays) |
| invoice_date > ALL (SELECT holiday_date FROM holidays) |
| invoice_date >= ANY (SELECT holiday_date FROM holidays) |
| invoice_date < ALL (SELECT holiday_date FROM holidays) |
| </pre> |
| <a name="N11FAA" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>EXISTS</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>exists predicate</em></span> |
| </p> |
| <p> |
| <code class="literal"><exists predicate> ::= EXISTS <table |
| subquery></code> |
| </p> |
| <p>Specify a test for a non-empty set. If the evaluation of |
| <code class="literal"><table subquery></code> results in one or more rows, |
| then the expression is TRUE, otherwise FALSE.</p> |
| <a name="N11FBE" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>UNIQUE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>unique predicate</em></span> |
| </p> |
| <p> |
| <code class="literal"><unique predicate> ::= UNIQUE <table |
| subquery></code> |
| </p> |
| <p>Specify a test for the absence of duplicate rows. The result of |
| the test is either TRUE or FALSE (never UNKNOWN). The rows of the |
| <code class="literal"><table subquery></code> that contain one or more NULL |
| values are not considered for this test. If the rest of the rows are |
| distinct from each other, the result of the test is TRUE, otherwise it |
| is FALSE. The distinctness of rows X and Y is tested with the predicate |
| <code class="literal">X IS DISTINCT FROM Y</code>.</p> |
| <a name="N11FD5" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>MATCH</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>match predicate</em></span> |
| </p> |
| <p> |
| <code class="literal"><match predicate> ::= <row value |
| predicand> MATCH [ UNIQUE ] [ SIMPLE | PARTIAL | FULL ] <table |
| subquery></code> |
| </p> |
| <p>Specify a test for matching rows. The default is MATCH SIMPLE |
| without UNIQUE. The result of the test is either TRUE or FALSE (never |
| UNKNOWN).</p> |
| <p>The interpretation of NULL values is different from other |
| predicates and quite counter-intuitive. If the <code class="literal"><row value |
| predicand></code> is NULL, or all of its fields are NULL, the |
| result is TRUE.</p> |
| <p>Otherwise, the <code class="literal"><row value predicand></code> |
| is compared with each row of the <code class="literal"><table |
| subquery></code>.</p> |
| <p>If SIMPLE is specified, if some field of <code class="literal"><row value |
| predicate></code> is NULL, the result is TRUE. Otherwise if |
| <code class="literal"><row value predicate> </code>is equal to one or more |
| rows of <code class="literal"><table subquery></code> the result is TRUE if |
| UNIQUE is not specified, or if UNIQUE is specified and only one row |
| matches. Otherwise the result is FALSE.</p> |
| <p>If PARTIAL is specified, if the non-null values |
| <code class="literal"><row value predicate> </code>are equal to those in one |
| or more rows of <code class="literal"><table subquery></code> the result is |
| TRUE if UNIQUE is not specified, or if UNIQUE is specified and only one |
| row matches. Otherwise the result is FALSE.</p> |
| <p>If FULL is specified, if some field of <code class="literal"><row value |
| predicate></code> is NULL, the result is FALSE. Otherwise if |
| <code class="literal"><row value predicate></code> is equal to one or more |
| rows of <code class="literal"><table subquery></code> the result is TRUE if |
| UNIQUE is not specified, or if UNIQUE is specified and only one row |
| matches.</p> |
| <p>Note that MATCH can also used be used in FOREIGN KEY constraint |
| definitions. The exact meaning is described in the <a class="link" href="databaseobjects-chapt.html" title="Chapter 4. Schemas and Database Objects">Schemas and Database Objects</a> chapter.</p> |
| <a name="N12017" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>OVERLAPS</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>overlaps predicate</em></span> |
| </p> |
| <p> |
| <code class="literal"><overlaps predicate> ::= <row value |
| predicand> OVERLAPS <row value predicand></code> |
| </p> |
| <p>Specify a test for an overlap between two datetime periods. |
| Each <code class="literal"><row value predicand></code> must have two fields |
| and the fields together represent a datetime period. So the predicates |
| is always in the form <code class="literal">(X1, X2) OVERLAPS (Y1, Y2)</code>. The |
| first field is always a datetime value, while the second field is either |
| a datetime value or an interval value.</p> |
| <p>If the second value is an interval value, it is replaced with |
| the sum of the datetime value and itself, for example <code class="literal">(X1, X1 + |
| X2) OVERLAPS (Y1, Y1 + Y 2)</code>.</p> |
| <p>If any of the values is NULL, the result is UNKNOWN.</p> |
| <p>The expression is true if there is there is any overlap between |
| the two datetime periods. In the example below, the period is compared |
| with a week long period ending yesterday.</p> |
| <pre class="programlisting">(startdate, enddate) OVERLAPS (CURRENT_DATE - 7 DAY, CURRENT_DATE - 1 DAY)</pre> |
| <a name="N12039" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>IS DISTINCT</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>is distinct predicate</em></span> |
| </p> |
| <p> |
| <code class="literal"><distinct predicate> ::= <row value |
| predicand> IS [ NOT ] DISTINCT FROM <row value |
| predicand></code> |
| </p> |
| <p>Specify a test of whether two row values are distinct. The |
| result of the test is either TRUE or FALSE (never UNKNOWN). The |
| <em class="glossterm">degree</em> the two <code class="literal"><row value |
| predicand></code> must be the same. Each field of the first |
| <code class="literal"><row value predicand></code> is compared to the field |
| of the second <code class="literal"><row value predicand></code> at the same |
| position. If one field is NULL and the other is not NULL, or if the |
| elements are NOT equal, then the result of the expression is TRUE. If no |
| comparison result is TRUE, then the result of the predicate is FALSE. |
| The expression <code class="literal">X IS NOT DISTINCT FROM Y</code> is equivalent |
| to <code class="literal">NOT (X IS DISTINCT FORM Y)</code>. The following check |
| returns true if startdate is not equal to enddate. It also returns true |
| if either startdate or enddate is NULL. It returns false in other |
| cases.</p> |
| <pre class="programlisting">startdate IS DISTINCT FROM enddate</pre> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N1205E"></a>Other Syntax Elements</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N12061" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>search condition</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>search condition</em></span> |
| </p> |
| <p> |
| <code class="literal"><search condition> ::= <boolean value |
| expression></code> |
| </p> |
| <p>Specify a condition that is TRUE, FALSE, or UNKNOWN. A search |
| condition is often a predicate.</p> |
| <a name="N12072" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>PATH</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>path specification</em></span> |
| </p> |
| <p> |
| <code class="literal"><path specification> ::= PATH <schema name |
| list></code> |
| </p> |
| <p> |
| <code class="literal"><schema name list> ::= <schema name> [ { |
| <comma> <schema name> }... ]</code> |
| </p> |
| <p>Specify an order for searching for a user-defined SQL-invoked |
| routine. This is not currently supported by HyperSQL.</p> |
| <a name="N12086" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>routine invocation</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>routine invocation</em></span> |
| </p> |
| <p> |
| <code class="literal"><routine invocation> ::= <routine name> |
| <SQL argument list></code> |
| </p> |
| <p> |
| <code class="literal"><routine name> ::= [ <schema name> |
| <period> ] <qualified identifier></code> |
| </p> |
| <p> |
| <code class="literal"><SQL argument list> ::= <left paren> [ |
| <SQL argument> [ { <comma> <SQL argument> }... ] ] |
| <right paren></code> |
| </p> |
| <p> |
| <code class="literal"><SQL argument> ::= <value expression> | |
| <target specification></code> |
| </p> |
| <p>Invoke an SQL-invoked routine. Examples are given in the <a class="link" href="sqlroutines-chapt.html" title="Chapter 8. SQL-Invoked Routines">SQL-Invoked Routines</a> |
| chapter.</p> |
| <a name="N120A4" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>COLLATE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>collate clause</em></span> |
| </p> |
| <p> |
| <code class="literal"><collate clause> ::= COLLATE <collation |
| name></code> |
| </p> |
| <p>Specify a default collation. This is not currently supported by |
| HyperSQL</p> |
| <a name="N120B5" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CONSTRAINT</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>constraint name definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><constraint name definition> ::= CONSTRAINT |
| <constraint name></code> |
| </p> |
| <p> |
| <code class="literal"><constraint characteristics> ::= <constraint |
| check time> [ [ NOT ] DEFERRABLE ] | [ NOT ] DEFERRABLE [ |
| <constraint check time> ]</code> |
| </p> |
| <p> |
| <code class="literal"><constraint check time> ::= INITIALLY DEFERRED | |
| INITIALLY IMMEDIATE</code> |
| </p> |
| <p>Specify the name of a constraint and its characteristics. This |
| is an optional element of CONSTRAINT definition, not yet supported by |
| HyperSQL.</p> |
| <a name="N120CC" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>aggregate function</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>aggregate function</em></span> |
| </p> |
| <p> |
| <code class="literal"><aggregate function> ::= COUNT <left |
| paren> <asterisk> <right paren> [ <filter clause> ] |
| | <general set function> [ <filter clause> ] |
| </code> |
| </p> |
| <p> |
| <code class="literal"><general set function> ::= <set function |
| type> <left paren> [ <set quantifier> ] <value |
| expression> <right paren></code> |
| </p> |
| <p> |
| <code class="literal"><set function type> ::= <computational |
| operation></code> |
| </p> |
| <p> |
| <code class="literal"><computational operation> ::= AVG | MAX | MIN | |
| SUM | EVERY | ANY | SOME | COUNT | STDDEV_POP | STDDEV_SAMP | VAR_SAMP | |
| VAR_POP</code> |
| </p> |
| <p> |
| <code class="literal"><set quantifier> ::= DISTINCT | |
| ALL</code> |
| </p> |
| <p> |
| <code class="literal"><filter clause> ::= FILTER <left paren> |
| WHERE <search condition> <right paren></code> |
| </p> |
| <p>Specify a value computed from a collection of rows. An |
| aggregate function is used exclusively in a <code class="literal"><query |
| specification></code> and its use transforms a normal query into |
| an aggregate query returning a single row instead of the group of |
| multiple rows that the original query returns. For example, |
| <code class="literal">SELECT acolumn <table expression></code> is a query |
| that returns the value of acolumn for all the rows the satisfy the given |
| condition. But <code class="literal">SELECT MAX(acolumn) <table |
| expression></code> returns only one row, containing the largest |
| value in that column. The query <code class="literal">SELECT COUNT(*) <table |
| expression></code> returns the count of rows, while |
| <code class="literal">SELECT COUNT(acolumn) <table expression></code> |
| returns the count of rows where <code class="literal">acolumn IS NOT |
| NULL</code>.</p> |
| <p>If the <code class="literal"><table expression></code> is a grouped |
| table, the aggregate function returns the result of the |
| <code class="literal">COUNT</code> or <code class="literal"><computational |
| operation></code> for each group. In this case the result has the |
| same number of rows as the original query. For example <code class="literal">SELECT |
| SUM(acolumn) <table expression></code> when <code class="literal"><table |
| expression></code> has a <code class="literal">GROUP BY</code> clause, |
| returns the sum of values for <code class="literal">acolumn</code> in each |
| group.</p> |
| <p>The AVG and SUM operations can be performed on numeric |
| expressions only. AVG returns the average value, while SUM returns the |
| sum of all non-null values. MAX and MIN return the minimum or the |
| maximum value. If all values are NULL, the operations return NULL. The |
| <code class="literal">COUNT(*)</code> operation returns the count of all values, |
| while <code class="literal">COUNT(<value expression>)</code> returns the |
| count of non-NULL values.</p> |
| <p>The EVERY, ANY and SOME operations can be performed on boolean |
| expressions only. EVERY returns TRUE if all the values are TRUE, |
| otherwise FALSE. ANY and SOME are the same operation and return TRUE if |
| one of the values is TRUE, otherwise it returns FALSE.</p> |
| <p>The other operations perform the statistical functions |
| STDDEV_POP, STDDEV_SAMP, VAR_SAMP, VAR_POP on numeric values. NULL |
| values are ignored in calculations.</p> |
| <p>User defined aggregate functions can be defined and used |
| instead of the built-in aggregate functions. Syntax and examples are |
| given in the <a class="link" href="sqlroutines-chapt.html" title="Chapter 8. SQL-Invoked Routines">SQL-Invoked Routines</a> chapter.</p> |
| <a name="N12127" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>sort specification |
| list</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>sort specification list</em></span> |
| </p> |
| <p> |
| <code class="literal"><sort specification list> ::= <value |
| expression> [ASC | DESC] [NULLS FIRST | NULLS |
| LAST]</code> |
| </p> |
| <p>Specify a sort order. A sort operation is performed on the |
| result of a <code class="literal"><query expression></code> or |
| <code class="literal"><query specification></code> and sorts the result |
| according to one or more <code class="literal"><value expression></code>. |
| The <code class="literal"><value expression></code> is usually a single |
| column of the result, but in some cases it can be a column of the |
| <code class="literal"><table expression></code> that is not used in the |
| select list.</p> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="N12147"></a>Data Access Statements</h2> |
| </div> |
| </div> |
| </div> |
| <p>HyperSQL fully supports all of SQL-92 data access statements, plus |
| some additions from SQL:2008. Due to time constraints, the current version |
| of this Guide does not cover the subject fully. You are advised to consult |
| an SQL book such as the recent O'Reilly title "SQL and Relational Theory" |
| by C. J. Date.</p> |
| <p>Database queries are data access statements. The most commonly used |
| data access statement is the SELECT statement, but there are other |
| statements that perform a similar role. Data access statements access |
| tables and return result tables. The returned result tables are falsely |
| called result sets, as they are not necessarily sets of rows, but |
| multisets of rows.</p> |
| <p>Result tables are formed by performing the following operations on |
| base tables and views. These operations are loosely based on Relational |
| Algebra.</p> |
| <p> |
| <em class="glossterm">JOIN</em> operations</p> |
| <p> |
| <em class="glossterm">SET</em> and <em class="glossterm">MULTISET</em> |
| operations</p> |
| <p> |
| <em class="glossterm">SELECTION</em> |
| </p> |
| <p> |
| <em class="glossterm">PROJECTION</em> |
| </p> |
| <p> |
| <em class="glossterm">COMPUTING</em> |
| </p> |
| <p> |
| <em class="glossterm">COLUMN NAMING</em> |
| </p> |
| <p> |
| <em class="glossterm">GROUPING</em> and |
| <em class="glossterm">AGGREGATION</em> |
| </p> |
| <p> |
| <em class="glossterm">SELECTION AFTER GROUPING OR |
| AGGREGATION</em> |
| </p> |
| <p> |
| <em class="glossterm">SET and MULTISET (COLLECTION) |
| OPERATIONS</em> |
| </p> |
| <p> |
| <em class="glossterm">ORDERING</em> |
| </p> |
| <p> |
| <em class="glossterm">SLICING</em> |
| </p> |
| <p>Conceptually, the operations are performed one by one in the above |
| order if they apply to the given data access statement. In the example |
| below a simple select statement is made more complex by adding various |
| operations.</p> |
| <p> |
| <pre class="programlisting">CREATE TABLE atable (a INT, b INT, c INT, d INT, e INT, f INT); |
| /* in the next SELECT, no join is performed and no further operation takes place */ |
| SELECT * FROM atable |
| /* in the next SELECT, selection is performed by the WHERE clause, with no further action */ |
| SELECT * FROM atable WHERE a + b = c |
| /* in the next SELECT, projection is performed after the other operations */ |
| SELECT d, e, f FROM atable WHERE a + b = c |
| /* in the next SELECT, computation is performed after projection */ |
| SELECT (d + e) / f FROM atable WHERE a + b = c |
| /* in the next two SELECT statements, column naming is performed in different ways*/ |
| SELECT (a + e) / f AS calc, f AS div FROM atable WHERE a + b = c |
| SELECT dcol, ecol, fcol FROM atable(acol, bcol, ccol, dcol, ecol, fcol) WHERE acol + bcol = ccol |
| /* in the next SELECT, both grouping and aggregation is performed */ |
| SELECT d, e, SUM(f) FROM atable GROUP BY d, e |
| /* in the next SELECT, selection is performed after grouping and aggregation is performed */ |
| SELECT d, e, SUM(f) FROM atable GROUP BY d, e HAVING SUM(f) > 10 |
| /* in the next SELECT, a UNION is performed on two selects from the same table */ |
| SELECT d, e, f FROM atable WHERE d = 3 UNION SELECT a, b, c FROM atable WHERE a = 30 |
| /* in the next SELECT, ordering is performed */ |
| SELECT (a + e) / f AS calc, f AS div FROM atable WHERE a + b = c ORDER BY calc DESC, div NULLS LAST |
| /* in the next SELECT, slicing is performed after ordering */ |
| SELECT * FROM atable WHERE a + b = c ORDER BY a FETCH 5 ROWS ONLY |
| |
| </pre>The next sections discuss various types of tables and |
| operations involved in data access statements.</p> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N1217F"></a>Table</h3> |
| </div> |
| </div> |
| </div> |
| <p>In data access statements, a table can be a database table (or |
| view) or an ephemeral table formed for the duration of the query. Some |
| types of table are <table primary> and can participate in joins |
| without the use of extra parentheses. The BNF in the Table Primary |
| section below lists different types of <table primary>:</p> |
| <p>Tables can also be formed by specifying the values that are |
| contained in them:</p> |
| <p> |
| <code class="literal"><table value constructor> ::= VALUES <row |
| value expression list></code> |
| </p> |
| <p> |
| <code class="literal"><row value expression list> ::= <table row |
| value expression> [ { <comma> <table row value |
| expression> }... ]</code> |
| </p> |
| <p>In the example below a table with two rows and 3 columns is |
| constructed out of some values:</p> |
| <pre class="programlisting">VALUES (12, 14, null), (10, 11, CURRENT_DATE)</pre> |
| <p>When a table is used directly in a UNION or similar operation, |
| the keyword TABLE is used with the name:</p> |
| <p> |
| <code class="literal"><explicit table> ::= TABLE <table or query |
| name></code> |
| </p> |
| <p>In the examples below, all rows of the two tables are included |
| in the union. The keyword TABLE is used in the first example. The two |
| examples below are equivalent.</p> |
| <pre class="programlisting">TABLE atable UNION TABLE anothertable |
| SELECT * FROM atable UNION SELECT * FROM anothertable |
| </pre> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N12199"></a>Query Specification</h3> |
| </div> |
| </div> |
| </div> |
| <p>A query specification is a SELECT statement. It is the most common |
| form of <code class="literal"><derived table></code> . A <code class="literal"><table |
| expression></code> is a base table, a view or any form of allowed |
| derived table. The SELECT statement performs projection, naming, |
| computing or aggregation on the rows of the <code class="literal"><table |
| expression></code> .</p> |
| <p> |
| <code class="literal"><query specification> ::= SELECT [ DISTINCT | |
| ALL ] <select list> <table expression></code> |
| </p> |
| <p> |
| <code class="literal"><select list> ::= <asterisk> | <select |
| sublist> [ { <comma> <select sublist> }... ] |
| </code> |
| </p> |
| <p> |
| <code class="literal"><select sublist> ::= <derived column> | |
| <qualified asterisk> </code> |
| </p> |
| <p> |
| <code class="literal"><qualified asterisk> ::= <asterisked |
| identifier chain> <period> <asterisk></code> |
| </p> |
| <p> |
| <code class="literal"><asterisked identifier chain> ::= <asterisked |
| identifier> [ { <period> <asterisked identifier> }... ] |
| </code> |
| </p> |
| <p> |
| <code class="literal"><asterisked identifier> ::= |
| <identifier></code> |
| </p> |
| <p> |
| <code class="literal"><derived column> ::= <value expression> [ |
| <as clause> ] </code> |
| </p> |
| <p> |
| <code class="literal"><as clause> ::= [ AS ] <column name> |
| </code> |
| </p> |
| <p>The qualifier DISTINCT or ALL apply to the results of the SELECT |
| statement after all other operations have been performed. ALL simply |
| returns the rows, while DISTINCT compares the rows and removes the |
| duplicate ones.</p> |
| <p>Projection is performed by the <code class="literal"><select |
| list></code>.</p> |
| <p>A single <code class="literal"><asterisk></code> means all columns of |
| the <code class="literal"><table expression></code> are included, in the |
| same order as they appear in the <code class="literal"><table |
| expression></code>. An asterisk qualified by a table name means |
| all the columns of the qualifier table name are included.</p> |
| <p>A derived column is a <code class="literal"><value expression></code>, |
| optionally named with the <code class="literal"><as clause></code>. A |
| <code class="literal"><value expression></code> can be many things. Common |
| types include: the name of a column in the <code class="literal"><table |
| expression></code>; an expression based on different columns or |
| constant values; a function call; an aggregate function; a CASE WHEN |
| expression.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N121DF"></a>Table Expression</h3> |
| </div> |
| </div> |
| </div> |
| <p>A table expression is part of the SELECT statement and consists of |
| the FROM clause with optional other clauses that performs selection (of |
| rows) and grouping from the table(s) in the FROM clause.</p> |
| <p> |
| <code class="literal"><table expression> ::= <from clause> [ |
| <where clause> ] [ <group by clause> ] [ <having |
| clause> ]</code> |
| </p> |
| <p> |
| <code class="literal"><from clause> ::= FROM <table reference> [ { |
| <comma> <table reference> }... ]</code> |
| </p> |
| <p> |
| <code class="literal"><table reference> ::= <table primary> | |
| <joined table> </code> |
| </p> |
| <p> |
| <code class="literal"><table primary> ::= <table or query name> |
| [ [ AS ] <correlation name> [ <left paren> <derived |
| column list> <right paren> ] ] </code> |
| </p> |
| <p> |
| <code class="literal">| <derived table> [ AS ] <correlation |
| name> [ <left paren> <derived column list> <right |
| paren> ] </code> |
| </p> |
| <p> |
| <code class="literal">| <lateral derived table> [ AS ] <correlation |
| name> [ <left paren> <derived column list> <right |
| paren> ] </code> |
| </p> |
| <p> |
| <code class="literal">| <collection derived table> [ AS ] |
| <correlation name> [ <left paren> <derived column |
| list> <right paren> ] </code> |
| </p> |
| <p> |
| <code class="literal">| <table function derived table> [ AS ] |
| <correlation name> [ <left paren> <derived column |
| list> <right paren> ] </code> |
| </p> |
| <p> |
| <code class="literal">| <parenthesized joined |
| table></code> |
| </p> |
| <p> |
| <code class="literal"><where clause> ::= WHERE <boolean value |
| expression></code> |
| </p> |
| <p> |
| <code class="literal"><group by clause> ::= GROUP BY [ <set |
| quantifier> ] <grouping element> [ { <comma> <grouping |
| element> }... ]</code> |
| </p> |
| <p> |
| <code class="literal"><having clause> ::= HAVING <boolean value |
| expression></code> |
| </p> |
| <p>The <code class="literal"><from clause></code> contains one or more |
| <code class="literal"><table reference></code> separated by commas. A table |
| reference is often a table or view name or a joined table.</p> |
| <p>The <code class="literal"><where clause></code> filters the rows of |
| the table in the <from clause> and removes the rows for which the |
| search condition is not TRUE.</p> |
| <p>The <code class="literal"><group by clause></code> is a comma |
| separated list of columns of the table in the <code class="literal"><from |
| clause></code> or expressions based on the columns.</p> |
| <p>When a <code class="literal"><group by clause></code> is used, only |
| the columns used in the <code class="literal"><group by clause></code> or |
| expressions used there, can be used in the <code class="literal"><select |
| list></code>, together with any <code class="literal"><aggregate |
| function></code> on other columns. A <code class="literal"><group by |
| clause></code> compares the rows and groups together the rows that |
| have the same values in the columns of the <code class="literal"><group by |
| clause></code>. Then any <code class="literal"><aggregate |
| function></code> in the <code class="literal"><select list></code> is |
| performed on each group, and for each group, a row is formed that |
| contains the values of the columns of the <code class="literal"><group by |
| clause></code> and the values returned from each |
| <code class="literal"><aggregate function>. In the first example below, a |
| simple column reference is used in GROUP BY, while in the second |
| example, an expression is used.</code> |
| <pre class="programlisting">CREATE TABLE atable (a INT, b INT, c INT, d INT, e INT, f INT); |
| SELECT d, e, f FROM atable WHERE a + b = c GROUP BY d, e, f |
| SELECT d + e, SUM(f) FROM atable WHERE a + b = c GROUP BY d + e HAVING SUM(f) > 2 AND d + e > 4 |
| </pre> |
| </p> |
| <p>A <code class="literal"><having clause></code> filters the rows of the |
| table that is formed after applying the <code class="literal"><group by |
| clause></code> using its search condition. The search condition |
| must be an expression based on the expressions in the GROUP BY list or |
| the aggregate functions used.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N12246"></a>Table Primary</h3> |
| </div> |
| </div> |
| </div> |
| <p>Table primary refers to different forms of table reference in the |
| FROM clause.</p> |
| <p>The simplest form of reference is simply a name. This is the name |
| of a table, a view, a transition table in a trigger definition, or a |
| query name specified in the WITH clause of a query expression.</p> |
| <p> |
| <code class="literal"><table or query name> ::= <table name> | |
| <transition table name> | <query name></code> |
| </p> |
| <p>A query expression that is enclosed in parentheses and returns |
| from zero to many rows is a <code class="literal"><table subquery></code>. |
| In a <code class="literal"><derived table></code> the query expression is |
| self contained and cannot reference the columns of other table |
| references.</p> |
| <p> |
| <code class="literal"><derived table> ::= <table |
| subquery></code> |
| </p> |
| <p> |
| <code class="literal">When the word LITERAL is used before a <table |
| subquery>, it means the query expression can reference the columns of |
| other table references that precede it. </code> |
| </p> |
| <p> |
| <code class="literal"><lateral derived table> ::= LATERAL <table |
| subquery></code> |
| </p> |
| <p>UNNEST is similar to LATERAL, but instead of a query expression, |
| and expression that returns an array is used. This expression is |
| converted into a table which has one column that contains the elements |
| of the array, and, if WITH ORDINALITY is used, a second column that |
| contains the index of each element. The array expression usually |
| contains a reference to a column of the table reference preceding the |
| current table reference.</p> |
| <p> |
| <code class="literal"><collection derived table> ::= UNNEST <left |
| paren> <array value expression> <right paren> [ WITH |
| ORDINALITY ]</code> |
| </p> |
| <p>When TABLE is used in this context, it also converts an array |
| value expression to a table, but this array must be the result of a |
| function call. A function that returns a MULTISET can also be used in |
| this context and each row of the multiset is expanded into a row of the |
| table.</p> |
| <p> |
| <code class="literal"><table function derived table> ::= TABLE <left |
| paren> <collection value expression> <right |
| paren></code> |
| </p> |
| <p>The column list that is specified for the table reference must |
| contain names that are unique within the list</p> |
| <p> |
| <code class="literal"><derived column list> ::= <column name |
| list></code> |
| </p> |
| <p> |
| <code class="literal"><column name list> ::= <column name> [ { |
| <comma> <column name> }... ] </code> |
| </p> |
| <p>A parenthesized joined table is simply a joined table contained in |
| parentheses. Joined tables are discussed below.</p> |
| <p> |
| <code class="literal"><parenthesized joined table> ::= <left paren> |
| <parenthesized joined table> <right paren> | <left |
| paren> <joined table> <right paren></code> |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N12278"></a>Joined Table</h3> |
| </div> |
| </div> |
| </div> |
| <p>Joins are operators with two table as the operands, resulting in a |
| third table, called joined table. All join operators are evaluated left |
| to right, therefore, with multiple joins, the table resulting from the |
| first join operator becomes an operand of the next join operator. |
| Parentheses can be used to group sequences of joined tables and change |
| the evaluation order. So if more than two tables are joined together |
| with join operators, the end result is also a joined table. There are |
| different types of join, each producing the result table in a different |
| way.</p> |
| <a name="N1227D" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CROSS JOIN</strong></span> |
| </p> |
| <p>The simplest form of join is CROSS JOIN. The CROSS JOIN of two |
| tables is a table that has all the columns of the first table, followed |
| by all the columns of the second table, in the original order. Each row |
| of the first table is combined with each row of the second table to fill |
| the rows of the new table. If the rows of each table form a set, then |
| the rows of the CROSS JOIN table form the Cartesian product of the rows |
| of the two table operands.</p> |
| <p>The CROSS JOIN can be expressed in two forms. The first form is |
| <code class="literal">A CROSS JOIN B</code>. The second form is <code class="literal">A, |
| B</code>. This type of join is not generally very useful, as it |
| returns large result tables.</p> |
| <a name="N12290" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>UNION JOIN</strong></span> |
| </p> |
| <p>The UNION JOIN has limited use in queries. The result table has |
| the same columns as that of CROSS JOIN. Each row of the first table is |
| extended to the right with nulls and added to the new table. Each row of |
| the second table is extended to the left with nulls and added to the new |
| table. The UNION JOIN is expressed as <code class="literal">A UNION JOIN B</code>. |
| This should not be confused with <code class="literal">A UNION B</code>, which is |
| a set operation. Union join is for special applications and is not |
| commonly used.</p> |
| <a name="N122A1" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>JOIN ... ON</strong></span> |
| </p> |
| <p>The condition join is similar to CROSS JOIN, but a condition is |
| tested for each row of the new table and the row is created only if the |
| condition is true. This form of join is expressed as <code class="literal">A JOIN B |
| ON (<search condition>)</code>.</p> |
| <p>Equijoin is a condition join in which the search condition is an |
| equality condition between on or more pairs of columns from the two |
| table. Equijoin is the most commonly used type of join.</p> |
| <a name="N122B1" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>JOIN ... USING</strong></span> |
| </p> |
| <a name="N122BA" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>NATURAL JOIN</strong></span> |
| </p> |
| <p>Joins with USING or NATURAL keywords joins are similar to an |
| equijoin but they cannot be replaced simply with an equijoin. The new |
| table is formed with the specified or implied shared columns of the two |
| tables, followed by the rest of the columns from each table. In NATURAL |
| JOIN, the shared columns are all the column pairs that have the same |
| name in the first and second table. In JOIN USING, only columns names |
| that are specified by the USING clause are shared. The joins are |
| expressed as <code class="literal">A NATURAL JOIN B</code>, and <code class="literal">A JOIN B |
| USING (<comma separated column name list>)</code>.</p> |
| <p>The columns of the joined table are formed by the following |
| procedures: In JOIN ... USING the shared columns are added to the joined |
| table in the same order as they appear in the column name list. In |
| NATURAL JOIN the shared columns are added to the joined table in the |
| same order as they appear in the first table. In bother forms of join, |
| the non-shared columns of the first table are added in the order they |
| appear in the first table, finally the non-shared columns of the second |
| table are added in the order they appear in the second table.</p> |
| <p>The type of each shared column of the joined table is based on the |
| type of the columns in the original tables. If the original types are |
| not exactly the same, the type of the shared column is formed by type |
| aggregation. Type aggregations selects a type that can represent values |
| of both aggregated types. Simple type aggregation picks one of the |
| types. For example SMALLINT and INTEGER, results in INTEGER, or |
| VARCHAR(10) and VARCHAR(20) results in VARCHAR(20). More complex type |
| aggregation inherits properties from both types. For example DECIMAL(8) |
| and DECIMAL (6,2) results in DECIMAL (8,2).</p> |
| <a name="N122CF" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>OUTER JOIN</strong></span> |
| </p> |
| <p>LEFT, RIGHT and FULL OUTER JOIN</p> |
| <p>The three qualifiers can be added to all types of JOIN apart from |
| CROSS JOIN and UNION JOIN. First the new table is populated with the |
| rows from the original join. If LEFT is specified, all the rows from the |
| first table that did not make it into the new table are extended to the |
| right with nulls and added to the table. If RIGHT is specified, all the |
| rows from the second table that did not make it into the new table are |
| extended to the left with nulls and added to the table. If FULL is |
| specified, the addition of leftover rows is performed from both the |
| first and the second table. These forms are expressed by prefixing the |
| join specification with the given keyword. For example <code class="literal">A LEFT |
| OUTER JOIN B ON (<search condition>)</code> or <code class="literal">A |
| NATURAL FULL OUTER JOIN B</code> or <code class="literal">A FULL OUTER JOIN B |
| USING (<comma separated column name list>)</code>.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N122E5"></a>Selection</h3> |
| </div> |
| </div> |
| </div> |
| <p>Despite the name, selection has nothing to do with the list of |
| columns in a SELECT statement. In fact, it refers to the search |
| condition used to limit the rows that from a result table (selection of |
| rows, not columns). In SQL, simple selection is expressed with a WHERE |
| condition appended to a single table or a joined table. In some cases, |
| this method of selection is the only method available. But when it is |
| possible to perform the selection with join conditions, this is the |
| better method, as it results in a clearer expression of the |
| query.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N122EA"></a>Projection</h3> |
| </div> |
| </div> |
| </div> |
| <p>Projection is selection of the columns from a simple or joined |
| table to form a result table. Explicit projection is performed in the |
| SELECT statement by specifying the select column list. Some form of |
| projection is also performed in JOIN ... USING and NATURAL JOIN.</p> |
| <p>The joined table has columns that are formed according to the |
| rules mentioned above. But in many cases, not all the columns are |
| necessary for the intended operation. If the statement is in the form, |
| SELECT * FROM <joined table>, then all the columns of <joined |
| table> are returned. But normally, the columns to be returned are |
| specified after the SELECT keyword, separated from each other with |
| commas.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N122F1"></a>Computed Columns</h3> |
| </div> |
| </div> |
| </div> |
| <p>In the select list, it is possible to use expressions that |
| reference any columns of <joined table>. Each of these expressions |
| forms a computed column. It is computed for each row of the result |
| table, using the values of the columns of the <joined table> for |
| that row.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N122F6"></a>Naming</h3> |
| </div> |
| </div> |
| </div> |
| <p>Naming is used to hide the original names of tables or table |
| columns and to replace them with new names in the scope of the query. |
| Naming is also used for defining names for computed columns.</p> |
| <a name="N122FB" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>Naming in Joined |
| Table</strong></span> |
| </p> |
| <p>Naming is performed by adding a new name after a table's real name |
| and by adding a list of column names after the new table name. Both |
| table naming and column naming are optional, but table naming is |
| required for column naming. The expression <code class="literal">A [AS] X (<comma |
| separated column name list>)</code> means table A is used in the |
| query expression as table X and its columns are named as in the given |
| list. The original name A, or its original column names, are not visible |
| in the scope of the query. The BNF is given below. The |
| <code class="literal"><correlation name></code> can be the same or different |
| from the name of the table. The <code class="literal"><derived column |
| list></code> is a comma separated list of column names. The degree |
| of this list must be equal to the degree of the table. The column names |
| in the list must be distinct. They can be the same or different from the |
| names of the table's columns.</p> |
| <p> |
| <code class="literal"><table or query name> [ [ AS ] <correlation |
| name> [ <left paren> <derived column list> <right |
| paren> ] ]</code> |
| </p> |
| <p>In the examples below, the columns of the original tables are |
| named (a, b, c, d, e, f). The two queries are equivalent. In the second |
| query, the table and its columns are renamed and the new names are used |
| in the WHERE clauses:</p> |
| <pre class="programlisting">CREATE TABLE atable (a INT, b INT, c INT, d INT, e INT, f INT); |
| SELECT d, e, f FROM atable WHERE a + b = c |
| SELECT x, y, z FROM atable AS t (u, v, w, x, y, z) WHERE u + v = w</pre> |
| <a name="N12316" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>Naming in Select |
| List</strong></span> |
| </p> |
| <p>Naming in the SELECT list logically takes place after naming in |
| the joined table. The new names for columns are not visible in the |
| immediate query expression or query expression. They become visible in |
| the ORDER BY clause and in the result table that is returned to the |
| user. Or if the query expression is used as a derived table in an |
| enclosing query expression.</p> |
| <p>In the example below, the query is on the same table but with |
| column renaming in the Select list. The new names are used in the ORDER |
| BY clause:</p> |
| <pre class="programlisting">SELECT x + y AS xysum, y + z AS yzsum FROM atable AS t (u, v, w, x, y, z) WHERE u + v = w ORDER BY xysum, yzsum</pre> |
| <p>If the names <code class="literal">xysum</code> or <code class="literal">yzsum</code> |
| are not used, the computed columns cannot be referenced in the ORDER BY |
| list.</p> |
| <a name="N1232D" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>Name Resolution</strong></span> |
| </p> |
| <p>In a joined table, if a column name appears in tables on both |
| sides then any reference to the name must use the table name in order to |
| specify which table is being referred to.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N12338"></a>Grouping Operations</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N1233B" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>Grouping Operations</strong></span> |
| </p> |
| <p>Grouping results in the elimination of duplicate rows. A grouping |
| operation is performed after the operations discussed above. A simple |
| form of grouping is performed by the use of DISTINCT after SELECT. This |
| eliminates all the duplicate rows (rows that have the same value in each |
| of their columns when compared to another row). The other form of |
| grouping is performed with the GROUP BY clause. This form is usually |
| used together with aggregation.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N12346"></a>Aggregation</h3> |
| </div> |
| </div> |
| </div> |
| <p>Aggregation is an operation that computes a single value from the |
| values of a column over several rows. The operation is performed with an |
| aggregate function. The simplest form of aggregation is counting, |
| performed by the COUNT function.</p> |
| <p>Other common aggregate functions return the maximum, minimum and |
| average value among the values in different rows.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N1234D"></a>Set Operations</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N12350" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>Set and Multiset |
| Operations</strong></span> |
| </p> |
| <p>While join operations generally result in laterally expanded |
| tables, SET and COLLECTION operations are performed on two tables that |
| have the same degree and result in a table of the same degree. The SET |
| operations are UNION, INTERSECT and EXCEPT (difference). When each of |
| these operations is performed on two tables, the collection of rows in |
| each table and in the result is reduced to a set of rows, by eliminating |
| duplicates. The set operations are then performed on the two tables, |
| resulting in the new table which itself is a set of rows. Collection |
| operations are similar but the tables are not reduced to sets before or |
| after the operation and the result is not necessarily a set, but a |
| collection of rows.</p> |
| <p>The set operations on two tables A and B are: <code class="literal">A UNION |
| [DISTINCT] B</code>, <code class="literal">A INTERSECT [DISTINCT] B</code> and |
| <code class="literal">A EXCEPT [DISTINCT] B</code>. The result table is formed in |
| the following way: The UNION operation adds all the rows from A and B |
| into the new table, but avoids copying duplicate rows. The INTERSECT |
| operation copies only those rows from each table that also exist in the |
| other table, but avoids copying duplicate rows. The EXCEPT operation |
| copies those rows from the first table which do not exist in the second |
| table, but avoids copying duplicate rows.</p> |
| <p>The collection operations are similar to the set operations, but |
| can return duplicate rows. They are <code class="literal">A UNION ALL B</code>, |
| <code class="literal">A INTERSECT ALL B</code> and <code class="literal">A EXCEPT ALL |
| B</code>. The UNION ALL operation adds all the rows from A and B into |
| the new table. The INTERSECT operation copies only those rows from each |
| table that also exist in the other table. If n copies of a rows exists |
| in one table, and m copies in the other table, the number of copies in |
| the result table is the smaller of n and m. The EXCEPT operation copies |
| those rows from the first table which do not exist in the second table. |
| If n copies of a row exist in the first table and m copies in the second |
| table the number of copies in the result table is n-m, or if n < m, |
| then zero.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N12371"></a>Query Expression</h3> |
| </div> |
| </div> |
| </div> |
| <p>A query expression consists of an optional WITH clause and a query |
| expression body. The WITH clause lists one or more named ephemeral |
| tables that can be referenced in the query expression body.</p> |
| <p> |
| <code class="literal"><query expression> ::= [ <with clause> ] |
| <query expression body></code> |
| </p> |
| <p> |
| <code class="literal"><with clause> ::= WITH <with |
| list></code> |
| </p> |
| <p> |
| <code class="literal"><with list> ::= <with list element> [ { |
| <comma> <with list element> }... ] </code> |
| </p> |
| <p> |
| <code class="literal"><with list element> ::= <query name> [ |
| <left paren> <with column list> <right paren> ] AS |
| <left paren> <query expression> <right paren> |
| </code> |
| </p> |
| <p> |
| <code class="literal"><with column list> ::= <column name |
| list></code> |
| </p> |
| <p>A query expression body refers to a table formed by using UNION |
| and other set operations. The query expression body is evaluated from |
| left to right and the INTERSECT operator has precedence over the UNION |
| and EXCEPT operators. A simplified BNF is given below:</p> |
| <p> |
| <code class="literal"><query expression body> ::= <query term> | |
| <query expression body> UNION | EXCEPT [ ALL | DISTINCT ] [ |
| <corresponding spec> ] <query term></code> |
| </p> |
| <p> |
| <code class="literal"><query term> ::= <query primary> | |
| <query term> INTERSECT [ ALL | DISTINCT ] [ <corresponding |
| spec> ] <query term></code> |
| </p> |
| <p> |
| <code class="literal"><query primary> ::= <simple table> | |
| <left paren> <query expression body> [ <order by |
| clause> ] [ <result offset clause> ] [ <fetch first |
| clause> ] <right paren></code> |
| </p> |
| <p> |
| <code class="literal"><simple table> ::= <query specification> | |
| <table value constructor> | <explicit table> <explicit |
| table> ::= TABLE <table or query name></code> |
| </p> |
| <p> |
| <code class="literal"><corresponding spec> ::= CORRESPONDING [ BY |
| <left paren> <column name list> <right paren> |
| ]</code> |
| </p> |
| <p>A <code class="literal"><query term></code> and a <code class="literal"><query |
| primary></code> can be a SELECT statement, an |
| <code class="literal"><explicit table></code>, or a <code class="literal"><table value |
| constructor></code>.</p> |
| <p>The CORRESPONDING clause is optional. If it is not specified, then |
| the <code class="literal"><query term></code> and the <code class="literal"><query |
| primary></code> must have the same number of columns. If |
| CORRESPONDING is specified, the two sides need not have the same number |
| of columns. If no column list is used with CORRESPONDING, then all the |
| column names that are common in the tables on two sides are used in the |
| order in which they appear in the first table. If a columns list is |
| used, it allows you to select only some columns of the tables on the |
| left and right side to create the new table. In the example below the |
| columns named u and v from the two SELECT statements are used to create |
| the UNION table.</p> |
| <p> |
| <pre class="programlisting">SELECT * FROM atable UNION CORRESPONDING BY (u, v) SELECT * FROM anothertable |
| </pre>The type of each column of the query expression is determined |
| by combining the types of the corresponding columns from the two |
| participating tables.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N123B0"></a>Ordering</h3> |
| </div> |
| </div> |
| </div> |
| <p>When the rows of the result table have been formed, it is possible |
| to specify the order in which they are returned to the user. The ORDER |
| BY clause is used to specify the columns used for ordering, and whether |
| ascending or descending ordering is used. It can also specify whether |
| NULL values are returned first or last.</p> |
| <pre class="programlisting">SELECT x + y AS xysum, y + z AS yzsum FROM atable AS t (u, v, w, x, y, z) WHERE u + v = w ORDER BY xysum NULLS LAST, yzsum NULLS FIRST</pre> |
| <p>The ORDER BY clause specifies one or more <code class="literal"><value |
| expressions></code>. The list of rows is sorted according to the |
| first <code class="literal"><value expression></code>. When some rows are |
| sorted equal then they are sorted according to the next |
| <code class="literal"><value expression></code> and so on.</p> |
| <p> |
| <code class="literal"><order by clause> ::= ORDER BY <sort |
| specification> [ { <comma> <sort specification> }... |
| ]</code> |
| </p> |
| <p> |
| <code class="literal"><sort specification> ::= <value expression> [ |
| ASC | DESC ] [ NULLS FIRST | NULLS LAST ]</code> |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N123C8"></a>Slicing</h3> |
| </div> |
| </div> |
| </div> |
| <p>A different form of limiting the rows can be performed on the |
| result table after it has been formed according to all the other |
| operations (selection, grouping, ordering etc.). This is specified by |
| the FETCH ... ROWS and OFFSET clauses of a SELECT statement. In this |
| form, the specified OFFSET rows are removed from start of the table, |
| then up to the specified FETCH rows are kept and the rest of the rows |
| are discarded.</p> |
| <p> |
| <code class="literal"><result offset clause> ::= OFFSET <offset row |
| count> { ROW | ROWS } </code> |
| </p> |
| <p> |
| <code class="literal"><fetch first clause> ::= FETCH { FIRST | NEXT } [ |
| <fetch first row count> ] { ROW | ROWS } ONLY</code> |
| </p> |
| <p> |
| <code class="literal"><limit clause> ::= LIMIT [ <fetch first row |
| count> ]</code> |
| </p> |
| <p>A slicing operation takes the result set that has been already |
| processed and ordered. It then discards the specified number of rows |
| from the start of the result set and returns the specified number of |
| rows after the discarded rows.</p> |
| <pre class="programlisting">SELECT a, b FROM atable WHERE d < 5 ORDER BY absum OFFSET 3 FETCH 2 ROWS ONLY |
| SELECT a, b FROM atable WHERE d < 5 ORDER BY absum OFFSET 3 LIMIT 2 /* alternative keyword */ </pre> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="N123DA"></a>Data Change Statements</h2> |
| </div> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N123DD"></a>Delete Statement</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N123E0" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DELETE FROM</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>delete statement: searched</em></span> |
| </p> |
| <p> |
| <code class="literal"><delete statement: searched> ::= DELETE FROM |
| <target table> [ [ AS ] <correlation name> ] [ WHERE |
| <search condition> ]</code> |
| </p> |
| <p>Delete rows of a table. The search condition is a |
| <code class="literal"><boolean value expression></code> that is evaluated |
| for each row of the table. If the condition is true, the row is deleted. |
| If the condition is not specified, all the rows of the table are |
| deleted. In fact, an implicit SELECT is performed in the form of |
| <code class="literal">SELECT * FROM <target table> [ WHERE <search |
| condition>]</code> and the selected rows are deleted. When used in |
| JDBC, the number of rows returned by the implicit SELECT is returned as |
| the update count.</p> |
| <p>If there are FOREIGN KEY constraints on other tables that |
| reference the subject table, and the FOREIGN KEY constraints have |
| referential actions, then rows from those other tables that reference |
| the deleted rows are either deleted, or updated, according to the |
| specified referential actions.</p> |
| <p>In the second example below the rows that have the maximum |
| value for column A are deleted;</p> |
| <pre class="programlisting">DELETE FROM T WHERE C > 5 |
| DELETE FROM T AS TT WHERE TT.A = (SELECT MAX(A) FROM T) |
| </pre> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N123FD"></a>Truncate Statement</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N12400" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>TRUNCATE TABLE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>truncate table statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><truncate table statement> ::= TRUNCATE TABLE |
| <target table> [ <identity column restart option> |
| ]</code> |
| </p> |
| <p> |
| <code class="literal"><identity column restart option> ::= CONTINUE |
| IDENTITY | RESTART IDENTITY</code> |
| </p> |
| <p>Delete all rows of a table without firing its triggers. This |
| statement can only be used on base tables (not views). If the table is |
| referenced in a FOREIGN KEY constraint, the statement causes an |
| exception. Triggers defined on the table are not executed with this |
| statement. The default for <code class="literal"><identity column restart |
| option></code> is <code class="literal">CONTINUE IDENTITY</code>. This means |
| no change to the IDENTITY sequence of the table. If <code class="literal">RESTART |
| IDENTITY</code> is specified, then the sequence is reset to its start |
| value.</p> |
| <p>TRUNCATE is faster than ordinary DELETE. The TRUNCATE statement |
| is an SQL Standard data change statement, therefore it is performed |
| under transaction control and can be rolled back if the connection is |
| not in the auto-commit mode.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N1241F"></a>Insert Statement</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N12422" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>INSERT INTO</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>insert statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><insert statement> ::= INSERT INTO <target |
| table> <insert columns and source></code> |
| </p> |
| <p> |
| <code class="literal"><insert columns and source> ::= <from |
| subquery> | <from constructor> | <from |
| default></code> |
| </p> |
| <p> |
| <code class="literal"><from subquery> ::= [ <left paren> |
| <insert column list> <right paren> ] [ <override |
| clause> ] <query expression></code> |
| </p> |
| <p> |
| <code class="literal"><from constructor> ::= [ <left paren> |
| <insert column list> <right paren> ] [ <override |
| clause> ] <contextually typed table value |
| constructor></code> |
| </p> |
| <p> |
| <code class="literal"><override clause> ::= OVERRIDING USER VALUE | |
| OVERRIDING SYSTEM VALUE</code> |
| </p> |
| <p> |
| <code class="literal"><from default> ::= DEFAULT |
| VALUES</code> |
| </p> |
| <p> |
| <code class="literal"><insert column list> ::= <column name |
| list></code> |
| </p> |
| <p>Insert new rows in a table. An INSERT statement inserts one or |
| more rows into the table.</p> |
| <p>The special form, <code class="literal">INSERT INTO <target table> |
| DEFAULT VALUES</code> can be used with tables which have a default |
| value for each column.</p> |
| <p>With the other forms of INSERT, the optional |
| <code class="literal">(<insert column list>)</code> specifies to which |
| columns of the table the new values are assigned.</p> |
| <p>In one form, the inserted values are from a <code class="literal"><query |
| expression></code> and all the rows that are returned by the |
| <code class="literal"><query expression></code> are inserted into the table. |
| If the <code class="literal"><query expression></code> returns no rows, |
| nothing is inserted.</p> |
| <p>In the other form, a comma separated list of values called |
| <code class="literal"><contextually typed table value constructor></code> is |
| used to insert one or more rows into the table. This list is |
| contextually typed, because the keywords NULL and DEFAULT can be used |
| for the values that are assigned to each column of the table. The |
| keyword DEFAULT means the default value of the column and can be used |
| only if the target column has a default value or is an IDENTITY or |
| GENERATED column of the table.</p> |
| <p>The <code class="literal"><override clause></code> must be used |
| when a value is explicitly assigned to a column that has been defined as |
| GENERATED ALWAYS AS IDENTITY. The clause, OVERRIDE SYSTEM VALUE means |
| the provided values are used for the insert, while OVERRIDING USER VALUE |
| means the provided values are simply ignored and the values generated by |
| the system are used instead.</p> |
| <p>An array can be inserted into a column of the array type by |
| using literals, by specifying a parameter in a prepared statement or an |
| existing array returned by query expression. The last example below |
| inserts an array.</p> |
| <p>The rows that are inserted into the table are checked against |
| all the constraints that have been declared on the table. The whole |
| INSERT operation fails if any row fails to inserted due to constraint |
| violation. Examples:</p> |
| <pre class="programlisting">INSERT INTO T DEFAULT VALUES /* all columns of T have DEFAULT clauses */ |
| INSERT INTO T (SELECT * FROM Z) /* table Z has the same columns as table T */ |
| INSERT INTO T (A,B) VALUES ((1,2),(3,NULL), (DEFAULT,6)) /* three rows are inserted into table T */ |
| INSERT INTO T VALUES 3, ARRAY['hot','cold'] |
| </pre> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N1246A"></a>Update Statement</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N1246D" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>UPDATE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>update statement: searched</em></span> |
| </p> |
| <p> |
| <code class="literal"><update statement: searched> ::= UPDATE |
| <target table> [ [ AS ] <correlation name> ] SET <set |
| clause list> [ WHERE <search condition> ]</code> |
| </p> |
| <p>Update rows of a table. An UPDATE statement selects rows from |
| the <code class="literal"><target table></code> using an implicit SELECT |
| statement formed in the following manner:</p> |
| <p> |
| <code class="literal">SELECT * FROM <target table> [ [ AS ] |
| <correlation name> ] [ WHERE <search condition> |
| ]</code> |
| </p> |
| <p>Then it applies the <code class="literal">SET <set clause |
| list></code> expression to each selected row.</p> |
| <p>If the implicit SELECT returns no rows, no update takes place. |
| When used in JDBC, the number of rows returned by the implicit SELECT is |
| returned as the update count.</p> |
| <p>If there are FOREIGN KEY constraints on other tables that |
| reference the subject table, and the FOREIGN KEY constraints have |
| referential actions, then rows from those other tables that reference |
| the updated rows are updated, according to the specified referential |
| actions.</p> |
| <p>The rows that are updated are checked against all the |
| constraints that have been declared on the table. The whole UPDATE |
| operation fails if any row violates any constraint.</p> |
| <a name="N1248F" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>set clause list</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>set clause list</em></span> |
| </p> |
| <p> |
| <code class="literal"><set clause list> ::= <set clause> [ { |
| <comma> <set clause> }... ]</code> |
| </p> |
| <p> |
| <code class="literal"><set clause> ::= <multiple column |
| assignment> | <set target> <equals operator> <update |
| source></code> |
| </p> |
| <p> |
| <code class="literal"><multiple column assignment> ::= <set target |
| list> <equals operator> <assigned |
| row></code> |
| </p> |
| <p> |
| <code class="literal"><set target list> ::= <left paren> <set |
| target> [ { <comma> <set target> }... ] <right |
| paren></code> |
| </p> |
| <p> |
| <code class="literal"><assigned row> ::= <contextually typed row |
| value expression></code> |
| </p> |
| <p> |
| <code class="literal"><set target> ::= <column |
| name></code> |
| </p> |
| <p> |
| <code class="literal"><update source> ::= <value expression> | |
| <contextually typed value specification></code> |
| </p> |
| <p>Specify a list of assignments. This is used in UPDATE, MERGE |
| and SET statements to assign values to a scalar or row target.</p> |
| <p>Apart from setting a whole target to a value, a SET statement |
| can set individual elements of an array to new values. The last example |
| below shows this form of assignment to the array in the column named |
| B.</p> |
| <p>In the examples given below, UPDATE statements with single and |
| multiple assignments are shown. Note in the third example, a SELECT |
| statement is used to provide the update values for columns A and C, |
| while the update value for column B is given separately. The SELECT |
| statement must return exactly one row . In this example the SELECT |
| statement refers to the existing value for column C in its search |
| condition.</p> |
| <pre class="programlisting">UPDATE T SET A = 5 WHERE ... |
| UPDATE T SET (A, B) = (1, NULL) WHERE ... |
| UPDATE T SET (A, C) = (SELECT X, Y FROM U WHERE Z = C), B = 10 WHERE ... |
| UPDATE T SET A = 3, B[3] = 'warm' |
| </pre> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N124B8"></a>Merge Statement</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N124BB" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>MERGE INTO</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>merge statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><merge statement> ::= MERGE INTO <target |
| table> [ [ AS ] <merge correlation name> ] USING <table |
| reference> ON <search condition> <merge operation |
| specification></code> |
| </p> |
| <p> |
| <code class="literal"><merge correlation name> ::= <correlation |
| name></code> |
| </p> |
| <p> |
| <code class="literal"><merge operation specification> ::= <merge |
| when clause>...</code> |
| </p> |
| <p> |
| <code class="literal"><merge when clause> ::= <merge when matched |
| clause> | <merge when not matched clause></code> |
| </p> |
| <p> |
| <code class="literal"><merge when matched clause> ::= WHEN MATCHED |
| THEN <merge update specification></code> |
| </p> |
| <p> |
| <code class="literal"><merge when not matched clause> ::= WHEN NOT |
| MATCHED THEN <merge insert specification></code> |
| </p> |
| <p> |
| <code class="literal"><merge update specification> ::= UPDATE SET |
| <set clause list></code> |
| </p> |
| <p> |
| <code class="literal"><merge insert specification> ::= INSERT [ |
| <left paren> <insert column list> <right paren> ] [ |
| <override clause> ] VALUES <merge insert value |
| list></code> |
| </p> |
| <p> |
| <code class="literal"><merge insert value list> ::= <left paren> |
| <merge insert value element> [ { <comma> <merge insert |
| value element> }... ] <right paren></code> |
| </p> |
| <p> |
| <code class="literal"><merge insert value element> ::= <value |
| expression> | <contextually typed value |
| specification></code> |
| </p> |
| <p>Update rows, or insert new rows into the <code class="literal"><target |
| table></code>. The MERGE statement uses a second table, specified |
| by <code class="literal"><table reference></code>, to determine the rows to |
| be updated or inserted. It is possible to use the statement only to |
| update rows or to insert rows, but usually both update and insert are |
| specified.</p> |
| <p>The <code class="literal"><search condition></code> matches each |
| row of the <code class="literal"><table reference></code> with each row of |
| the <code class="literal"><target table></code>. If the two rows match then |
| the UPDATE clause is used to update the matching row of the target |
| table. Those rows of <code class="literal"><table reference></code> that |
| have no matching rows are then used to insert new rows into the |
| <code class="literal"><target table></code>. Therefore, a MERGE statement |
| can update between 0 and all the rows of the <code class="literal"><target |
| table></code> and can insert between 0 and the number of the rows |
| in <code class="literal"><table reference></code> into the |
| <code class="literal"><target table></code>. If any row in the |
| <code class="literal"><target table></code> matches more than one row in |
| <code class="literal"><table reference></code> a cardinality error is |
| raised. On the other hand, several rows in the <code class="literal"><target |
| table></code> can matches a single row in <code class="literal"><table |
| reference></code> without any error. The constraints and |
| referential actions specified on the database tables are enforced the |
| same way as for an update and an insert statement.</p> |
| <p>The MERGE statement can be used with only the WHEN NOT MATCHED |
| clause as a conditional INSERT statement that inserts a row if no |
| existing rows match a condition.</p> |
| <p>In the first example below, the table originally contains two |
| rows for different furniture. The <code class="literal"><table |
| reference></code> is the <code class="literal">(VALUES(1, 'conference table'), |
| (14, 'sofa'), (5, 'coffee table'))</code> expression, which evaluates |
| to a table with 3 rows. When the x value for a row matches an existing |
| row, then the existing row is updated. When the x value does not match, |
| the row is inserted. Therefore one row of table t is updated from |
| 'dining table' to 'conference table', and two rows are inserted into |
| table t. The second example uses a SELECT statement as the source of the |
| values for the MERGE.</p> |
| <p>In the third example, a new row in inserted into the table only |
| when the primary key for the new row does not exist. This example uses |
| parameters and should be executed as a JDBC PreparedStatement.</p> |
| <pre class="programlisting">CREATE TABLE t (id INT PRIMARY KEY, description VARCHAR(100)) |
| INSERT INTO t VALUES (1, 'dining table'), (2, 'deck chair') |
| MERGE INTO t USING (VALUES(1, 'conference table'), (14, 'sofa'), (5, 'coffee table')) |
| AS vals(x,y) ON t.id = vals.x |
| WHEN MATCHED THEN UPDATE SET t.description = vals.y |
| WHEN NOT MATCHED THEN INSERT VALUES vals.x, vals.y |
| |
| MERGE INTO t USING (SELECT * FROM tt WHERE acol = 2) AS vals(x,y) ON t.id = vals.x |
| WHEN MATCHED THEN UPDATE SET t.description = vals.y |
| WHEN NOT MATCHED THEN INSERT VALUES vals.x, vals.y |
| |
| MERGE INTO t USING (VALUES(CAST(? AS INT))) AS vals(x) ON t.id = vals.x |
| WHEN NOT MATCHED THEN INSERT VALUES vals.x, ? |
| </pre> |
| </div> |
| </div> |
| </div> |
| <HR xmlns:xi="http://www.w3.org/2001/XInclude"> |
| <P xmlns:xi="http://www.w3.org/2001/XInclude" class="svnrev">$Revision: 3601 $</P> |
| <div class="navfooter"> |
| <hr> |
| <table summary="Navigation footer" width="100%"> |
| <tr> |
| <td align="left" width="40%"><a accesskey="p" href="accesscontrol-chapt.html"><img src="../images/db/prev.png" alt="Prev"></a> </td><td align="center" width="20%"> </td><td align="right" width="40%"> <a accesskey="n" href="sqlroutines-chapt.html"><img src="../images/db/next.png" alt="Next"></a></td> |
| </tr> |
| <tr> |
| <td valign="top" align="left" width="40%">Chapter 6. Access Control </td><td align="center" width="20%"><a accesskey="h" href="index.html"><img src="../images/db/home.png" alt="Home"></a></td><td valign="top" align="right" width="40%"> Chapter 8. SQL-Invoked Routines</td> |
| </tr> |
| </table> |
| </div> |
| </body> |
| </html> |
