Example: Determining the Number and Names of Employees - Teradata Vantage

To determine the department number and names of all employees in departments 500 and 600, the UNION operator can 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.

The same results can be 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 uses the primary index.