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.
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.