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

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

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.