MP Users

DML Features

HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003

3-15

SELECT Statement

Table Reference Within FROM Clause

In NonStop SQL/MP, a table reference within a FROM clause of a SELECT statement

can be a table, view, or joined table. The maximum number of tables that can be

specified in a FROM clause is 16. This maximum includes the underlying base tables

of views.

In NonStop SQL/MX, a table reference can also be a query expression or, stated more

precisely, the derived table determined by the evaluation of a query expression. A

query expression can be the UNION or JOIN of two (or more) table references. A

query expression can also be a simple table—a table value constructor generated by a

VALUES clause, TABLE clause, or a subquery.

In NonStop SQL/MX, an item in the select list can be a subquery:

SELECT (SELECT a FROM s WHERE b=t.b) FROM t;

In NonStop SQL/MX, a joined table can be used as a table reference in the FROM

clause of a SELECT statement. An SQL:1999 joined table is specified by connecting

the names of the two tables with the keywords NATURAL, CROSS, INNER, LEFT, or

RIGHT preceding the JOIN keyword. NonStop SQL/MP supports LEFT and INNER

joins only.

For example, the clause PARTS NATURAL JOIN ODETAIL (as a table reference in a

FROM clause) joins rows only where the values of all columns that have the same

name in both tables match. The clause PARTS NATURAL LEFT JOIN ODETAIL joins

rows where the values of all columns that have the same name in both tables match

plus rows from PARTS (the table to the left of the JOIN keyword) that do not meet this

condition.

GROUP BY Clause and Numerical Arguments

In NonStop SQL/MP, the GROUP BY clause allows you to use numerical arguments,

for example:

SELECT a + b FROM t GROUP BY 1;

NonStop SQL/MX does not allow the use of numerical arguments with the GROUP BY

clause. However, you can use a derived query to achieve the same purpose as shown

SELECT c FROM (SELECT a + b FROM t) x(c) GROUP BY c;

Placement of GROUP BY Clause

NonStop SQL/MX requires the GROUP BY clause, if one exists, to precede a HAVING

clause. In NonStop SQL/MP, you can place the clauses in any order.