SQL UNION Operator

In SQL the UNION Clause combines the results of two SQL queries into a single table of all matching rows. The two queries must have the same number of columns and compatible data types to unite. Any duplicate records are automatically removed unless UNION ALL is used.

Syntax: <Select statement1> UNION <Select statement2> <Order by clause>

Here the statement1 and statement2 are the compulsory statements. Both the queries execute independently but in the end the result will be the all the rows of statement1 and, statament2. Also the number of columns and their data type should be same.

Now consider the example given below:

SELECT ENAME, EMPNO FROM EMP WHERE ENAME LIKE ‘M%‘ OR ENAME LIKE ‘J%‘;
UNION
SELECT ENAME, EMPNO FROM EMP WHERE ENAME LIKE ‘J%‘ OR ENAME LIKE ‘A%‘;

On execution the first SELECT statement will return all employee names starting from ‘M’ or ‘J’ and second SELECT statement will return all the employee names starting with ‘J’ or ‘A’. So after Performing UNION operation all the rows from two select statements will be combined and duplicate rows are removed.