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 .. 76 77 78 79 80 81 < 82 > 83 84 85 86 87 88 .. 241 >> Next

UPDATE Employee SET Salary=Salary + 200 WHERE DeptNo NOT IN (100, 700) UPDATE Employee SET Salary=Salary + 200 WHERE (DeptNo ë= 100) AND (DeptNo ë= 700) ;

The second form of IN and NOT IN is either of the following two forms:

-expr

Lnot-I

(-L expiLL)

-IN —(— subquery— ) ¦

Lnot-I

-IN — (— subquery —) -

HH01A002

where subquery is a SELECT statement that returns values that satisfy the search condition. The subquery must meet the following criteria:

• be enclosed in parentheses,

• not end with a semicolon, and

• select the same number of expressions as are defined in the expr-list.

A statement result does not include null column values when IN is used with a subquery.

• For a query of the form:

SELECT ... FROM Tl WHERE x NOT IN (SELECT y FROM T2);

If one of the y values is null, then no T1 rows are returned for the entire query.

6-66

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Logical Expressions: IN and NOT IN Operator

Examples (Form 2 of IN and NOT IN)

• If Some rowS are returned by the Subquery, and if x containS Some null valueS, then thoSe T1 rowS that contain a null in x are not returned.

• For a query in the form:

SELECT ... FROM Tl

WHERE expression_list1 NOT IN (SELECT expression_list2 FROM T2);

IF a null is . . . THEN . . .
the first field in expressionlist2 no rowS from T1 are returned
in a field other than the firSt field of expressionlist2 Some rowS may be returned
in the firSt field in expreSSionliSt1 of only Some of the returned rowS the T1 rowS containing a null in the first field of expressionlist1 are not returned.
The following statement searches for the names of all employees who work in Atlanta.
SELECT Name FROM Employee

WHERE DeptNo IN

(SELECT DeptNo FROM Department WHERE Loc = 'ATL') ;

USing a Similar example but aSSuming that the DeptNo iS divided into two columns, the following statement could be used:

SELECT Name FROM Employee

WHERE (DeptNoA, DeptNoB) IN

(SELECT DeptNoA, DeptNoB FROM Department WHERE Loc = 'LAX') ;

Teradata RDBMS for UNIX SQL Reference

6-67
SQL Expressions

Logical Expressions: IS NULL and IS NOT NULL Operator

Logical Expressions: IS NULL and IS NOT NULL Operator

You may search for or exclude nulls in an expression by using the IS Introduction 6 NULL and IS NOT NULL operators.

The format is:

Form6

- expression----IS —³----------,— NULL

L NOT-I

NOT-

HH01A042

For example, to search for the names of all employees who have not Examples 6 been assigned to a department, enter the following statement:

SELECT Name FROM Employee WHERE DeptNo IS NULL;

The result of this query is the names of all employees with a null in the DeptNo field.

Conversely, to search for the names of all employees who have been assigned to a department, you could enter the following statement:

SELECT Name FROM Employee WHERE DeptNo IS NOT NULL;

This query returns the names of all employees with a non-null value in the DeptNo field.

Searching for NULL and NOT-NULL in the Same Statement

If you are searching for nulls and non-null values in the same statement, the search condition for null values must be listed separately.

For example, to select the names of all employees without the job title of “Manager” or “Vice Pres”, plus the names of all employees with a null in the JobTitle column, you must enter the statement as follows:

SELECT Name, JobTitle FROM Employee

WHERE (JobTitle NOT IN ('Manager' OR 'Vice Pres'))

OR (JobTitle IS NULL) ;

6-68

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Logical Expressions: IS NULL and IS NOT NULL Operator

Searching a Table That May Contain NULLs

You must be careful when searching a table that may contain nulls. For example, if the EdLev column containS nullS and you enter the following:

SELECT Name, EdLev FROM Employee WHERE (EdLev < 16) ;

the reSult containS only the nameS of employeeS with an education level of less than 16 years.

To make Sure that the reSult of a Statement will contain nullS, the Statement muSt be entered aS:

SELECT Name, EdLev FROM Employee

WHERE (EdLev < 16) or (EdLev IS NULL) ;

Teradata RDBMS for UNIX SQL Reference

6-69
SQL Expressions

Logical Expressions: EXISTS

Logical Expressions: EXISTS

Exists conditions are used to test a specified table, normally a IntrOducUon derived table, for the existence of at least one row (i.e. to test

whether the table in question is non-empty).

The EXISTS predicate iS Supported aS the predicate of the Search condition in a WHERE clause.

The general form of its usage is:

Form6

------1-----p EXISTS —subquery------------

Lnot-I

HH01A047

The meaning of the EXISTS predicate iS to teSt the reSult of the Subquery.

If execution of the subquery would return response rows then the Usage N°tes where condition is considered satisfied. Use of the NOT qualifier for

Previous << 1 .. 76 77 78 79 80 81 < 82 > 83 84 85 86 87 88 .. 241 >> Next