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.