|
|
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.
ISQL or Application Programs
[BULK]QueryExpression [ORDER BY {ColumnID [ASC
DESC]}[,...]]
(QueryExpression)
{QueryBlock
(QueryExpression)} [UNION [ALL]{QueryBlock
(QueryExpreession)}][...]
SELECT [ALL
DISTINCT] SelectList [INTO HostVariableSpecification]
FROM FromSpec [,...]
[WHERE SearchCondition1]
[GROUP BY GroupColumnList]
[HAVING SearchCondition2]
{*
[Owner.]Table.*
Correlation.Name*
Expression
[[Owner.]Table.]ColumnName
CorrelationName.ColumnName}[,...]
:Buffer [,:StartIndex [,:NumberOfRows]]
{:HostVariable [[INDICATOR] :Indicator] ) [,...]
{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)}}
[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."
[BULK]QueryExpression [ORDER BY {ColumnID [ASC
DESC]}[,...]]
- 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}
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.
(QueryExpression)
- 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.
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.
{QueryBlock
(QueryExpression)} [UNION [ALL] {QueryBlock
(QueryExpression}][...]
- 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.
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. |
SELECT [ALL
DISTINCT] SelectList [INTO HostVariableSpecification]
FROM FromSpec [,...]
[WHERE SearchCondition1]
[GROUP BY GroupColumnList]
[HAVING SearchCondition2]
- 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.
{*
[Owner.]Table.*
Correlation.Name*
Expression
[[Owner.]Table.]ColumnName
CorrelationName.ColumnName }[,...]
- *
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.
:Buffer [,:StartIndex [,:NumberOfRows] ]
- 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.
- NumberOfRows
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.)
{ :HostVariable [INDICATOR]:Indicator] } [,...]
- 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.
{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
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.
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.
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.
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)
|