HP 3000 Manuals HP 3000 Manuals
SELECT
The SELECT statement retrieves data from one or more tables or views.
The retrieved data is presented in the form of a table, called the result
table or query result. The explanation of SQL Select syntax is broken
down into several levels for easier understanding. An overview of the
syntax at each of these levels is presented here starting with the Select
Statement Level and continuing through the syntax for the FromSpec.
Detailed discussion of each of these syntax levels is presented in the
same order, on the following pages.
Scope
ISQL or Application Programs
SQL Syntax--Select Statement Level
[BULK] QueryExpression [ORDER BY {ColumnID [ASC ]} [,...]]
[ { [DESC]} ]
SQL Syntax--Subquery Level
(QueryExpression)
SQL Syntax--Query Expression Level
{QueryBlock } [UNION [ALL] {QueryBlock }] [...]
{(QueryExpression)} [ {(QueryExpression)}]
SQL Syntax--Query Block Level
SELECT [ALL ] SelectList [INTO HostVariableSpecification] FROM
[DISTINCT]
FromSpec [,...] [WHERE SearchCondition1] [GROUP BY GroupColumnList]
[HAVING SearchCondition2]
SelectList
{* }
{[Owner.]Table.* }
{CorrelationName.* } [,...]
{Expression }
{[[Owner.]Table.]ColumnName}
{CorrelationName.ColumnName}
HostVariableSpecification--With BULK Option
:Buffer [,:StartIndex [,:NumberOfRows]]
HostVariableSpecification--Without BULK Option
{:HostVariable [[INDICATOR] :Indicator]} [,...]
FromSpec
{TableSpec }
{(FromSpec) }
{ [INNER ] }
{FromSpec NATURAL [LEFT [OUTER] ] JOIN {TableSpec } }
{ [RIGHT [OUTER]] {(FromSpec)} }
{ }
{ [INNER ] }
{FromSpec [LEFT [OUTER] ] JOIN {TableSpec } {ON SearchCondition3}}
{ [RIGHT [OUTER]] {(FromSpec)} {USING (ColumnList) }}
TableSpec
[Owner.]TableName [CorrelationName]
A SELECT statement can be examined at the following four levels:
Select Statement A select statement is a syntactically complete
SQL statement containing one or more SELECT
statements but having a single query result that
can optionally be sorted with an ORDER BY clause.
At its simplest, a select statement is a query
expression consisting of a single query block.
Subquery A subquery (also known as a nested query) is a
query expression enclosed in parentheses and
embedded in a search condition. A subquery
returns a value which is used in evaluating the
search condition.
Query Expression A query expression is a complex expression
consisting of one or more query blocks and
UNION/UNION ALL operators.
Query Block A query block is the primary query syntax for
specifying which tables to query and which
columns to return.
The syntax and usage of each of these levels is described below. For
additional information, refer to the chapter "SQL Queries."
SQL Syntax--Select Statement Level
[BULK] QueryExpression [ORDER BY {ColumnID [ASC ]} [,...]]
[ { [DESC]} ]
Parameters--Select Statement Level
BULK is specified in an application program to
retrieve multiple rows with a single execution of
the SELECT statement.
Do not use this option in select statements
associated with a cursor. Instead, use the BULK
option of the FETCH statement.
QueryExpression is a complex expression specifying what is to be
selected. The query expression is made up of one
or more query blocks, as described in the chapter
"SQL Queries."
ORDER BY sorts the result table rows in order by specified
columns. Specify the sort key columns in order
from major sort key to minor sort key. You can
specify as many as 1023 columns. The column
specified in the ORDER BY parameter must be one
of the columns appearing in the SELECT list.
Data is returned in descending order when the
ORDER BY columnID DESC clause is specified.
For each column you can specify whether the sort
order is to be ascending or descending. If
neither ASC nor DESC is specified, ascending
order is used.
ColumnID must correspond to a column in the select list.
You can identify a column to be sorted by giving
its name or by giving its ordinal number, with
the first column in the select list being column
number 1. You must use a column number when
referring to columns in the query result that are
derived from column expressions. You must also
use a column number to refer to columns if the
expression contains more than one query block.
The syntax for a column ID in the ORDER BY clause
follows:
{ColumnNumber }
{[[Owner.]TableName.]ColumnName}
{[CorrelationName. ] }
Description--Select Statement Level
* The SELECT statement is considered updatable if the query
expression it contains is updatable and if no ORDER BY clause is
present.
* The BULK option cannot be used interactively or in a procedure.
* ALLBASE/SQL uses file space in the defined TempSpaces, and in the
system files when processing queries containing ORDER BY clauses
or UNION operators. (No such space is used during UNION ALL.)
* When using this statement to select LONG columns, the name
of the file is returned in the appropriate field in the
HostVariableSpecification specified within the QueryExpression.
With the BULK option, if the output mode is specified with $, then
each LONG column in each row accessed has a file with a unique
name containing the LONG data retrieved.
SQL Syntax--Subquery Level
(QueryExpression)
Parameters--Subquery Level
QueryExpression is the basic syntax of a query or SELECT
statement. The query expression in a subquery
may not contain any UNION or UNION ALL
operations.
Description--Subquery Level
* Subqueries are used to retrieve data that is then used in
evaluating a search condition. For example, get supplier numbers
for the suppliers who supply the maximum quantity of part 'P1'.
SELECT SP.SNO
FROM SP
WHERE SP.PNO = 'P1'
AND SP.QTY = ( SELECT MAX(SP.QTY)
FROM SP
WHERE SP.PNO = 'P1')
Without using nested queries, the same answer would require the
two following queries--one to find the maximum, the other to list
the supplier number:
SELECT MAX(SP.QTY)
FROM SP
WHERE SP.PNO = 'P1'
and
SELECT SP.SNO
FROM SP
WHERE SP.PNO = 'P1'
AND SP.QTY = MaxQty
where MaxQty is the result of the first query.
* A subquery may be used only in the following types of predicates:
* EXISTS predicate.
* Quantified predicate.
* IN predicate.
* Comparison predicate.
* A subquery may be used in the WHERE or HAVING clause of SELECT
statements and in the WHERE clause of UPDATE, INSERT, and DELETE
statements.
* A subquery may also be nested in the WHERE or HAVING clause of
another subquery. No ALLBASE/SQL statement can have more than 16
query blocks within it.
* A subquery may reference a column value in a higher level of the
query (or outer query). Such a reference is called an outer
reference. A subquery making an outer reference is called a
correlated subquery. Because a correlated subquery depends on a
value of the outer query, the subquery must be reevaluated for
each new value of the outer query, as in the following example to
get supplier numbers for those who supply the most parts for each
part number.
SELECT SP1.SNO
FROM SP SP1
WHERE SP1.QTY = (SELECT MAX(SP2.QTY)
FROM SP SP2
WHERE SP1.PNO = SP2.PNO)
Note that the reference to SP1.PNO in the WHERE clause of the
subquery is an outer reference. In this case, because both the
outer query and the subquery refer to table SP, correlation names
SP1 and SP2 are assigned to make the distinction between the outer
and normal references. Within the subquery, any unqualified
column names (that is, those which are specified without a table
name) are assumed to refer only to tables specified in the FROM
clause of that subquery.
* If a query has a HAVING clause with subqueries in it, any outer
reference made from those subqueries to the query with the HAVING
clause must refer to a column specified in a GROUP BY clause.
SQL Syntax--Query Expression Level
{QueryBlock } [UNION [ALL] {QueryBlock }] [...]
{(QueryExpression)} [ {(QueryExpression)}]
Parameters--Query Expression Level
QueryBlock is the primary query stating which tables to
query, which columns to return, and which search
conditions to use for filtering data. The query
block is further described in one of the next
sections.
UNION unites two query expressions into a combined
query expression.
The union of two sets is the set of all elements
that belong to either or both of the original
sets. Because a table is a set of rows, the
union of two tables is possible. The resulting
table consists of all rows appearing in either or
both of the original tables.
ALL indicates that duplicates are not removed from
the result table when UNION is specified. If
UNION is specified without ALL, duplicates are
removed.
(QueryExpression) may be embedded within another query expression
if enclosed in parentheses. Parentheses are
optional when a query expression is not embedded.
Description--Query Expression Level
* For the following, assume that T1 is the result of the query block
or query expression on the left of the UNION operator, and T2 is
the result of the query block or query expression on the right of
the UNION operator. (The same conditions must be met if there are
additional UNION operators which include results from T3,...Tn.):
* T1 and T2 must have the same number of columns. (They may
be derived from tables with varying numbers of columns.)
* The union is derived by first inserting each row of T1 and
each row of T2 into a result table and then eliminating any
redundant rows unless ALL is specified.
* The result of the union inherits the column names specified
for T1.
* The maximum number of query blocks within a query
expression is 16.
* Data types of corresponding columns in T1 and T2 must be
comparable. When columns are of the same type but of
different sizes, the result has the length of the longer of
the source columns.
* The ORDER BY clause can specify the ordinal number or the column
name of a column in the leftmost query expression in a UNION.
* You cannot use LONG columns in a UNION statement except in long
string functions.
Table 10-7 shows the conversion rules for comparable data types:
Table 10-7.
----------------------------------------------------------------------------------------------------------------------
| CSI243sIX1581CSI32s | | | |
| | | | |
| Data Type | Source Columns | Result Column | Comment |
| | | | |
----------------------------------------------------------------------------------------------------------------------
| | | | |
| Character | One CHAR, one VARCHAR | VARCHAR | Result has the length of the |
| | | | longer of the two source |
| | | | columns. |
| | | | |
----------------------------------------------------------------------------------------------------------------------
| | | | |
| | One NATIVE CHAR, one | NATIVE VARCHAR | Result has the length of the |
| | NATIVE VARCHAR | | longer of the two source |
| | | | columns. |
| | | | |
----------------------------------------------------------------------------------------------------------------------
| | | | |
| | One NATIVE CHAR, one | NATIVE CHAR | Result has the length of the |
| | CHAR | | longer of the two source |
| | | | columns. |
| | | | |
----------------------------------------------------------------------------------------------------------------------
| | | | |
| | One NATIVE VARCHAR, one | NATIVE VARCHAR | Result has the length of the |
| | CHAR or VARCHAR | | longer of the two source |
| | | | columns. |
| | | | |
----------------------------------------------------------------------------------------------------------------------
| | | | |
| | One NATIVE CHAR, one | NATIVE VARCHAR | Result has the length of the |
| | VARCHAR | | longer of the two source |
| | | | columns. |
| | | | |
----------------------------------------------------------------------------------------------------------------------
| | | | |
| | One NATIVE VARCHAR, one | NATIVE VARCHAR | Result has the length of the |
| | VARCHAR | | longer of the two source |
| | | | columns. |
| | | | |
----------------------------------------------------------------------------------------------------------------------
| | | | |
| Numeric | One FLOAT or REAL | FLOAT | |
| | | | |
----------------------------------------------------------------------------------------------------------------------
| | | | |
| | Both DECIMAL | DECIMAL | If p1 and s1 are the precision |
| | | | and scale of C1, and p2 and s2 |
| | | | are the precision and scale of |
| | | | C2, the precision and scale of |
| | | | the result column is as |
| | | | follows:[REV BEG] |
| | | | MIN(27, MAX(s1,s2) + |
| | | | MAX(p1-s1, p2-s2)) |
| | | | and the following is the scale |
| | | | of the result column: |
| | | | MAX(s1,s2)[REV END] |
| | | | |
----------------------------------------------------------------------------------------------------------------------
| | | | |
| | One DECIMAL, one | DECIMAL | Precision and scale are |
| | SMALLINT or INTEGER | | derived as above. The |
| | | | precision and scale for an |
| | | | integer is (10,0); for a |
| | | | smallint, (5,0). |
| | | | |
----------------------------------------------------------------------------------------------------------------------
| | | | |
| | One INTEGER, one | INTEGER | |
| | SMALLINT | | |
| | | | |
----------------------------------------------------------------------------------------------------------------------
| | | | |
| Date/Time | Both DATE, TIME, | DATE, TIME, DATETIME, | |
| | DATETIME, or INTERVAL | or INTERVAL, | |
| | | respectively | |
| | | | |
----------------------------------------------------------------------------------------------------------------------
| | | | |
| | One CHAR or VARCHAR and | DATE, TIME, DATETIME, | |
| | one DATE, TIME, | or INTERVAL, | |
| | DATETIME, or INTERVAL | respectively | |
| | | | |
----------------------------------------------------------------------------------------------------------------------
| | | | |
| Binary | One BINARY, one | VARBINARY | Result has length of the |
| | VARBINARY | | longer of the two source |
| | | | columns. |
| | | | |
----------------------------------------------------------------------------------------------------------------------
SQL Syntax--Query Block Level
SELECT [ALL ] SelectList [INTO HostVariableSpecification] FROM
[DISTINCT]
FromSpec [,...] [WHERE SearchCondition1] [GROUP BY GroupColumnList]
[HAVING SearchCondition2]
Parameters--Query Block Level
ALL prevents elimination of duplicate rows from the
result. If neither ALL nor DISTINCT is
specified, the ALL option is assumed.
DISTINCT ensures that each row in the query result is
unique. All null values are considered equal.
You cannot specify this option if the select
list contains an aggregate function with
DISTINCT in the argument. This option cannot
be used for a select list longer than 255
items.[REV BEG] Avoid DISTINCT in subqueries
since the query result is not changed, and it
hinders rather than helping performance.[REV
END]
SelectList tells how the columns of the result table are
to be derived. The syntax of SelectList is
presented separately below.
INTO The INTO clause defines host variables for
holding rows returned in application programs.
Do not use this clause for SELECT statements
associated with a cursor or dynamically
preprocessed SELECT statements, query blocks
within subqueries, nested query expressions, or
any but the first query block in a SELECT
statement.
HostVariableSpecification identifies one or more host variables for
holding rows returned in application programs.
Do not use this clause for SELECT statements
associated with a cursor or dynamically
preprocessed SELECT statements, query blocks
within subqueries, nested query expressions, or
any but the first query block in a SELECT
statement. The syntax of BULK and non-BULK
types of HostVariableSpecification are
presented separately below.
FROM The FROM clause identifies the tables and views
referenced anywhere in the SELECT statement.
The maximum number of tables per query is 31.
FromSpec identifies the tables and views in a query
block and explicitly defines inner and outer
joins. The syntax of FromSpec is presented
separately below.
WHERE The WHERE clause determines the set of rows to
be retrieved. Rows for which SearchCondition1
is false or unknown are excluded from
processing. If the WHERE clause is omitted, no
rows are excluded. Aggregate functions cannot
be used in the WHERE clause.
Rows that do not satisfy SearchCondition1 are
eliminated before groups are formed and
aggregate functions are evaluated.
When you are joining tables or views, the WHERE
clause also specifies the condition(s) under
which rows should be joined. You cannot join
on a column in a view derived using a GROUP BY
clause. If you omit a join condition,
ALLBASE/SQL joins each row in each table in the
FROM clause with each row in all other tables
in the FROM clause.
SearchCondition1 may contain subqueries. Each
subquery is effectively executed for each row
of the outer query and the results used in the
application of SearchCondition1 to the given
row. If any executed subquery contains an
outer reference to a column of a table or view
in the FROM clause, then the reference is to
the value of that column in the given row.
Refer to the "Search Conditions" chapter for
additional information on search conditions.
GROUP BY The GROUP BY clause identifies the columns to
be used for grouping when aggregate functions
are specified in the select list and you want
to apply the function to groups of rows. You
can specify as many as 1023 columns, unless the
select list contains an aggregate function with
the DISTINCT option, in which case you can
specify as many as 254 columns.
The syntax for the group column list in the
GROUP BY clause follows:
{[[Owner.]TableName.]ColumnName}[,...]
{[CorrelationName. ] }
When you use the GROUP BY clause, the select
list can contain only aggregate functions and
columns referenced in the GROUP BY clause. If
the select list contains an *, a TableName.*,
or an Owner.TableName.* construct, then the
GROUP BY clause must contain all columns that
the * includes. Specify the grouping column
names in order from major to minor.
Null values are considered equivalent in
grouping columns. If all other columns are
equal, all nulls in a column are placed in a
single group.
If the GROUP BY clause is omitted, the entire
query result table is treated as one group.
HAVING The HAVING clause specifies a test to be
applied to each group. Any group for which the
result of the test is false or unknown is
excluded from the query result. This test,
referred to as SearchCondition2, can be a
predicate containing either an aggregate
function or a column named in the GROUP BY
clause.
Each subquery in SearchCondition2 is
effectively checked for each group created by
the GROUP BY clause, and the result is used in
the application of SearchCondition2 to the
given group. If any executed subquery contains
an outer reference to a column, then the
reference is to the values of that column in
the given group. Only grouping columns can be
used as outer references in a subquery in
SearchCondition2.
SQL Syntax--SelectList
{* }
{[Owner.] Table.* }
{CorrelationName.* } [,...]
{Expression }
{[[Owner.] Table.] ColumnName}
{CorrelationName.ColumnName }
Parameters--SelectList
* includes, as columns of the result table, all
columns of all tables and views specified in the
FROM clause.
[Owner.]Table.* includes all columns of the specified table or
view in the result.
CorrelationName.* includes all columns of the specified table or
view in the result. The correlation name is a
synonym for the table or view as defined in the
FROM clause.
Expression produces a single column in the result table; the
result column values are computed by evaluating
the specified expression for each row of the
result table.
The expression can be of any complexity. For
example, it can simply designate a single column
of one of the tables or views specified in the
FROM clause, or it can involve aggregate
functions, multiple columns, and so on. When you
specify one or more aggregate functions in a
select list, the only other entity you can
specify is the name(s) of the column(s) you group
by.
[ [Owner.]Table.] includes a particular column from the named
ColumnName owner's indicated table.
CorrelationName. includes a specific column from the table whose
ColumnName correlation name is defined in the FROM clause.
SQL Syntax--BULK HostVariableSpecification
:Buffer [,:StartIndex [,:NumberOfRows] ]
Parameters--BULK HostVariableSpecification
Buffer is a host array or structure that is to receive the
output of the SELECT statement. This array
contains elements for each column in the SelectList
and indicator variables for columns that can
contain null values. Whenever a column can contain
nulls, an indicator variable must be included in
the array definition immediately after the
definition of that column. The indicator variable
can receive the following integer values after a
SELECT statement:
0 the column's value is not NULL
-1 the column's value is NULL
> 0 is truncated; the number indicates the data
length before truncation
StartIndex is a host variable whose value specifies the array
subscript denoting where the first row in the query
result should be stored; default is the first
element of the array.
Number- OfRows is a host variable whose value specifies the
maximum number of rows to store; default is to fill
from the starting index to the end of the array.
The total number of rows stored is returned in the SQLERRD[3] field of
the SQLCA. (SQLERRD[2] for the C language.)
SQL Syntax--non-BULK HostVariableSpecification
{:HostVariable [[INDICATOR] :Indicator]} [,...]
Parameters--non-BULK HostVariableSpecification
HostVariable identifies the host variable corresponding to one
column in the row.
Indicator names an indicator variable, an output host
variable whose value (see following) depends on
whether the host variable contains a null value:
0 the column's value is not NULL
-1 the column's value is NULL
> 0 is truncated; the number indicates the data
length before truncation
The order of the host variables must match the order of their
corresponding items in the select list.
SQL Syntax--FromSpec
{TableSpec }
{(FromSpec) }
{ [INNER ] }
{FromSpec NATURAL [LEFT [OUTER] ] JOIN {TableSpec } }
{ [RIGHT [OUTER]] {(FromSpec)} }
{ }
{ [INNER ] }
{FromSpec [LEFT [OUTER] ] JOIN {TableSpec } {ON SearchCondition3}}
{ [RIGHT [OUTER]] {(FromSpec)} {USING (ColumnList) }}
Parameters--FromSpec
TableSpec identifies a table or view from which rows are
selected.
The syntax for a TableSpec in a FromSpec follows:
[Owner.]TableName [CorrelationName]
[Owner.]TableName identifies a table or view to be
referenced. The TableName may
be preceded by an OwnerName, and
may be followed by the
definition of a CorrelationName.
CorrelationName specifies a synonym for the
immediately preceding table or
view. The correlation name can
be used instead of the actual
table or view name anywhere
within the SELECT statement when
accessing columns or TID values
of that table.
The correlation name must
conform to the syntax rules for
a basic name. All correlation
names within one SELECT
statement must be unique. They
cannot be the same as any table
name or view name in the FROM
clause that does not also have a
correlation name associated with
it.
Correlation names are useful
when you join a table to itself.
You name the table twice in the
FROM clause, and assign it two
different correlation names.
(FromSpec) allows the placement of parentheses around a
FromSpec in order to alter the order of evaluation
of the components of a complex FromSpec, such as one
used to describe a three or more table outer join.
NATURAL indicates that for both inner and outer joins,
columns which are common to two tables being joined
will be coalesced into a single column when the
query result is returned. Also, ALLBASE/SQL will
automatically identify and use the columns common to
both tables to execute the join. When using
the keyword NATURAL you do not use an ON
SearchCondition3 clause or a USING (ColumnList)
clause to specify the join columns.
INNER join type indicates that the only rows selected in
the join will be those rows for which a match is
found in the join column(s) of both tables being
joined. If the join type is not specified, INNER is
the default.
LEFT defines the join as a LEFT OUTER JOIN. For a LEFT
OUTER JOIN the query result will contain not only
the matched rows from both tables being joined, but
will also preserve (contain) those rows from the
left hand table in the FromSpec for which there is
no match in the right hand table. The preserved
rows are extended to the right with null column
values for each column obtained from the right hand
table.
For each instance of the keyword JOIN in a FromSpec,
the named table or the result table immediately
preceding JOIN is the left hand table, the named
table or the result table immediately following JOIN
is the right hand table.
RIGHT defines the join as a RIGHT OUTER JOIN. For a RIGHT
OUTER JOIN the query result will contain not only
the matched rows from both tables being joined, but
will also preserve (contain) those rows from the
right hand table in the FromSpec for which there is
no match in the left hand table. The preserved rows
are extended to the left with null column values for
each column obtained from the left hand table.
For each instance of the keyword JOIN in a FromSpec,
the named table immediately following JOIN is the
right hand table, the named table immediately
preceding JOIN is the left hand table.
OUTER is optional as a keyword. If either LEFT or RIGHT
are used, the join type is, by default, an outer
join.
JOIN specifies that a join is being defined. Evaluation
of the FromSpec is from left to right. For a three
or more table join, the two tables associated with
the left most instance of the JOIN keyword are
joined first, and the result of that join is
considered the left hand table for the next
occurring instance of the keyword JOIN. The same
algorithm applies for each additional occurrence of
JOIN. Parentheses can be used to force a change in
this order of evaluation of the FromSpec.
ON SearchCondition3 may only be used when the keyword NATURAL is not
used. Two types of predicates are specified in
SearchCondition3.
The first type of predicate contains the equality
which specifies the join columns to be used for
the associated join. For each occurrence in the
FromSpec of the keyword JOIN, in the ON
SearchCondition3 clause the column names
specified on each side of the equality must be
fully qualified.
The second type of predicate limits, for the
associated join only, the rows which participate
in the inner part of the join. Rows which are
excluded from the inner part of the join will be
added to those preserved in the outer part of the
join. This predicate follows all general rules
for search conditions as specified in the "Search
Conditions" chapter.
Predicates placed in the ON SearchCondition3
clause, associated with an instance of JOIN,
apply only to that associated inner join.
However, predicates placed in the WHERE clause of
the SELECT statement apply to the entire query
result, after all joins have been evaluated.
Therefore you must consider carefully the
placement of limiting predicates to decide
whether they belong in the WHERE clause, or in an
ON SearchCondition3 clause associated with a
particular instance of JOIN in the FromSpec. See
"Outer Joins" in the "SQL Queries" chapter for
specific examples illustrating the changes to the
query result brought about by changes in
placement of the limiting predicates.
USING(ColumnList) specifies participating columns common to both
tables being joined, and can only be used if the
keyword NATURAL has not been used in the
FromSpec. The column names must be unqualified
because the columns occur in more than one table.
Description--Query Block Level
* The BULK option and INTO clause cannot be used interactively or in
procedures.
* The clauses must be specified in the order given in the syntax
diagram.
* A result column in the select list can be derived in any of these
following ways:
* A result column can be taken directly from one of the
tables or views listed in the FROM clause.
* Values in a result column can be computed, using an
arithmetic expression, from values in a specified column of
a table or view listed in the FROM clause.
* Values in several columns of a single table or view can be
combined in an arithmetic expression to produce the result
column values.
* Values in columns of various different tables or views can
be combined in an arithmetic expression to produce the
result column values.
* Aggregate functions (AVG, MAX, MIN, SUM, and COUNT) can be
used to compute result column values over groups of rows.
Aggregate functions can be used alone or in an expression.
If you specify more than one aggregate function containing
the DISTINCT option, all these aggregate functions must
operate on the same column. If the GROUP BY clause is not
specified, the function is applied over all rows that
satisfy the query. If the GROUP BY clause is specified,
the function is applied once for each group defined by the
GROUP BY clause. When you use aggregate functions with the
GROUP BY clause, the select list can contain only aggregate
functions and columns referenced in the GROUP BY clause.
* A result column containing a fixed value can be created by
specifying a constant or an expression involving only
constants.
* In addition to specifying how the result columns are derived, the
select list also controls their relative position from left to
right in the result table. The first result column specified by
the select list becomes the leftmost column in the result table.
* The maximum number of columns in a query result is 1024, except
when the query contains the DISTINCT option or is within a UNION
query expression. In this case, the maximum number of columns is
1023. The maximum number of LONG data type columns which can be
directly selected or fetched in a select list is 40. However, any
number can be referenced in long string functions. They must be
referenced by column name only and cannot participate in an
expression in the select list, unless they are being accessed
through long string functions.
* Result columns in the select list are numbered from left to right.
The leftmost column is number 1. Result columns can be referred
to by column number in the ORDER BY clause; this is especially
useful if you want to refer to a column defined by an arithmetic
expression.
* When you specify the NATURAL....JOIN:
* You can not use the ON SearchCondition3 or USING
(ColumnList) clauses.
* Each pair of columns with the same column name, which are
common to the two tables being joined, will be coalesced
into a single common column in the query result.
ALLBASE/SQL will automatically determine which columns to
use for the join. All columns which have the same column
name in each of the tables being joined will be used for
the join.
* When common columns are referenced in the query, such as in
the select list, you must use only the unqualified name of
the column.
* Each pair of columns common to two tables being joined must
have the same or compatible data types.
* For a SELECT *, each pair of columns, common to the two
tables being joined, will be coalesced into a single common
column and will be the first columns displayed in the
result, in the order in which they were defined in the left
hand table. They will be followed by the columns from the
left hand table that were not used for the join. The last
columns displayed will be those from the right hand table
not participating in the join. Columns not used for the
join will be displayed in the order in which they are
defined in their respective tables.
* For any other SELECT, the columns displayed will be those
specified in the select list, in the order specified.
* If there are no common columns between the tables being
joined, the columns resulting from the join are the same as
the columns that would result from the Cartesian product of
the joined tables. See the "SQL Queries" chapter.
* When you specify JOIN....ON SearchCondition3:
* You cannot use the keyword NATURAL or the USING ColumnList
clause.
* Column Names from common columns used in the join predicate
in SearchCondition3 must be fully qualified. If additional
predicates are used in SearchCondition3 to limit the rows
returned from the join, each column name used must
unambiguously reference a column in one of the tables being
joined, or must be an outer reference (as in the case of
nested subqueries).
* For a SELECT *, the columns contained in the result of the
join are the same as the columns of the Cartesian product
of the tables being joined.
* For any other SELECT, the columns displayed will be those
specified in the select list, in the order specified.
* The result of the INNER JOIN....ON SearchCondition3
contains the multiset of rows of the Cartesian Product of
the tables being joined for which all predicates in
SearchCondition3 are true.
* When you specify JOIN....USING (ColumnList):
* You must not use the keyword NATURAL or the ON
SearchCondition3 clause.
* You place in the ColumnList one unqualified column name for
each pair of common columns being used for the join.
* No column name may be used if it is not common to both
tables being joined.
* For SELECT *, the result of the INNER JOIN....USING
(ColumnList) contains the multiset of rows of the Cartesian
product of the tables being joined for which the
corresponding join columns have equal values. The
coalesced common columns are returned first. (No duplicate
columns are displayed in the case of common columns). The
non-join columns from both tables appear next. If there is
no common column, the result contains the multiset of rows
of the Cartesian product of the tables being joined.
* The result of the [NATURAL] LEFT [OUTER] JOIN is the union of two
components. The first component is the result of the equivalent
[NATURAL] INNER JOIN. The second component contains those rows in
the left hand table that are not in the INNER JOIN result. These
rows are extended to the right with null values in the column
positions corresponding to the columns from the right hand table.
For a natural join, the column values in the common columns are
taken from the left hand table.
* The result of the [NATURAL] RIGHT [OUTER] JOIN is the union of two
components. The first component is the result of the equivalent
[NATURAL] INNER JOIN. The second component contains those rows in
the right hand table that are not in the INNER JOIN result. These
rows are extended to the left with null values in the column
positions corresponding to the columns from the left hand table.
For a natural join, the column values in the common columns are
taken from the right hand table.
[REV BEG]
* The ON clause (which is associated with the OUTER JOIN in a join
condition) and all predicates in a WHERE clause are filters. At
each OUTER JOIN block, the INNER JOIN result (which matches the
join condition in an ON clause) will be presented. Then all
tuples in the preserving table (which is not in the INNER
JOIN result) will be presented by matching columns in the
non-preserving table with nulls.[REV END]
* For three or more table joins, care must be taken when mixing
NATURAL....JOIN, JOIN ON SearchCondition3, and JOIN USING
(ColumnList) clauses.
* The JOIN ON Searchcondition3 clause produces a result table
with the common columns appearing twice, once for each
table participating in the join.
* If this result table is used as input to a NATURAL....JOIN
clause or a JOIN USING (ColumnList) clause, and the column
appearing twice in the result table is named as a join
column in the JOIN USING (ColumnList) clause or is selected
by ALLBASE/SQL as the join column in the NATURAL JOIN, an
error will result. This happens because it is impossible
to specify which of the two common columns in the result
table is to participate in the following join.
* When writing a three or more table join with explicit join
syntax, make sure that for any single result table
participating in a join, there are no duplicate column
names which will be named as a join column. To ensure
this, make each join clause a NATURAL...JOIN or a
JOIN...USING (ColumnList), except for the final join, which
may contain these types or a JOIN...ON SearchCondition3
clause. Otherwise, ensure that each join clause is a
JOIN...ON SearchCondition3 clause.
* To join tables, without using explicit JOIN syntax, list the
tables in the FROM clause, and specify a join predicate in the
WHERE clause.
* If you specify SELECT * and in the WHERE clause an equal
predicate specifies the join but there are no other
limiting predicates, the result of this procedure is the
same as that obtained when using the INNER JOIN described
above. The common column appears twice in the query
result, once for each table from which it was obtained.
* If you select each column explicitly, naming each column
only once (and appropriately fully qualify a single column
name for each pair of column names that is common to both
tables) the result is the same as that obtained when using
the NATURAL INNER JOIN, above. The common column appears
only once in the query result, and is taken from the table
specified in the fully qualified column name.
* To join a table with itself, define correlation names for the
table in the FROM clause; use the correlation names in the select
list and the WHERE clause to qualify columns from that table.
* NULLs affect joins and Cartesian products as follows:
* Rows are only selected for an inner join when the join
predicate evaluates to true. Since the value of NULL is
undetermined, the value of the predicate NULL = NULL is
unknown. Thus, if the value in the common columns being
joined is NULL, the rows involved will not be selected.
* Rows excluded from the inner part of an outer join because
the common column values are NULL, are included in the
outer part of the outer join.
* The existence of NULLs does not exclude rows from being
included in a Cartesian product. See the "SQL Queries"
chapter for more information.
* When you use the GROUP BY clause, one answer is returned per
group, in accord with the select list:
* The WHERE clause eliminates rows before groups are formed.
* The GROUP BY clause groups the resulting rows.
* The HAVING clause eliminates groups.
* The select list aggregate functions are computed for each
group.
* ALLBASE/SQL allocates sort file space in /tmp, by default, or in
the space specified using the CREATE TEMPSPACE statement. The
space is deallocated once the statement completes.
* The query block is considered updatable if, and only if, it
satisfies the following conditions:
* No DISTINCT, GROUP BY, or HAVING clause is specified in the
outermost SELECT clause, and no aggregates appear in the
select list.
* No INTO clause is specified.
* The FROM clause specifies exactly one table or view
(contains no inner or outer joins) and if a view is
specified, it is an updatable view.
* For INSERT and UPDATE through views, the select list in the
view definition must not contain any arithmetic
expressions. It must contain only column names.
* For DELETE WHERE CURRENT and UPDATE WHERE CURRENT
operations, the cursor definition must not contain
subqueries.
* For noncursor UPDATE, DELETE, or INSERT, the view
definition, or the WHERE clause must not contain any
subqueries referencing the target table in their FROM
clause.
Authorization
If you specify the name of a table, you must have SELECT or OWNER
authority for the table, or you must have DBA authority.
If you specify the name of a view, you must have SELECT or OWNER
authority for the view, or you must have DBA authority. Also, the owner
of the view must have SELECT or OWNER authority with respect to the
view's definition, or the owner must have DBA authority.
Examples
1. Simple queries
One value, the average number of days you wait for a part, is
returned.
SELECT AVG(DeliveryDays)
FROM PurchDB.SupplyPrice
The part number and delivery time for all parts that take fewer
than 20 days to deliver are returned. Multiple rows may be
returned for a single part.
SELECT PartNumber, DeliveryDays
FROM PurchDB.SupplyPrice
WHERE DeliveryDays < 20
2. Grouping
The part number and average price of each part are returned.
SELECT PartNumber, AVG(UnitPrice)
FROM PurchDB.SupplyPrice
GROUP BY PartNumber
The query result is the same as the query result for the previous
SELECT statement, except it contains rows only for parts that can
be delivered in fewer than 20 days.
SELECT PartNumber, AVG(UnitPrice)
FROM PurchDB.SupplyPrice
GROUP BY PartNumber
HAVING MAX(DeliveryDays) < 20
3. Joining
This join returns names and locations of California suppliers.
Rows are returned in ascending PartNumber order; rows containing
duplicate PartNumbers are returned in ascending VendorName order.
The FROM clause defines two correlation names (v and s), which are
used in both the select list and the WHERE clause. VendorNumber
is the only common column between Vendors and SupplyPrice.
SELECT PartNumber, VendorName, s.VendorNumber, VendorCity
FROM PurchDB.SupplyPrice s, PurchDB.Vendors v
WHERE s.VendorNumber = v.VendorNumber
AND VendorState = 'CA'
ORDER BY PartNumber, VendorName
This query is identical to the query immediately above except that
it uses the explicit JOIN syntax .
SELECT PartNumber, VendorName, VendorNumber, VendorCity
FROM PurchDB.SupplyPrice
NATURAL JOIN PurchDB.Vendors
WHERE VendorState = 'CA'
ORDER BY PartNumber, VendorName
This query joins table PurchDB.Parts to itself in order to
determine which parts have the same sales price as part 1133-P-01.
SELECT q.PartNumber, q.SalesPrice
FROM PurchDB.Parts p, PurchDB.Parts q
WHERE p.SalesPrice = q.SalesPrice
AND p.PartNumber = '1133-P-01'
This query does a left outer join between the Vendors and
SupplyPrice tables. Since every part supplied by a vendor has an
entry in the SupplyPrice table, the result first displays every
vendor who supplies a part. The result then displays every vendor
who does not supply any parts.
SELECT PartNumber, VendorName, VendorCity
FROM Purchdb.Vendors v
LEFT JOIN Purchdb.SupplyPrice s
ON s.VendorNumber = v.VendorNumber
ORDER BY PartNumber, VendorName
4. BULK SELECT
Programmatically, when you do not need to use the capabilities
associated with a cursor, you can use the BULK option to retrieve
multiple rows.
BULK SELECT *
INTO :Items, :Start, :NumRow
FROM PurchDB.Inventory
5. UNION Option
Retrieves all rows from two Parts tables into a single query
result ordered by PartNumber. PartNumber and PartValue are
comparable; SalesPrice and Price are comparable.
SELECT PartNumber, SalesPrice
FROM P1988.Parts
UNION
SELECT PartValue, Price
FROM P1989.Parts
ORDER BY PartNumber
6. Nested query or subquery
Obtain a list of customer orders whose totals are higher than the
largest order of 1988.
SELECT OrderNumber, SUM(PurchasePrice)
FROM PurchDB.OrderItems
GROUP BY OrderNumber
HAVING SUM(PurchasePrice) > (SELECT MAX(PurchasePrice)
FROM FY1988.Orders)
Get vendor numbers for all vendors located in the same city as
vendor number 9005.
SELECT VendorNumber
FROM PurchDB.Vendors
WHERE VendorCity = (SELECT VendorCity
FROM PurchDB.Vendors
WHERE VendorNumber = '9005')
Get supplier names for suppliers who provide at least one red
part.
SELECT SNAME
FROM S
WHERE SNO IN ( SELECT SNO
FROM SP
WHERE EXISTS (SELECT PNO
FROM P
WHERE P.PNO = SP.PNO
AND COLOR = 'RED' ))
Get supplier number for suppliers who supply the most parts.
SELECT SNO
FROM SP
GROUP BY SNO
HAVING COUNT(DISTINCT PNO) >= ALL ( SELECT COUNT(DISTINCT PNO)
FROM SP
GROUP BY SNO )
Insert into table T, supplier names of each supplier who does not
supply any part.
INSERT INTO T (SNO)
SELECT SNO
FROM S
WHERE NOT EXISTS (SELECT *
FROM SP
WHERE SP.SNO = S. SNO)
Delete all suppliers from the supplier table who do not supply any
parts.
DELETE FROM S
WHERE NOT EXISTS ( SELECT *
FROM SP
WHERE SP.SNO = S.SNO)