This operator is used to combine two similar queries results into one single result. Here both the queries should have same number of columns and all the respective columns should be of same datatypes. The order of columns in both the queries should also be same. This operator eliminates the duplicate records and displays single record. The resultant records are sorted in ascending order by default.
Suppose we have to see the employees in EMP_TEST and EMP_DESIGN tables. We can see in the below diagram that Kathy is present in both the tables. But UNION operator considered the duplicate values only once in the resulting table.
SELECT EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_SSN FROM EMP_TEST UNION SELECT EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_SSN FROM EMP_DESIGN;
If we need to retain the duplicate values in the result set, then we have to use UNION ALL operator. Below query will display Kathy’s record two times.
SELECT EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_SSN FROM EMP_TEST UNION ALL SELECT EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_SSN FROM EMP_DESIGN;