SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-107
Tables
A subquery within the WHERE clause of another subquery is at a different level,
however, so this query has two levels of nesting:
SELECT * FROM TABLE1
WHERE A = (SELECT P FROM TABLE2
WHERE Q = (SELECT X FROM TABLE3
WHERE Y = 2))
Correlated Subqueries When Providing Comparison Values
In the search condition of a subquery, when you refer to columns of any table or view
defined in an outer query, the reference is called an outer reference. A subquery
containing an outer reference is called a correlated subquery.
If you refer to a column name that occurs in more than one outer query, you must
qualify the column name with the correlation name of the table or view to which it
belongs. Similarly, if you refer to a column name that occurs in the subquery and in one
or more outer queries, you must qualify the column name with the correlation name of
the table or view to which it belongs. The correlation name is known to other
subqueries at the same level, or to inner queries but not to outer queries.
If you use the same correlation name at different levels of nesting, an inner query uses
the one from the nearest outer level. MXCI checks the FROM clause of the subquery
first, then its outer query, and so forth, until it determines the applicable table or view.
Tables
A table is a logical representation of data in which a set of records is represented as a
sequence of rows, and the set of fields common to all rows is represented by columns.
A column is a set of values of the same data type with the same definition. The
intersection of a row and column represents the data value of a particular field in a
particular record.
Every table must have one or more columns, but the number of rows can be zero.
There is no inherent order of rows within a table.
You create an SQL/MX or SQL/MP user table by using the CREATE TABLE statement
in the appropriate environment. The definition of a user table within the statement
includes this information:
•
Name of the table
•
Name of each column of the table
•
Type of data you can store in each column of the table
•
Other information about the table, including the physical characteristics of the file
that stores the table (for example, the storage order of rows within the table)
An SQL/MP table is described in an SQL/MP catalog and stored in a physical file in the
Guardian environment. An SQL/MP table name must be a Guardian name of the form:
[\node.][[$volume.]subvol.]filename