<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.subject" content="SQL" />
<meta name="dc.subject" content="SQL Reference" />
<meta name="dc.subject" content="Select" />
<meta name="dc.subject" content="Update" />
<meta name="dc.subject" content="delete" />
<meta name="dc.subject" content="Select Statement" />
<meta name="dc.subject" content="SQL Syntax" />
<meta name="dc.subject" content="Syntax" />
<meta name="dc.title" content="8. SQL Reference" />
<meta name="dc.subject" content="8. SQL Reference" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="sqlreference.html" title="Chapter Contents" />
<link rel="prev" href="updatestmt.html" title="UPDATE Statement" />
<link rel="next" href="COMMIT_ROLLBACK.html" title="COMMIT WORK, ROLLBACK WORK Statement" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>8. SQL Reference</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="SQL; SQL Reference; Select; Update; delete; Select Statement; SQL Syntax; Syntax; " />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="SELECTSTMT" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>8. SQL Reference</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sqlreference.html">Chapter Contents</a> | <a class="link" href="updatestmt.html" title="UPDATE Statement">Prev</a> | <a class="link" href="COMMIT_ROLLBACK.html" title="COMMIT WORK, ROLLBACK WORK Statement">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="sqlreference.html">SQL Reference</a>
</div>
<br />
<div>
<a href="sqlrefDATATYPES.html">Datatypes</a>
</div>
<div>
<a href="udt.html">User Defined Types</a>
</div>
<div>
<a href="sqlrefxmldatatype.html">XML Column Type</a>
</div>
<div>
<a href="catidentifiers.html">Identifier Case & Quoting</a>
</div>
<div>
<a href="wideidentifiers.html">Wide Character Identifiers</a>
</div>
<div>
<a href="QUALIFIEDNAMES.html">Qualified Names</a>
</div>
<div>
<a href="litsbraceescs.html">Literals, Brace Escapes</a>
</div>
<div>
<a href="CREATETABLE.html">CREATE TABLE Statement</a>
</div>
<div>
<a href="DROPTABLE.html">DROP TABLE Statement</a>
</div>
<div>
<a href="CREATEINDEX.html">CREATE INDEX Statement</a>
</div>
<div>
<a href="DROPINDEX.html">DROP INDEX Statement</a>
</div>
<div>
<a href="ALTERTABLE.html">ALTER TABLE Statement</a>
</div>
<div>
<a href="CREATEVIEW.html">CREATE VIEW Statement</a>
</div>
<div>
<a href="CREATEXMLSCHEMA.html">CREATE XML SCHEMA Statement</a>
</div>
<div>
<a href="DROPXMLSCHEMA.html">DROP XML SCHEMA Statement</a>
</div>
<div>
<a href="sequenceobjects.html">Sequence Objects</a>
</div>
<div>
<a href="insertSTMT.html">INSERT Statement</a>
</div>
<div>
<a href="updatestmt.html">UPDATE Statement</a>
</div>
<div class="selected">
<a href="SELECTSTMT.html">SELECT Statement</a>
<div>
<a href="#selectsyntax" title="Syntax">Syntax</a>
<a href="#descrip" title="Description">Description</a>
<a href="#ASDEC" title="Column Aliasing - AS Declaration">Column Aliasing - AS Declaration</a>
<a href="#joinexamples" title="Join examples">Join examples</a>
<a href="#ordergrping" title="Ordering and Grouping">Ordering and Grouping</a>
<a href="#derivedtables" title="Derived Tables">Derived Tables</a>
<a href="#qryexpressions" title="Query Expressions">Query Expressions</a>
<a href="#LikePredicate" title="LIKE Predicate & Search Patterns">LIKE Predicate & Search Patterns</a>
<a href="#topselectoption" title="The TOP SELECT Option">The TOP SELECT Option</a>
<a href="#caseandco" title="CASE, NULLIF, COALESCE, CAST Value Expressions">CASE, NULLIF, COALESCE, CAST Value Expressions</a>
<a href="#selbreackup" title="SELECT BREAKUP">SELECT BREAKUP</a>
</div>
</div>
<div>
<a href="COMMIT_ROLLBACK.html">COMMIT WORK, ROLLBACK WORK Statement</a>
</div>
<div>
<a href="CHECKPOINT.html">CHECKPOINT, SHUTDOWN Statement</a>
</div>
<div>
<a href="spasviewsandtables.html">Stored Procedures as Views & Derived Tables</a>
</div>
<div>
<a href="GRANT.html">GRANT, REVOKE Statement</a>
</div>
<div>
<a href="SETstmt.html">SET Statement</a>
</div>
<div>
<a href="anytimequeries.html">Anytime Queries</a>
</div>
<div>
<a href="besteffortunion.html">Best Effort Union</a>
</div>
<div>
<a href="aggregates.html">Standard and User-Defined Aggregate Functions</a>
</div>
<div>
<a href="sqloptimizer.html">Virtuoso SQL Optimization</a>
</div>
<div>
<a href="sqlinverse.html">SQL Inverse Functions</a>
</div>
<div>
<a href="GRAMMAR.html">SQL Grammar</a>
</div>
<div>
<a href="BITMAPINDICES.html">Bitmap Indices</a>
</div>
<div>
<a href="transitivityinsQL.html">Transitivity in SQL</a>
</div>
<div>
<a href="sqlreffastphrasematch.html">Fast Phrase Match Processor</a>
</div>
<br />
</div>
<div id="text">
<a name="SELECTSTMT" />
<h2>8.19. SELECT Statement</h2>
<a name="selectsyntax" />
<h3>8.19.1. Syntax</h3>
<p />
<div>
<pre class="programlisting">
< select statement: single row > ::= SELECT [ < set quantifier > ] < select list >
INTO < select target list >
< table expression >
< select target list > ::=
< target specification > [ { < comma > < target specification > }... ]
< query expression > ::=
< non-join query expression >
| < joined table >
< non-join query expression > ::=
< non-join query term >
| < query expression > UNION [ ALL ]
[ < corresponding spec > ] < query term >
| < query expression > EXCEPT [ ALL ]
[ < corresponding spec > ] < query term >
< non-join query term > ::=
< non-join query primary >
| < query term > INTERSECT [ ALL ]
[ < corresponding spec > ] < query primary >
< non-join query primary > ::=
< simple table >
| < left parent > < non-join query expression > < right parent >
< simple table > ::=
< query specification >
| < table value constructor >
| < explicit table >
< query specification > ::=
SELECT [ < set quantifier > ] < select list > < table expression >
< select list > ::=
< asterisk >
| < select sublist > [ { < comma > < select sublist > }... ]
< select sublist > ::=
< derived column >
| < qualifier > < period > < asterisk >
< derived column > ::= < value expression > [ < as clause > ]
< as clause > ::= [ AS ] < column name >
< table expression > ::=
< from clause >
[ < where clause > ]
[ < group by clause > ]
[ < having clause > ]
< from clause > ::= FROM < table reference >
[ { < comma > < table reference > }... ]
< table reference > ::=
< table name > [ [ AS ] < correlation name >
[ < left parent > < derived column list > < right parent > ] ]
| < derived table > [ AS ] < correlation name >
[ < left parent > < derived column list > < right parent > ]
| < joined table >
< derived column list > ::= < column name list >
< derived table > ::= < table subquery >
< table subquery > ::= < subquery >
< joined table > ::=
< cross join >
| < qualified join >
| < left parent > < joined table > < right parent >
< cross join > ::=
< table reference > CROSS JOIN < table reference >
< qualified join > ::=
< table reference > [ NATURAL ] [ < join type > ] JOIN
< table reference > [ < join specification > ]
< join type > ::=
INNER
| < outer join type > [ OUTER ]
| UNION
< outer join type > ::=
LEFT
| RIGHT
| FULL
< join specification > ::=
< join condition >
| < named columns join >
< join condition > ::= ON < search condition >
< named columns join > ::=
USING < left parent > < join column list > < right parent >
< join column list > ::= < column name list >
< where clause > ::= WHERE < search condition >
< group by clause > ::=
GROUP BY < grouping column reference list >
< grouping column reference list > ::=
< grouping column reference >
[ { < comma > < grouping column reference > }... ]
< grouping column reference > ::=
< column reference > [ < collate clause > ]
</pre>
</div>
<p />
<br />
<a name="descrip" />
<h3>8.19.2. Description</h3>
<p>
The SELECT statement is the principal means of information retrieval in SQL.
A SELECT can retrieve information from one or more tables with arbitrary search criteria.
SELECT's can also be nested to have the output of one serve as an input or search condition for another.
Several SELECT's can be combined into one query with the UNION, INTERSECTION and EXCEPT operators.
</p>
<p>
The SELECT syntax consists of the following parts:
</p>
<div>
<pre class="programlisting">
SELECT [DISTINCT] scalar_exp {, scalar_exp}
FROM table {, table}
WHERE < search condition >
GROUP BY < column list >
HAVING < search condition >
ORDER BY < ordering spec list >
FOR UPDATE
</pre>
</div>
<p>
All parts are optional. If one or more of the clauses
appear they must appear in the above order. All parts do not need to be specified,
e.g. SELECT A FROM T FOR UPDATE is valid but SELECT A FROM T ORDER BY a WHERE < < 10 is not.
</p>
<div class="note">
<div class="notetitle">Note:</div>
<p>A select without a FROM clause is allowed.
</p>
<p>
This is useful for returning values of expressions to the client. Such a select always returns one row,
with the values listed as columns. Typically only useful from interactive SQL.
</p>
<p>
Example:
</p>
<p>
select 1 + 2 as three;
</p>
</div>
<p>
A table reference in the FROM clause can either be a simple table name, another SELECT
expression of the form described above or a join expression.
A SELECT inside a FROM is called a derived table. This means that the rows selected by
the derived table expression are treated as if they constituted a table. This is similar to a
VIEW reference and a derived table can be thought of as an unnamed in-line VIEW declaration.
</p>
<p>
A join expression combines table references, which are either simple, derived or joined tables
themselves into different joins.
</p>
<p>
A join is an operation that retrieves for each row of one table zero
or more rows from another table. The join condition specifies how the rows are matched.
The result of a join is a set of rows containing selected columns from both joined tables.
Joins are by default so called INNER joins, which means that for a row to be in the result
there must be a row matching the left table in the right table as specified by the join
condition. An OUTER join is a join that will produce a result row even if there is no row
in the right table for the row in the left table. The columns that would have come from the
right table are then just set to NULL's.
</p>
<div>
<pre class="programlisting">
table_ref ::=
< table name > [< correlation name >]
| / query expression ) < correlation name >
| < table ref > < [NATURAL] join > < table ref > < join condition >
join ::=
< empty >
| CROSS
| INNER
| LEFT [OUTER]
| RIGHT [OUTER]
join condition ::=
< empty >
| ON < search condition >
| USING '(' < column > {, column} ')'
</pre>
</div>
<p>
The < correlation name > is an identifier that is used to identify the table in a column
reference if the same table appears many times in the query expression, e.g. is joined
with itself.
</p>
<p>
The CROSS join has no join condition. This means that for each row in the left table all
rows in the right table are included in the result.
</p>
<br />
<a name="ASDEC" />
<h3>8.19.3. Column Aliasing - AS Declaration</h3>
<p>
Virtuoso supports the AS operator in the selection list of a SELECT
statement. This notation allows declaring a name and optionally a type
and precision for a statement's output column.
</p>
<p>
The syntax is:
</p>
<div>
<pre class="screen">
as_exp:
Scalar_exp AS NAME opt_data_type
</pre>
</div>
<p>
For instance, one can write:
</p>
<div>
<pre class="programlisting">
SELECT COUNT (*) AS NUMBEROFROWS FROM XX;
SELECT COUNT (*) AS NUMBEROFROWS INTEGER (2) FROM XX;
SELECT CONCATENATE (COL1, COL2) AS RESULTSTRING VARCHAR (50) FROM XX;
</pre>
</div>
<p>
The AS expression tells the client to return specified values in the
SQLDescribeCol, SQLColAttribute or equivalent calls. The semantics of
statements is not otherwise affected. An AS expression can appear
anywhere a scalar expression can but the only place where it has an
effect is the selection list of a SELECT statement.
</p>
<p>
If a data type is given and contains a precision, that precision is
returned to the client as the precision of the column in question.
</p>
<br />
<a name="joinexamples" />
<h3>8.19.4. Join examples</h3>
<p>
The following three statements produce an identical result.
</p>
<div>
<pre class="programlisting">
select Orders.OrderID, ProductID
from Orders natural join Order_Details using (OrderID)
select Orders.OrderID, ProductID
from Orders join Order_Details on Orders.OrderID = Order_Details.OrderID
select Orders.OrderID, ProductID
from Orders, Order_Details where Orders.OrderID = Order_Details.OrderID
</pre>
</div>
<div class="note">
<div class="notetitle">Note:</div>
<p>In all these cases if there exists no Order_Details row matching the Orders row there
will no no result row corresponding to the Orders row. An outer join can can be used to also
retrieve left table records for which there is no matching right table record.
</p>
</div>
<div>
<pre class="programlisting">
select Orders.OrderID, ProductID
from Orders natural left outer join Order_Details using (OrderID)
</pre>
</div>
<p>will produce a result identical to the above sample if for each Orders row there is at least
one Order_Details row. If there is none however, the OrderID column from Orders will appear
together with a NULL ProductID from the non-existent Order_Details.
</p>
<p>
A right outer join is like a left outer join with the left and right tables reversed.
</p>
<br />
<a name="ordergrping" />
<h3>8.19.5. Ordering and Grouping</h3>
<p>
The result rows of a query can be ordered based on their column values.
The ORDER BY phrase allows specifying an ascending or descending sort order for a
any column. The SQL interpreter will use an index if there is an index whose order
reflects the order in the ORDER BY clause. If there is no appropriate index or if
ascending and descending order is combined for columns of the same table the SQL
interpreter will first evaluate the query and then sort the results before returning
them.
</p>
<div class="tip">
<div class="tiptitle">See:</div>
<p>Optimizations below for more information.</p>
</div>
<div>
<pre class="programlisting">
select * from Employees order by BirthDate;
</pre>
</div>
<p>
will list all employees, oldest first, in ascending order of birth date.
</p>
<p>
The GROUP BY clause allows computing functions over repeating groups. Without
the GROUP by clause set functions (AVG, MIN, MAX, SUM, COUNT) may not be mixed
with normal columns in a selection list.
If set functions and columns are mixed, all the columns must appear in the GROUP BY section.
Such a query will produce as many rows as there are distinct value combinations of the grouping columns.
The set functions will be computed for each distinct column combination.
</p>
<div>
<pre class="programlisting">
select OrderID, sum (UnitPrice * Quantity)
from Order_Details group by OrderID
having sum (UnitPrice * Quantity) > 5000 order by 2 desc;
</pre>
</div>
<p>
Produces the OrderID and total value of the order in decreasing order of order value.
The HAVING clause specifies that only orders with a value > 5000 will be counted. Note
that the sum expression in having must be written identically to the same expression
in the SELECT left.
</p>
<p>
The 2 in the order by refers to the second column of the select, which has no name,
it being a function reference.
</p>
<a name="ordergrping_cube" />
<h4>8.19.5.1. CUBE and ROLLUP</h4>
<p>Virtuoso database offers the tool which increase efficiency of SQL summary queries and
simplify such operations. The options ROLLUP and CUBE in the GROUP BY allow creating more comprehensive
summary operations. The result of CUBE and ROLLUP are result sets which could be produced in other
way only with additional coding and queries. </p>
<p>The ROLLUP and CUBE extends the result set of GROUP BY. The ROLLUP builds consequence
of subtotal aggregates on every queried level including the grand total. The CUBE is an extension of ROLLUP.
It builds all possible subtotal aggregates combination for given GROUP BY.</p>
<p>Here are the examples of ROLLUP and CUBE usage:</p>
<div>
<pre class="programlisting">
select j, grouping (j), k, grouping (k), t, grouping (t), sum (i) from TABLE1 group by rollup (j,k,t);
select j, grouping (j), k, grouping (k), t, grouping (t), sum (i) from ROLLUP1 group by cube (j,k,t);
</pre>
</div>
<p>the grouping(c1) procedure returns "1" if the column "c1" is
not in the dynamic GROUP BY set, and returns "0" otherwise.</p>
<p>The result set of:</p>
<div>
<pre class="programlisting">
select j, k, t, sum (i) from TABLE1 group by rollup (j,k,t) ;
</pre>
</div>
<p>is equivalent of accumulate result sets of:</p>
<div>
<pre class="programlisting">
select j, k, t, sum (i) from TABLE1 group by j,k,t;
select NULL, k, t, sum (i) from TABLE1 group by k,t;
select NULL, NULL, t, sum (i) from TABLE1 group t;
select NULL, NULL, NULL, sum (i) from TABLE1;
</pre>
</div>
<p>The result set of:</p>
<div>
<pre class="programlisting">
select t,s, sum (i) from TABLE1 group by cube (t,s);
</pre>
</div>
<p>is equivalent of accumulate result set of:</p>
<div>
<pre class="programlisting">
select t,s, sum (i) from TABLE1 group by t,s;
select t,NULL, sum (i) from TABLE1 group by t;
select s,NULL, sum (i) from TABLE1 group by s;
select NULL,NULL, sum (i) from TABLE1;
</pre>
</div>
<br />
<br />
<a name="derivedtables" />
<h3>8.19.6. Derived Tables</h3>
<p>
A SELECT expression may be used in the place of a table in a FROM clause.
This provides control over where DISTINCT and ORDER BY operations are evaluated.
</p>
<div>
<pre class="programlisting">
select ProductName, UnitsInStock
from (select distinct ProductID from Order_Details) O,
Products where Products.ProductID = O.ProductID;
</pre>
</div>
<p>
This retrieves the name and quantity of products that have been ordered.
</p>
<p>
An equivalent phrasing would be
</p>
<div>
<pre class="programlisting">
select distinct ProductName, UnitsInStock
from Order_Details O, Products where Products.ProductID = O.ProductID;
</pre>
</div>
<p>
The difference is that the latter retrieves a Products row for each order line whereas
as the first retrieves a products row for each distinct product in the order lines.
The first is therefore faster to evaluate. Also note that the rows in the DISTINCT buffer
in the first example only consist of the product id whereas they are much longer in the
second example.
</p>
<p>
Note that a correlation name is required for derived tables since the derived table is as
such anonymous.
</p>
<br />
<a name="qryexpressions" />
<h3>8.19.7. Query Expressions</h3>
<div>
<pre class="programlisting">
< non-join query expression > ::=
< non-join query term >
| < query expression > UNION [ ALL ]
[ < corresponding spec > ] < query term >
| < query expression > EXCEPT [ ALL ]
[ < corresponding spec > ] < query term >
< corresponding spec > ::=
CORRESPONDING [ BY < left parent >
< corresponding column list > < right parent > ]
</pre>
</div>
<p>
Queries can be combined by set operators UNION, INTERSECTION and EXCEPT (set difference).
The ALL keyword will allow duplicate rows in the result set. The CORRESPONDING BY clause
allows specifying which columns will be used to determine the equality of rows from the
left and right operands.
</p>
<div>
<pre class="programlisting">
select OrderID from Orders except
corresponding by (OrderID) select OrderID from Order_Details
</pre>
</div>
<p>
will produce the OrderID's of orders that have no Order_Details. This is equivalent to:
select OrderID from Orders a where not exists (select 1 from Order_Details b where a.OrderID = b.OrderID)
</p>
<p>
Note that the queries, although to a similar effect are executed quite differently.
There may be significant differences in performance.
</p>
<br />
<a name="LikePredicate" />
<h3>8.19.8. LIKE Predicate & Search Patterns</h3>
<p>
The <strong>LIKE</strong> predicate expects a pattern to be applied to
a varchar or nvarchar column to qualify the results to be returned from a query.
</p>
<p>
If the pattern does not begin with an at-sign (@) or with two
asterisks (**), then we test the equality of the string and pattern
with ordinary wildcard matching, which behaves
approximately like the filename pattern matching in the Unix shell.
(But not like the regular expression matching in utilities like grep
and sed).
</p>
<p>
The following characters have special significance in the pattern:
</p>
<ul>
<li>? Matches any single character.</li>
<li>* Matches zero or more of any characters.</li>
</ul>
<p>
[ ] (Called a group-expression here)
Matches any one of the enclosed characters, unless the
first character following the opening [ is ^, then matches
only if the character (in the datum string) is not any one of
those specified after the ^. (i.e. the ^ negates the meaning
of this expression.)
</p>
<p>
You can use character ranges like 0-9 (shorthand for 0123456789)
inside the brackets, in which case the character in the datum
string must be lexically within the inclusive range of that
pair (of course the character at the left side of hyphen must
be lexically (that is, its ASCII value) less than the
character at the right side).
</p>
<p>
The hyphen can be included in the character set by putting it
as the first or last character. The right bracket (]) can
be included by putting it as the first character in the expression,
i.e. immediately after the opening bracket ([) or the caret (^)
following it.
</p>
<a name="" />
<div class="example">
<div class="exampletitle">Examples:</div>
<div>
<pre class="programlisting">
[abc] Matches any of the letters a, b and c.
[^0123456789] Matches anything, except digits. (same as [^0-9])
[[] Matches [
[]] Matches ]
[][] Matches ] and [
[^]] Matches anything except ]
[A-Za-z0-9] Matches all the alphanumeric characters.
[-*+/] Matches the four basic arithmetic operators.
[-] Matches to single hyphen.
[]-] Matches to ] or -
[-[] or [[-] Matches to - or [
</pre>
</div>
</div>
<p>
That is, the hyphen indicates a range between characters, unless
it is the first or the last character in the group expression,
in which case it matches just to itself.
</p>
<p>
<strong>@</strong> Matches the character last matched to ? or group-expression.
For example ?*@ matches to all strings which begin with the same
character they end. However, if there is neither ? nor [] expression
at the left side of @ in the pattern, then @ matches just to
itself. (e.g. *@* should match to all e-mail addresses).
</p>
<p>
Any other characters match ONLY to themselves, that is, not even to
the upper- or lowercase variants of the same letter. Use expression
like [Wo][Oo][Rr][Dd] if you want to find any mixed-case variant of
the word "word", or use the substring search explained below.
</p>
<p>
However, if the pattern begins with an at-sign (@) then we compare
the rest of pattern to string with the fuzzy matching,
allowing differences of few characters in quality and
quantity (length). If there is more than one @ in the beginning of
pattern they are all skipped, and so many
additional liberties are given for the match function. The more
@-signs there are in the beginning, the more fuzzy (liberal) is the
search. For example: pattern "@Johnson" will match to string
"Jonsson" and pattern "@@Johnson" will match also to "Jansson".
</p>
<p>
If the pattern begins with two asterisks, then we do diacritic- and
case insensitive substring search,
trying to find the string given in the rest of pattern from the
datum string.
</p>
<a name="" />
<div class="example">
<div class="exampletitle">Example:</div>
<p>"**escort" will match to "Ford Escort vm. 1975".</p>
</div>
<p>
If there are any ISO8859.1 diacritic letters (e.g. vowels with
accents or umlaut-signs, or letters like the Spanish n with ~ (tilde))
present in the datum string, then the plain unaccented (7-bit ASCII)
variant of the same letter in the pattern string will match to it.
But if there are any diacritic letter specified in the pattern string,
then it will match only to the upper- or lowercase variant of exactly
the same diacritic letter.
</p>
<p>
The rationale behind this is that the people entering the information
to database can use the exact spelling for the word, for example
writing the word "Citroen" with the umlaut-e (e with two dots above it),
as it is actually written in French, and the people who search for
the Citroens can still find it without need to remember the exact
orthography of the French, by just giving a word "citroen".
And this allows also the people who have just plain 7-bit ASCII
keyboards to search for the words like Ra"a"kkyla" (place in Finland,
a" means umlaut-a, i.e. a with two dots above it), just by entering
the word raakkyla.
</p>
<p>
So the following holds with the substring searches:
</p>
<ul>
<li>
1) Any non-alphabetic character in the pattern matches just to itself
in the datum string (e.g. ? to ? and 3 to 3).
</li>
<li>
2) Any 7-bit ASCII letter (A-Z and a-z without any diacritic signs)
in the pattern matches to any diacritic variant of the same letter
(as well as to same 7-bit ASCII letter) in the datum string, either
in the upper- or lowercase.
</li>
<li>
3) Any diacritic letter (8-bit ISO8859.1 letter) in the pattern matches
only to the same letter (in the upper- or lowercase) in the datum
string.
</li>
</ul>
<div class="note">
<div class="notetitle">Note:</div>
<p>because the internal matching functions use macros
which consider also the characters like:
@, [, \, ], and ^ to be letters, they will match against characters
`, {, |, }, and ~ respectively, which is just all right, because
in some older implementations of European character sets those
characters mark the uppercase and lowercase variants of certain
diacritic letters.
</p>
</div>
<p>
It is generally better to match
too liberally and so maybe sometimes give something entirely off
the wall to the user, than to miss something important because of
too strict criteria.
</p>
<p>
Of course, when searching from the data which contains text in
some wide-character format (like certain coding systems for
Japanese and Chinese where one character is coded with two bytes)
neither fuzzy matching function nor nc_strstr function presented here
should be used, as they would often match on entirely spurious cases.
</p>
<br />
<a name="topselectoption" />
<h3>8.19.9. The TOP SELECT Option</h3>
<div>
<pre class="programlisting">
query_term : SELECT opt_top selection ....
opt_top : opt_all_distinct [ TOP INTNUM ]
| opt_all_distinct [ TOP SKIPINTNUM, INTNUM ]
| opt_all_distinct [ TOP (num_scalar_exp) ]
| opt_all_distinct [ TOP (skip_num_scalar_exp, num_scalar_exp) ]
opt_all_distinct : [ ALL | DISTINCT ]
</pre>
</div>
<p>
The TOP n phrase can follow an optional ALL or DISTINCT keyword in a SELECT,
either at top level or inside a query term of an expression or subquery. The query
with the TOP option will generate at most the indicated number of result rows.
</p>
<p>
The scope of TOP is the query in question. For example
</p>
<div>
<pre class="programlisting">
select top 3 row_no from t1 best union select top 3 row_no + 1 from t1;
</pre>
</div>
<p>
Will always return 4 rows assuming there are at least 3 rows in T1.
</p>
<p>The optional SKIPINTNUM parameter lets you offset the selection
by SKIPINTNUM number of rows. If you have a ten-row table and
<span class="computeroutput">select top 2 from this_table</span> you get
the first two rows, <span class="computeroutput">select top 2, 2 from this_table</span>
will return the third and fourth rows only, instead.</p>
<br />
<a name="caseandco" />
<h3>8.19.10. CASE, NULLIF, COALESCE, CAST Value Expressions</h3>
<a name="case" />
<h4>8.19.10.1. The CASE Expression</h4>
<p>There are many situations where you might find it useful to alter the
the data returned by a SQL query based on a few rules. For example, you may
want to display Customers gender as 'Male' or 'Female' based on whether their
title is 'Mr' or one of 'Miss', 'Mrs' or 'Ms'. The CASE expression can
easily accommodate this.</p>
<p>The Syntax of CASE is:</p>
<div>
<pre class="programlisting">
CASE
WHEN <search-condition> THEN <output>
WHEN <search-condition> THEN <output>
...
ELSE <output>
END
</pre>
</div>
<p>When a <search-condition> is met the corresponding <output>
is returned. If no conditions are met then the <output> after is ELSE
is returned as a default value.</p>
<a name="ex_caseexp" />
<div class="example">
<div class="exampletitle">Using the CASE expression</div>
<div>
<pre class="programlisting">
SELECT Title, CustomerName,
CASE
WHEN Title = 'Mr' THEN 'Male'
WHEN Title = 'Mrs' THEN 'Female'
WHEN Title = 'Miss' THEN Female'
WHEN Title = 'Ms' THEN 'Female'
ELSE 'Unknown'
END as Gender,
Company
FROM Customers
</pre>
</div>
<p>May return values such as:</p>
<div>
<pre class="screen">
Title Gender CustomerName Company
VARCHAR VARCHAR VARCHAR VARCHAR
___________________________________________________________
Mr Male Thomas Hardy Around the Horn
Miss Female Christina Berglund Berglunds shop
Mrs Female Hanna Moos Blauer See Delikatessen
Mr Male Laurence Lebihan Bon app
</pre>
</div>
</div>
<p>There is also a short hand notation for the CASE expression as follows:</p>
<div>
<pre class="programlisting">
CASE <search-parameter>
WHEN <search-value> THEN <output>
WHEN <search-value> THEN <output>
...
ELSE <output>
END
</pre>
</div>
<p>This short hand is best demonstrated by the rewrite of the above example
as follows:</p>
<a name="ex_caseexpshort" />
<div class="example">
<div class="exampletitle">Using the CASE short-hand expression</div>
<div>
<pre class="programlisting">
SELECT Title, CustomerName,
CASE Title
WHEN 'Mr' THEN 'Male'
WHEN 'Mrs' THEN 'Female'
WHEN 'Miss' THEN Female'
WHEN 'Ms' THEN 'Female'
ELSE 'Unknown'
END as Gender,
Company
FROM Customers
</pre>
</div>
</div>
<p>In both cases the ELSE keyword is optional. If ELSE is unspecified
then ELSE NULL is implicit.</p>
<br />
<a name="casenullif" />
<h4>8.19.10.2. The NULLIF Expression</h4>
<p>The NULLIF expression is a short hand implementation of a special case
of the CASE expression for a popular demand. Consider the following CASE
expression:</p>
<div>
<pre class="programlisting">
CASE col1
WHEN 'something' THEN NULL
ELSE col1
END
</pre>
</div>
<p>This is replaced by the NULLIF expression which achieves the same
result using the following, much shorter expression:</p>
<div>
<pre class="programlisting">
NULLIF (col1, 'something')
</pre>
</div>
<p>This is often useful in situations where you have a code to denote
a value as unspecified for whatever reason, but in many applications you
would rather this was NULL.</p>
<br />
<a name="casecoalesce" />
<h4>8.19.10.3. The COALESCE Expression</h4>
<p>The COALESCE expression is another application of the CASE expression
to suit another frequent requirement. The syntax of COALESCE is as follows:</p>
<div>
<pre class="programlisting">
COALESCE (value-1, value-2, ..., value-n)
</pre>
</div>
<p>COALESCE returns the first non-NULL parameter. This is equivalent to</p>
<div>
<pre class="programlisting">
CASE
WHEN value-1 IS NOT NULL THEN value-1
WHEN value-2 IS NOT NULL THEN value-2
...
ELSE value-n
END
</pre>
</div>
<br />
<a name="selcastexp" />
<h4>8.19.10.4. The CAST Expression</h4>
<p>SQL has always been considered a strongly typed language, meaning that
you cannot have expressions that contain arbitrary data types. Casting is
invaluable for comparing values that are obviously compatible but their
data types are not, such as <span class="computeroutput">1 = '1'</span>. This
attempts compares an integer with a char which would not work unless one of the
values was cast as follows:</p>
<div>
<pre class="programlisting">cast('1' as integer) = 1</pre>
</div>
<div class="tip">
<div class="tiptitle">See Also:</div>
<p>The <a href="sqlrefDATATYPES.html#dtcasting">CASTING</a> section for more
information.</p>
</div>
<br />
<br />
<a name="selbreackup" />
<h3>8.19.11. SELECT BREAKUP</h3>
<p>Virtuoso extends the select statement with a breakup option. This option allows
a single row selected by a derived table to be seen as multiple rows by the enclosing query.
</p>
<p>This is specially useful when translating relation tables to RDF. Using breakup,
one can do a single pass over a table and generate multiple triples, each presented as a separate result row.
</p>
<p>The syntax is:
</p>
<div>
<pre class="programlisting">
SELECT BREAKUP breakup_term [, ...] FROM ....
breakup_term ::=
scalar_exp [, scalar_exp...] [WHERE search_condition]
</pre>
</div>
<p>Each breakup term is a list of comma separated expressions with an optional search condition at
the end. Each list is treated as a select list in a union, i.e. they must be of equal length and the leftmost
list must provide a name for each column. This means that an AS declaration is needed if the expression is not a column.
</p>
<p>If a breakup tern has the optional WHERE clause, the condition is evaluated in the scope of the
select, i.e. all that is defined by the FROM. If the condition is true, the row represented by the breakup term
is added to the result set of the breakup select, otherwise it is ignored.
</p>
<p>A breakup select is only allowed in a derived table or a union or other set operator term inside
a derived table. A top level breakup select is not allowed. To have a breakup select as the topmost select, simply write:
</p>
<div>
<pre class="programlisting">
select * from (select breakup .... from ...) f;
</pre>
</div>
<p>Breakup cannot be mixed with distinct, top, group by or order by. Again, to combine these, use nested derived tables.
</p>
<p>Breakup operates equally well on local and remote tables. Breakup is never passed on to a remote but the FROM of a
breakup select can consist of tables from any source.
</p>
<a name="" />
<div class="example">
<div class="exampletitle">Examples:</div>
<div>
<pre class="programlisting">
select * from (select breakup (a.row_no, b.fi2) (b.row_no, a.fi3 where a.fi3 is not null)
from r1..t1 a, r1..t1 b
where a.row_no < 10 and b.row_no = a.row_no)f;
</pre>
</div>
</div>
<p>This produces 2 rows for each result of the join, except if fi3 is null, in which case only the first term of
the breakup is returned in the result set.
</p>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="updatestmt.html" title="UPDATE Statement">Previous</a>
<br />UPDATE Statement</td>
<td align="center" width="34%">
<a href="sqlreference.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="COMMIT_ROLLBACK.html" title="COMMIT WORK, ROLLBACK WORK Statement">Next</a>
<br />COMMIT WORK, ROLLBACK WORK Statement</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
46
