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 .. 101 102 103 104 105 106 < 107 > 108 109 110 111 112 113 .. 241 >> Next


O.CourseNo O.Location E.EmpNo
C100 El Segundo 236
C100 El Segundo 668
C2 0 0 Dayton Null
C4 0 0 El Segundo Null

Teradata RDBMS for UNIX SQL Reference

7-53
Queries: The SELECT Statement

INTERSECT Operator

INTERSECT Operator

Introduction

The INTERSECT operator returns only the result rows that exist in the result of both queries.

Unless the ALL option is used, duplicate rows are eliminated from the final result

If the ALL option is specified, duplicate rows are retained. The ALL option may be specified for as many INTERSECT operators as are used in a multi-statement query.

When the SQL flagger is enabled, the INTERSECT operator is flagged as not entry level ANSI and the ALL option is flagged as non-ANSI.

Form

The form of the INTERSECT operator is:

query factor—INTERSECT —³-1— query term-

lALL-1

-H

Example: INTERSECT

Assume that two tables contain the following rows: SPart table SLocation table

SuppNo PartNo SuppNo SuppLoc
100 P2 100 London
101 P1 101 London
102 P1 102 Toronto
103 P2 103 Tokyo

To then select supplier number (SuppNo) for suppliers located in London (SuppLoc) who supply part number P1 (PartNo), the following request could be used:

SELECT SuppNo FROM SLocation WHERE SuppLoc = 'London'

INTERSECT

SELECT SuppNo FROM SPart WHERE PartNo = 'P1';

The result of this request is:

SuppNo

101

FF06A021

7-54

Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement

EXCEPT (MINUS) Operator

EXCEPT (MINUS) Operator

Introduction

The EXCEPT (MINUS) operator determines which result rows returned by the first SELECT query are not contained in the second SELECT query’s result rows. That is, the result rows returned by the second SELECT query are subtracted from the result rows returned by the first SELECT query.

Form

The form of the EXCEPT (MINUS) operator is:

tTCALLJ

-MINUS-1 lALL

FF06A022

Example

The ALL option causes duplicate rows to be retained. If ALL is not specified, duplicate rows are eliminated in the final result. The ALL option may be specified for more than one EXCEPT (MINUS) operator in a multi-statement query

Note: When the SQL flagger is enabled, the EXCEPT(MINUS) operator and the ALL option are flagged as non-ANSI.

Using the SLocation and SPart tables defined earlier, the following query could be used to determine the suppliers in London who do not stock part P1:

SELECT SuppNo FROM SLocation

WHERE UPPER (SuppLoc) = 'LONDON'

EXCEPT

SELECT SuppNo FROM SPart

WHERE UPPER (PartNo) = 'P1' ;

The result of this query is:

SuppNo

100

Teradata RDBMS for UNIX SQL Reference

7-55
Queries: The SELECT Statement

EXCEPT (MINUS) Operator

7-56

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

Chapter 8

Teradata SQL Syntax Guide

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

About This Chapter

Introduction

Teradata SQL Syntax Abbreviations

About This Chapter

This chapter documents SQL statements valid for the Teradata RDBMS with the exception of those statements that are specific to embedded SQL.

Those statements are documented in the manual Teradata Application Programming With Embedded SQL for C, COBOL, and PL/I.

With the exception of the abbreviations marked with (*), the following syntax abbreviations are Teradata extensions to ANSI. They are flagged as non-ANSI when the SQL flagger is enabled.

Abbreviation Syntax
BT BEGIN TRANSACTION
CD CREATE DATABASE
CM CREATE MACRO
CT CREATE TABLE
CV CREATE VIEW
DEL DELETE
ET END TRANSACTION
EXEC EXECUTE
INS INSERT
SEL SELECT
SS SET SESSION
UPD UPDATE

Teradata RDBMS for UNIX SQL Reference

8-1
Teradata SQL Syntax Guide

About This Chapter

Data Type Abbreviations

Access Rights Abbreviations

The following data type abbreviations appear in views and tables of the Data Dictionary.

Abbreviations Listed in Data Dictionary Tables and Views Data Type
BF BYTE
BV VARBYTE
CF CHARACTER
CV VARCHAR
D DECIMAL NUMERIC
DA DATE
F FLOAT REAL DOUBLE PRECISION
GF GRAPHIC
GV VARGRAPHIC LONG VARGRAPHIC
I INTEGER
I1 BYTEINT
I2 SMALLINT

Note: The GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC abbreviations will change in a future release.

The following lists the abbreviations used for access rights in the Data Dictionary:

Abbreviations Listed in Data Dictionary Tables and Views Access Right
CM CREATE MACRO
CT CREATE TABLE
CU CREATE USER
CV CREATE VIEW
D DELETE

8-2

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

About This Chapter

Abbreviations Listed in Data Dictionary Tables and Views Access Right
DM DROP MACRO
DU DROP USER
DV DROP VIEW
E EXECUTE
I INSERT
RF REFERENCE
IX CREATE/DROP INDEX

Teradata RDBMS for UNIX SQL Reference

8-3
Teradata SQL Syntax Guide

ABORT

ABORT

ABORT is a synonym for ROLLBACK. Refer also to the FuncUon “ROLLBACK” statement.
Previous << 1 .. 101 102 103 104 105 106 < 107 > 108 109 110 111 112 113 .. 241 >> Next