Teradata RDBMS forUNIX SQL Reference - NCR

The SELECT statement is the only SQL statement that can use the set operators (query expressions) UNION, INTERSECT, and MINUS. The set operators allow the user to manipulate the answers to two or more queries by combining the results of each query into a single result set.

A select operation can reference data in one or more tables and/or views. In this case, the statement defines a join of the tables or views.

When the result of a SELECT statement is returned to a user, the activity count that is part of the success response indicates the total number of returned rows. If no rows are returned, the activity count equals zero.

The SELECT statement is the most frequently used SQL statement. The SELECT statement may be used to specify:

 The set of result data that is returned

 The order in which result data is returned

 The format in which result data is returned

The abbreviation for the SELECT statement is SEL, but the full spelling is recommended for ANSI compliance.

The following sections describe how to use the SELECT statement to request data from the Teradata RDBMS. Topics include:

A SELECT can be used in an outer query, main query or subquery. The SELECT syntax has changed in this release, to require that tables referenced in the SELECT must be specified in a FROM clause if there are any subqueries in the request.

Requests with no subqueries are still allowed to avoid regression problems. Such requests are not ANSI-compliant and NCR strongly

Teradata RDBMS for UNIX SQL Reference

7-7
Queries: The SELECT Statement

SELECT

Examples

Example 1: Valid SELECT in All Releases

Example 2: Non Valid SELECT in Current Release, Valid Previously

Example 3: Valid SELECT, Previously Invalid

suggests that new scripts avoid using this non-ANSI syntax.and that existing scripts be revised as is practicable.

If a SELECT is specified in a subquery, any table referenced in the subquery must be specified in the FROM clause of either the subquery or some outer query.

The following examples illustrate the use of SELECT:

The following queries are 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);

The following queries are syntactically correct in all previous releases but now invalid:

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

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

These statements are not valid because table t2 is referenced in the subquery and not in the main query. Also, subqueries always require a FROM clause.

The following query is illegal in previous releases (as long as no table named p with a column named department exists in the default database) but legal in the current release:

The above query returns the name of employees who have the highest salary in each department.

1This query references no tables, hence no FROM clause is needed. This form is flagged as non-ANSI SQL when the SQL flagger is enabled.

7-8

Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement

SELECT

Example 4: SELECT Executed Differently in Previous Releases

In previous releases, the above query will not retrieve any publisher name if any publisher has some books in the library, which is not a very useful result in this case.

In V2R2.0 and future releases, even if some publishers might have some books in the library, the above query still retrieves the name of a publisher if this publisher has no books in the library. The difference occurs because of the implementation of correlated subqueries. See Chapter 9, Advanced SQL, Correlated Subqueries, for a detailed explanation.

The following query is syntactically correct in all releases but has a different meaning in the current release than in previous releases:

SEL PubName FROM Publisher WHERE 0 =

(SELECT COUNT(*) FROM book WHERE book.PubNum=Publisher.pubnum);

Teradata RDBMS for UNIX SQL Reference

7-9
Queries: The SELECT Statement

Simple SELECT Statement

Simple SELECT Statement

A simple form of the SELECT statement returns a result that is Function determined by a list of expressions.

Syntax

SELECT SEL-----

expr

tname

¦ FROM  tname -

TTT

FF06A024

*

The following four examples demonstrate the use of the simple Examples SELECT statement.

In the following example, Name is the only expression in the Example I SELECT expr-list.

The statement returns all of the employee names in the Employee table: