Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 183 184 185 186 187 188 < 189 > 190 191 192 193 194 195 .. 241 >> Next


• For each subquery of a DML statement, always specify a FROM clause that includes all tables referenced in the subquery.

• For the main query of a SELECT statement, always specify a FROM clause that includes all tables referenced in the main query.

• Do not specify joined tables in the main queries of DELETE and UPDATE statements. Restrict joins to subqueries instead.

There is no way to change an ABORT statement that references some table in an existing application so that it can work in the new release and prior releases. This is because the new syntax requires a FROM clause for an ABORT statement that references some table and this FROM clause is not available in syntax prior to the current release.

Correlated subqueries can be used to specify a combination of equality and inequality constraints with the subquery.

For example, to select the names of all students who are younger than all students at the same grade, the following query can be used:

SELECT name FROM student st1 WHERE age < ALL

(SELECT age FROM student st2 WHERE st1.grade=st2.grade AND

st1.stno <> st2.stno);

Correlated subqueries impact the SELECT, UPDATE, DELETE, and ABORT statements. See also the “Examples” section for each of these statements, in Chapter 8, “Teradata SQL Syntax Guide.”

The SELECT statement form of:

SELECT itemlist FROM tablelist WHERE itemlist operator (SELECT ...);

was supported in prior release and is still supported. However, there is a restriction on the implementation in that the subquery in parentheses, was only executed once, and the results of that execution was joined with the results of the execution of the main query.

The proper implementation of correlated subqueries requires that the subquery in parenthesis be executed once for each result row of the main query.

The following example demonstrates the difference between the implementation in previous releases, and that of correlated subqueries (in current and future releases).

9-10

Teradata RDBMS for UNIX SQL Reference
Advanced SQL

Correlated Subqueries

Table t1 has columns fl and f2 while table t2 has columns f3 and f4. The following four rows exist in the two tables.

f1 f2
100 1
50 1
20 2
40 2

f3 f4
100 1
50 1
20 2
40 2

• In releases prior to V2R2.0, the following select statement:

SELECT * FROM t1 WHERE fl IN (

SELECT MAX(f3) FROM t2 WHERE t1.f2=t2.f4);

returns only one response row with f1=100 and f2=1 since executing the subquery:

SELECT MAX(f3) FROM t2 WHERE t1.f2=t2.f4;

returns a single response row.

• In release V2R2.0 and higher, the implementation of correlated subqueries returns two response rows of f1=100, f2=1 and f1=40, f2=2. This is because the subquery is executed four times, once for each row in t1.

Only two response rows are returned, however, due to the max(f3) constraint as two of the executions result in defining a response row where f1 is not in the result. The four executions return the response rows of:

f3 f4

100 1

100 1

40 2

40 2

Only the first and fourth row of tl have a value of fl in this

result set. If the MAX function had not been specified then all four rows of t1 would have been returned under either implementation.

Teradata RDBMS for UNIX SQL Reference

9-11
Advanced SQL

Correlated Subqueries and SELECT

Correlated Subqueries and SELECT

A SELECT can be used in an outer query, main query or subquery. All tables referenced in the SELECT should be specified in a FROM clause.

If a SELECT is specified in the main query, any table referenced in the main query should be specified in the FROM clause of this SELECT. Otherwise, if a SELECT is specified in a subquery, any table referenced in the subquery should be specified in the FROM clause of either the subquery or some outer query.

The FROM clause is not required in the main query (refer to item 4 above, under “Rules”). However, it is highly recommended since a future release may enforce that all tables referenced in a SELECT must be specified in a FROM clause. A Warning message is given if the referenced table does not appear in the FROM clause.

A FROM tname clause is required in a query specification.

The following queries are:

Examples 0 . . ,

• Syntactically correct in all releases:

SELECT date, time;1

SELECT x1,x2 FROM t1,t2 WHERE t1.x1=t2.x2;

SELECT x1 FROM t1 WHERE x1 IN (SELECT x2 FROM t2);

SELECT t1.x1;

SELECT t1.x1 FROM t1 WHERE t1.x1=t2.x2;

• Syntactically correct in releases prior to V2R2.0 but not recommended in the current release.

SELECT * FROM t1 WHERE t1.x1 IN (SELECT t2.x2);

returns an error message.

SELECT * FROM t1 WHERE t1.x1 IN (SELECT x2 FROM t2 WHERE t2.n=t3.n);

returns an error message.

• Illegal in all releases prior to V2R2.0 (as long as no table named p with a column named department exists in the default database) but legal in the current and future releases:

SELECT name FROM personnel p WHERE salary =
Previous << 1 .. 183 184 185 186 187 188 < 189 > 190 191 192 193 194 195 .. 241 >> Next