15.00 - INTERSECT Operator - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

INTERSECT Operator

Purpose  

Returns only the rows that exist in the result of both queries.

Syntax  

where:

 

Syntax element …

Specifies …

query_expression_1

a complete SELECT statement to be INTERSECTed with query_expression_2.

See “Syntax for query_factor” on page 1147.

ALL

that duplicate rows are to be retained for the INTERSECT.

query_expression_2

a complete SELECT statement to be INTERSECTed with query_expression_1.

See “Syntax for query_term” on page 1147.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

The ALL option is a Teradata extension to the ANSI standard.

Rules for INTERSECT

The following rules apply to the use of INTERSECT:

  • In addition to using INTERSECT within simple queries, you can use INTERSECT within the following operations:
  • Derived tables
  • Note: You cannot use the HASH BY or LOCAL ORDER BY clauses in derived tables with set operators.

  • Subqueries
  • INSERT … SELECT statements
  • View definitions
  • Each query connected by INTERSECT is executed to produce a result consisting of a set of rows. The intersection must include the same number of columns from each table in each SELECT statement (more formally, they must be of the same degree), and the data types of these columns should be compatible.
  • INTERSECT cannot be used within the following:
  • SELECT AND CONSUME statements.
  • WITH RECURSIVE clause
  • CREATE RECURSIVE VIEW statements
  • Attributes of a Set Result

    The data type, title, and format clauses contained in the first SELECT statement in the intersection determine the data type, title, and format information that appear in the final result.

    Attributes for all other SELECT statements in the query are ignored.

    Data Type of Nulls

    When you specify an explicit NULL for any intersection operation, its data type is INTEGER. For an example of this principle using the UNION operator, see “Example 5: Effect of Explicit NULLs on Data Type of a UNION” on page 1173.

    On the other hand, column data defined as NULL has neither value nor data type and evaluates like any other null in a scalar expression.

    Duplicate Row Handling

    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 can be specified for as many INTERSECT operators as are used in a multistatement query.

    Example  

    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), use the following request:

       SELECT SuppNo FROM SLocation 
       WHERE SuppLoc = 'London' 
       INTERSECT 
       SELECT SuppNo FROM SPart 
       WHERE PartNo = 'P1';

    The result of this request is:

       SuppNo
       ------
          101