SELECT

• 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.

• 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.

• 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 Conversion Rules for Data in Query Expressions

• 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.

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)