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

the EXISTS predicate reverSeS the SenSe of the teSt. Execution of the Subquery doeS not, in fact, return any reSponSe rowS. It Simply returnS a boolean to indicate whether reSponSeS would or would not be returned.

EXISTS predicate iS uSed to teSt the exiStence of Specified rowS of a Subquery. In general, EXISTS can be uSed to replace compariSonS with IN and NOT EXISTS can be uSed to replace compariSonS with NOT IN. However, the reverSe iS not true. There are problemS which can be solved only by using EXISTS and/or NOT EXISTS predicate (see the example in the section ‘For All’).

To select rows of t1 whose values in column x1 are equal to the value Example in column x2 of t2, one of the following querieS can be uSed:

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

SELECT * FROM tl WHERE EXISTS (SELECT * FROM t2 WHERE t1.x1=t2.x2);

To Select rowS of t1 whoSe valueS in column x1 are not equal to any value in column x2 of t2, one of the following querieS can be uSed:

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

SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t1.x1=t2.x2);

SELECT 'T1 is not empty' WHERE EXISTS (SEL * FROM t1);

SELECT 'T1 is empty' WHERE NOT EXISTS (SEL * FROM t1);

6-70

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Logical Expressions: EXISTS

EXISTS Predicate Versus NOT IN and Null Values

The NOT EXISTS predicate should be used instead of NOT IN if the following conditions are true:

• Some column of the NOT IN condition is defined as nullable.

• Any rows from the main query with a Null value in any column of the NOT IN condition should always be returned.

• Any NULL values returned in the select list of the subquery should not prevent any rows from the main query from being returned.

For example, the following query with NOT IN:

SELECT dept, DeptName FROM Department WHERE Dept NOT IN (SELECT Dept FROM Course);

should be replaced by a query with NOT EXISTS as follows:

SELECT dept, DeptName FROM Department WHERE NOT EXISTS (SELECT Dept FROM Course WHERE Course.Dept=Department.Dept);

if all of the above conditions are true. That is, either Course.Dept or Department.Dept is nullable and a row from Department with a null value for Dept should be returned and a Null value in Course.Dept should not prevent rows from Department from being returned.

Two nested NOT EXISTS can be used to express a SELECT For ALL statement which embodies the notion of “for all (logical V) the

values in a column, there exists (logical$)...” For example the query to select a ‘true’ value if the library has at least one book for all the publishers can be expressed as follows:

SELECT 'TRUE' WHERE NOT EXISTS

(SELECT * FROM publisher pb WHERE NOT EXISTS

(SELECT * FROM book bk WHERE pb.PubNum=bk.PubNum);

Select all student names who have registered in at least one class offered by some department.

SELECT SName, SNo FROM student s WHERE EXISTS

(SELECT * FROM department d WHERE EXISTS

(SELECT * FROM course c, registration r, class cl WHERE

c.Dept=d.Dept AND c.CNo=r.CNo AND s.SNo=r.SNo

AND r.CNo=cl.CNo AND r.Sec=cl.Sec));

The content of the student table is as follows:

Sname SNo
Helen Chu 1
Alice Clark 2
Kathy Kim 3
Tom Brown 4

Example: EXISTS with Correlated Subqueries

Teradata RDBMS for UNIX SQL Reference

6-71
SQL Expressions

Logical Expressions: EXISTS

The content of the department table is as follows:

Dept DeptName
100 Computer Science
200 Physic
300 Math
400 Science

The content of course table is as follows:

The content of the registration table is as follows:

6-72

Teradata RDBMS for UNIX SQL Reference
Example: NOT EXISTS with Correlated Subqueries

SQL Expressions

Logical Expressions: EXISTS

The following rows are returned:

SName

Helen Chul Alice Clark Kathy Kim

See Chapter 9, “Advanced SQL,” for a full explanation of correlated subqueries.

Select all student names who have registered in at least one class offered by each department which offers a course.

SELECT SName, SNo FROM student s WHERE NOT EXISTS

SELECT * FROM department d WHERE d.Dept IN

SELECT Dept FROM course) AND NOT EXISTS

SELECT * FROM course c, registration r, class cl WHERE

c.Dept=d.Dept AND c.CNo=r.CNo AND s.SNo=r.SNo

AND r.CNo=cl.CNo AND r.Sec=cl.Sec));

With the contents of the tables as in example 1, the following rows are returned:

SName SNo

Helen Chu l

SNo

*

2

3

Teradata RDBMS for UNIX SQL Reference

6-73
SQL Expressions

Logical Expressions: LIKE Partial-String Operator

Logical Expressions: LIKE Partial-String Operator

Introduction

The partial-string operator is LIKE.

LIKE iS uSed to Search for character String data that partially matcheS a given String.

Form

The LIKE partial-string operator has the forms:

---- expr-

_-----1— LIKE—pattern_expr -,—

LnotJ L

ESCAPE— escape_character

T

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