17.05 - Example: Determining the Number and Names of Employees - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

To determine the department number and names of all employees in departments 500 and 600, the UNION operator could be used as follows:

   SELECT DeptNo, Name 
   FROM Employee 
   WHERE DeptNo = 500
   UNION
   SELECT DeptNo, Name 
   FROM Employee 
   WHERE DeptNo = 600 ; 

This query returns the following rows.

DeptNo Name
500 Carter J
500 Inglis C
500 Marston A
500 Omura H
500 Reed C
500 Smith T
500 Watson L
600 Aguilar J
600 Kemper R
600 Newman P
600 Regan R

The same results could have been returned with a simpler query, such as the following:

   SELECT Name, DeptNo 
   FROM Employee 
   WHERE (DeptNo = 500) 
   OR (DeptNo = 600); 

The advantage to formulating the query using the UNION operator is that if the DeptNo column is the primary index for the Employee table, then using the UNION operator guarantees that the basic selects are prime key operations. There is no guarantee that a query using the OR operation will make use of the primary index.