UNION Examples | SQL Statements | Teradata Vantage - 17.05 - Example: Selecting the Name, Project, and Employee Hours - 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 select the name, project, and the number of hours spent by employees assigned to project OE1-0001, plus the names of employees not assigned to a project, the following query could be used:

   SELECT Name, Proj_Id, Hours
   FROM Employee,Charges 
   WHERE Employee.Empno = Charges.Empno 
   AND Proj_Id IN ('OE1-0001')
   UNION
   SELECT Name, NULL (CHAR (8)), NULL (DECIMAL (4,2)) 
   FROM Employee 
   WHERE Empno NOT IN
   (SELECT Empno 
   FROM Charges);

This query returns the following rows.

Name Project Id Hours
Aguilar J ? ?
Brandle B ? ?
Chin M ?  
Clements D ? ?
Kemper R    
Marston A ? ?
Phan A ? ?
Regan R ? ?
Russell S ? ?
Smith T    
Watson L    
Inglis C 0E1-0001 30.0
Inglis C 0E1-001 30.5
Leidner P 0E1-001 10.5
Leidner P 0E1-001 23.0
Moffit H 0E1-001 12.0
Moffit H 0E1-001 35.5

In this example, null expressions are used in columns 2 and 3 of the second SELECT statement. The null expressions are used as place markers so that both SELECT statements in the query contain the same number of expressions.