HP 3000 Manuals HP 3000 Manuals
The Query
A query is a SELECT command that describes to ALLBASE/SQL the data you
want retrieved. You can retrieve all or only certain data from a table.
You can have ALLBASE/SQL group or order the rows you retrieve or perform
certain calculations or comparisons before presenting data to your
program. You can retrieve data from multiple tables. You can also
retrieve data using views or combinations of tables and views.
The SELECT Command
The SELECT command identifies the columns and rows you want in your query
result as well as the tables and views to use for data access. The
columns are identified in the select list. The rows are identified in
several clauses (GROUP BY, HAVING, and ORDER BY). The tables and views to
access are identified in the FROM clause. Data thus specified is
returned into host variables named in the INTO clause:
EXEC SQL SELECT SelectList
1 INTO HostVariables
2 FROM TableNames
3 WHERE SearchCondition1
4 GROUP BY ColumnName
5 HAVING SearchCondition2
6 ORDER BY ColumnID
To retrieve all data from a table, the SELECT command need specify only
the following:
EXEC SQL SELECT *
1 INTO :HostVariable1
2 :HostVariable2
3 .
4 .
5 .
6 FROM OwnerName.TableName
Although the shorthand notation * can be used in the select list to
indicate you want all columns from one or more tables or views, it is
better programming practice to explicitly name columns. Then, if the
tables or views referenced are altered, your program will still retrieve
only the data its host variables are designed to accommodate:
EXEC SQL SELECT PartNumber,
1 PartName,
2 SalesPrice
3 INTO :PartNumber,
4 :PartName,
5 :SalesPrice
6 FROM PurchDB.Parts
The SELECT command has several clauses you can use to format the data
retrieved from any table:
* the WHERE clause specifies a search condition. A search condition
consists of one or more predicates. A predicate is a test each
row must pass before it is returned to your program.
* the GROUP BY clause and the HAVING clause tell ALLBASE/SQL how to
group rows retrieved before applying any aggregate function in the
select list to each group of rows.
* the ORDER BY clause causes ALLBASE/SQL to return rows in ascending
or descending order, based on the value in one or more columns.
The following SELECT command contains a WHERE clause that limits rows
returned to those not containing a salesprice; the predicate used in the
WHERE clause is known as the null predicate:
EXEC SQL SELECT PartName,
1 SalesPrice
2 INTO :PartName,
3 :SalesPrice
4 FROM PurchDB.Parts
5 WHERE SalesPrice IS NULL
In the UPDATE and DELETE commands, you may need a WHERE clause to limit
the rows ALLBASE/SQL changes or deletes. In the following case, the
sales price of parts priced lower than $1000 is increased 10 percent; the
WHERE clause in this case illustrates the comparison predicate:
EXEC SQL UPDATE PurchDB.Parts
1 SET SalesPrice = SalesPrice * 1.1
2 WHERE SalesPrice < 1000.00
The ALLBASE/SQL Reference Manual details the syntax and semantics for
these and other predicates.
When you use an aggregate function in the select list, you can use the
GROUP BY clause to indicate how ALLBASE/SQL should group rows before
applying the function. You can also use the HAVING clause to limit the
groups to only those satisfying certain criteria. The following SELECT
command will produce a query result containing two columns: a sales
price and a number indicating how many parts have that price:
EXEC SQL SELECT SalesPrice,
1 COUNT(PartNumber)
2 INTO :SalesPrice,
3 :Count
4 FROM PurchDB.Parts
5 GROUP BY SalesPrice
6 HAVING AVG(SalesPrice) > 1500.00
The GROUP BY clause in this example causes ALLBASE/SQL to group all parts
with the same sales price together. The HAVING clause causes ALLBASE/SQL
to ignore any group having an average sales price less than or equal to
$1500.00. Once the groups have been defined, ALLBASE/SQL applies the
aggregate function COUNT to each group.
Each null value in a GROUP BY column constitutes a separate group.
Therefore a query result having a null value in the column(s) used to
group rows would contain a separate row for each null value.
An aggregate function is one example of an ALLBASE/SQL expression. An
expression specifies a value. An expression can be used in several
places in the SELECT command as well as in the other data manipulation
commands. Refer to the ALLBASE/SQL Reference Manual for the syntax and
semantics of expressions, as well as the effect of null values on them.
The rows in the query result obtained with the preceding query could be
returned in a specific order by using the ORDER BY clause. In the
following case, the rows are returned in descending sales price order:
EXEC SQL SELECT SalesPrice,
1 COUNT(PartNumber)
2 INTO :SalesPrice,
3 :Count
4 FROM PurchDB.Parts
5 GROUP BY SalesPrice
6 HAVING AVG(SalesPrice) > 1500.00
7 ORDER BY SalesPrice DESC
The examples shown so far have all included queries where results would
most likely contain more than one row. The sequential table processing
technique using cursors could also be used to handle multiple-row query
results. Later in this chapter you'll find examples of this technique,
as well as examples illustrating simple data manipulation, in which only
one-row query results are expected.
Selecting from Multiple Tables
To retrieve data from more than one table or view, the query describes to
ALLBASE/SQL how to join the tables before deriving the query result:
* In the FROM clause, you identify the tables and views to be
joined.
* In the WHERE clause, you specify a join condition. A join
condition defines the condition(s) under which rows should be
joined.
To obtain a query result consisting of the name of each part and its
quantity-on-hand, you need data from two tables in the sample database:
PurchDB.Parts and PurchDB.Inventory. The join condition in this case is
that you want ALLBASE/SQL to join rows in these tables that have the same
part number:
EXEC SQL SELECT PartName,
1 QtyOnHand
2 INTO :PartName,
3 :QtyOnHand
4 FROM PurchDB.Parts,
5 PurchDB.Inventory
6 WHERE PurchDB.Parts.PartNumber =
7 PurchDB.Inventory.PartNumber
Whenever two or more columns in a query have the same name but belong to
different tables, you avoid ambiguity by qualifying the column names with
table and owner names. Because the columns specified in the join
condition shown above have the same name (PartNumber) in both tables,
they are fully qualified with table and owner names (PurchDB.Parts and
PurchDB.Inventory). If one of the columns named PartNumber were named
PartNum, the WHERE clause could be written without having the fully
qualified column name as follows:
WHERE PartNumber = PartNum
ALLBASE/SQL creates a row for the query result whenever the PartNumber
value in one table matches that in the second table. Any row containing
a null PartNumber is excluded from the join, as are rows that have a
PartNumber value in one table, but not the other:
![[]](../../pic3k/M011857/FFN743c50.gif)
Figure 6-1. Sample Query Joining Multiple Tables
You can also join a table to itself. This type of join is useful when
you want to identify pairs of values within one table that have certain
relationships.
The PurchDB.SupplyPrice table contains the unit price, delivery time, and
other data for every vendor that supplies any part. Most parts are
supplied by more than one vendor, and prices vary with vendor. You can
join the PurchDB.SupplyPrice table to itself in order to identify for
which parts the difference among vendor prices is greater than $50. The
query and its result would appear as follows:
The query:
EXEC SQL SELECT X.PartNumber,
1 X.VendorNumber,
2 X.UnitPrice,
3 Y.VendorNumber,
4 Y.UnitPrice
5 INTO :PartNumber,
6 :VendorNumber1,
7 :UnitPrice1,
8 :VendorNumber2,
9 :UnitPrice2
1 FROM PurchDB.SupplyPrice X,
2 PurchDB.SupplyPrice Y
3 WHERE X.PartNumber = Y.PartNumber AND
4 X.UnitPrice > (Y.UnitPrice + 50.00)
The result:
----------------+------------+--------------+------------+--------------
PARTNUMBER |VENDORNUMBER|UNITPRICE |VENDORNUMBER|UNITPRICE
----------------+------------+--------------+------------+--------------
1123-P-01 | 9007| 550.00| 9002| 450.00
1123-P-01 | 9012| 525.00| 9002| 450.00
1123-P-01 | 9007| 550.00| 9008| 475.00
1123-P-01 | 9007| 550.00| 9003| 475.00
1433-M-01 | 9007| 700.00| 9003| 645.00
1623-TD-01 | 9011| 1800.00| 9015| 1650.00
|___________________________|
|
These vendors charge
at least $50 more for
a part than the vendors
identified in the next
two columns.
To obtain such a query result, ALLBASE/SQL joins one copy of the table
with another copy of the table, using the join condition specified in the
WHERE clause:
* You name each copy of the table in the FROM clause by using a join
variable. In this example, the join variables are X and Y. Then
you use the join variable to qualify column names in the select
list and other clauses in the query.
* The join condition in this example specifies that for each part
number, the query result should contain a row only when the price
of the part from vendor to vendor differs by more than $50.
Join variables can be used in any query as a shorthand way of referring
to a table, but they must be used in queries that join a table to itself
so that ALLBASE/SQL can distinguish between the two copies of the table.
Selecting Using Views
Views are used to restrict data visibility as well as to simplify data
access:
* Data visibility can be limited using views by defining them such
that only certain columns and/or rows are accessible through them.
* Data access can be simplified using views by creating views based
on joins or containing columns that are derived from expressions
or aggregate functions.
The sample database has a view called PurchDB.VendorStatistics, defined
as follows:
EXEC SQL CREATE VIEW PurchDB.VendorStatistics
1 (VendorNumber,
2 VendorName,
3 OrderDate,
4 OrderQuantity,
5 TotalPrice)
6 AS
7 SELECT PurchDB.Vendors.VendorNumber,
8 PurchDB.Vendors.VendorName,
9 OrderDate,
1 OrderQty,
2 OrderQty * PurchasePrice
3 FROM PurchDB.Vendors,
4 PurchDB.Orders,
5 PurchDB.OrderItems
6 WHERE PurchDB.Vendors.VendorNumber =
7 PurchDB.Orders.VendorNumber AND
8 PurchDB.OrderItems.OrderNumber =
9 PurchDB.OrderItems.OrderNumber
This view combines information from three base tables to provide a
summary of data on existing orders with each vendor. One of the columns
in the view consists of a computed expression: the total cost of an item
on order with the vendor.
Note that the select list of the SELECT command defining this view
contains some qualified and some unqualified column names. Columns
OrderDate, OrderQty, and PurchasePrice need not be qualified, because
these names are unique among the column names in the three tables joined
in this view. In the WHERE clause, however, both join conditions must
contain fully qualified column names since the columns are named the same
in each of the joined tables.
You can use a view in a query without restriction. In the FROM clause,
you identify the view as you would identify a table. When you reference
columns belonging to the view, you use the column names used in the view
definition. In the view above, for example, the column containing
quantity-on-order is called OrderQuantity, not OrderQty as it is in the
base table (PurchDB.OrderItems).
The VendorStatistics view can be used to quickly determine the total
dollar amount of orders existing for each vendor. Because the view
definition contains all the details for deriving this information, the
query based on this view is quite simple:
EXEC SQL SELECT VendorNumber,
1 SUM(TotalPrice)
2 INTO :VendorNumber,
3 :Sum
4 FROM PurchDB.VendorStatistics
5 GROUP BY VendorNumber
The query result appears as follows:
------------+----------------------
VENDORNUMBER|(EXPR)
------------+----------------------
9001| 31300.00
9002| 6555.00
9003| 6325.00
9004| 2850.00
9006| 2010.00
9008| 12460.00
9009| 7750.00
9010| 9180.00
9012| 12280.00
9013| 8270.00
9014| 2000.00
9015| 17550.00
Although you can use views in queries without restriction, you can use
only some views to INSERT, UPDATE, or DELETE rows:
* You cannot INSERT, UPDATE, or DELETE using a view if the view
definition contains one of the following:
* Join operation
* Aggregate function
* DISTINCT option
* GROUP BY clause
* You cannot INSERT using a view if any column of the view is
computed in an arithmetic expression.
The PurchDB.VendorStatistics view cannot be used for any INSERT, UPDATE,
or DELETE operation because it is based on a three-table join and
contains a column (TotalPrice) derived from a multiplication operation.
Query Efficiency
Three clauses in the SELECT command have an effect on the execution speed
of queries:
WHERE
GROUP BY
ORDER BY
As discussed earlier, the WHERE clause consists of one or more
predicates. Predicates can be evaluated more quickly when they can be
optimized by ALLBASE/SQL.
The following predicates are optimizable when all the data types within
them are the same (in the case of DOUBLE PRECISION data, the precisions
and scales of the different values must be the same). Note that after
optimization, ALLBASE/SQL may perform an index scan to access data; an
index scan improves data access speed by making use of an index on one or
more of the columns in the predicate:
* WHERE Column1 ComparisonOperator Column2 where ComparisonOperator
is one of the following: =, >, >=, <, or <=. An index may be
used if Column1 and Column2 are in different tables and an index
exists on either column:
WHERE PurchDB.Parts.PartNumber = PurchDB.SupplyPrice.PartNumber
* WHERE Column1 ComparisonOperator {Constant or HostVariable} where
ComparisonOperator is one of the following: =, >, >=, <, or <=.
An index may be used if one exists on Column1; however, an index
may be used if a host variable appears in the predicate only if
the comparison operator is an equal sign (=) :
WHERE SupplyPrice = :SupplyPrice
* WHERE Column1 BETWEEN {Column2 or Constant or HostVariable} AND
{Column2 or Constant or HostVariable}. An index may be used if
Column1 is the only column name in the predicate and an index
exists on it.
* WHERE Column1 <> {Column2 or Constant or Host Variable} Although
this kind of predicate is optimizable, an index is never used:
WHERE VendorState <> :VendorState
The lower the cluster count of an index, the greater the chance
ALLBASE/SQL will use it when an appropriate index is available. Cluster
count indicates the number of times ALLBASE/SQL has to access a different
data page to retrieve the next row during an index scan. Refer to the
ALLBASE/SQL Database Administration Guide for information on how to
optimize the cluster count of an index.
The following predicates are not optimizable, and an index is never used:
* Predicates containing arithmetic expressions:
WHERE Column1 > Column2 * :HostVariable
* LIKE predicates:
WHERE Column1 LIKE :HostVariable
* Predicates joined by the logical operator OR:
WHERE Column1 = Column2
OR Column1 > Constant
When a query does not contain a WHERE clause, an index is never used,
because all rows from tables in the FROM clause containing columns in the
select list qualify:
EXEC SQL SELECT *
1 INTO :HostVariableList
2 .
3 .
4 .
5 FROM OwnerName.TableName
When an index is not used, ALLBASE/SQL performs what is known as a serial
scan to locate rows. When a serial scan is performed instead of an index
scan, the entire table is locked, regardless of the automatic locking
mode of the table.
The optimization and locking ALLBASE/SQL performs for the WHERE clause in
the SELECT command also applies to the WHERE clause in the UPDATE and
DELETE commands.
When a query contains a GROUP BY and/or an ORDER BY clause, ALLBASE/SQL
must sort rows. The time required for sorting increases as the number of
qualifying rows increases.
Sorting occurs in DBEFiles associated with the SYSTEM DBEFileSet.
Therefore enough file space must be available in this DBEFileSet when the
query is executed to accommodate the sort operations. Guidelines
on space requirements can be found in the ALLBASE/SQL Database
Administration Guide .