Example: Performing a Union Operation to Find Hours Worked - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

Suppose you want to know the number of man-hours charged by each employee who is working on a project. In addition, suppose you also wanted the result to include the names of employees who are not working on a project.

To do this, you would have to perform a union operation as illustrated in the following example.

   SELECT Name, Proj_Id, Hours 
   FROM Employee, Charges 
   WHERE Employee.EmpNo = Charges.EmpNo
   UNION
   SELECT Name, Null (CHAR(8)), Null (DECIMAL(4,2)), 
   FROM Employee 
   WHERE EmpNo NOT IN 
   (SELECT EmpNo 
   FROM Charges
   )
   UNION
   SELECT Null (VARCHAR(12)), Proj_Id, Hours 
   FROM Charges 
   WHERE EmpNo NOT IN 
   (SELECT EmpNo 
   FROM Employee
   );

The first portion of the statement joins the Employee table with the Charges table on the EmpNo column. The second portion accounts for the employees who might be listed in the Employee table, but not the Charges table. The third portion of the statement accounts for the employees who might be listed in the Charges table and not in the Employee table. This ensures that all the information asked for is included in the response.