 |
» |
|
|
|
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 | |
SQL Syntax — Query Expression Level | |
{QueryBlock
(QueryExpression)}[UNION [ALL]{QueryBlock
(QueryExpreession)}][...] |
SQL Syntax — Query Block Level | |
SELECT [ALL
DISTINCT] SelectList [INTO HostVariableSpecification]
FROM FromSpec [,...]
[WHERE SearchCondition1]
[GROUP BY GroupColumnList]
[HAVING SearchCondition2] |
SelectList | |
{*
[Owner.]Table.*
Correlation.Name*
Expression
[[Owner.]Table.]ColumnName
CorrelationName.ColumnName}[,...] |
HostVariableSpecification — With BULK Option | |
:Buffer [,:StartIndex [,:NumberOfRows]] |
HostVariableSpecification — Without BULK
Option | |
{:HostVariable [ [ INDICATOR] :Indicator] ) [,...] |
FromSpec | |
{TableSpec
(FromSpec)
FromSpec NATURAL [INNER
LEFT [OUTER]
RIGHT [OUTER]] JOIN {TableSpec
(FromSpec)}
FromSpec [INNER
LEFT [OUTER]
RIGHT [OUTER]] JOIN {TableSpec
(FromSpec)}{ON SearchCondition3
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.
CorrelationName.]ColumnName} |
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. Additionally,
the data file is generated in the directory specified when the LONG
column was defined.
SQL Syntax — Subquery Level | |
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: 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
(QueryExpression)} [UNION [ALL] {QueryBlock
(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 12-1 “Conversion Rules for Data in Query Expressions” shows the conversion rules for
comparable data types: Table 12-1 Conversion Rules for Data in Query Expressions | 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 | NATIVE VARCHAR | Result has the length of the longer
of the two source columns. | | One NATIVE CHAR, one CHAR | NATIVE CHAR | Result has the length of the longer
of the two source columns. | | One NATIVE VARCHAR, one CHAR
or VARCHAR | NATIVE VARCHAR | Result has the length of the longer
of the two source columns. | | One NATIVE CHAR, one VARCHAR | NATIVE VARCHAR | Result has the length of the longer
of the two source columns. | | One NATIVE VARCHAR, one VARCHAR | NATIVE VARCHAR | Result has the length of the 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: MIN(27, MAX(s1,s2) + MAX(p1-s1, p2-s2)) and the following is the scale of the result column: MAX(s1,s2) | | One DECIMAL, one SMALLINT or INTEGER | DECIMAL | Precision and scale are derived
as above. The precision and scale for an integer is (10,0); for
a smallint, (5,0). | | One INTEGER, one SMALLINT | INTEGER | | | Date/Time | Both DATE, TIME, DATETIME, or INTERVAL | DATE, TIME, DATETIME, or INTERVAL, respectively | | | One CHAR or VARCHAR and one DATE,
TIME, DATETIME, or INTERVAL | DATE, TIME, DATETIME, or INTERVAL, respectively | | | Binary | One BINARY, one VARBINARY | VARBINARY | Result has length of the longer
of the two source columns. |
SQL Syntax — Query Block Level | |
SELECT [ALL
DISTINCT] SelectList [INTO HostVariableSpecification]
FROM 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. Avoid DISTINCT in subqueries since the query result
is not changed, and it hinders rather than helping performance. - 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.
CorrelationName.]ColumnName}[,...] |
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.*
Correlation.Name*
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.] ColumnName
includes a particular column from the named owner's indicated
table. - CorrelationName. ColumnName
includes a specific column from the table whose 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)
FromSpec NATURAL [INNER
LEFT [OUTER]
RIGHT [OUTER]] JOIN {TableSpec
(FromSpec)}
FromSpec [INNER
LEFT [OUTER]
RIGHT [OUTER]] JOIN {TableSpec
(FromSpec)}{ON SearchCondition3
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. 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. 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 | |
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 |
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 |
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 |
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 |
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 |
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) |
|
