Union Operator in SQL

SQL UNION Operator.

The UNION operator is used to combine the result sets of two or more SELECT statements. For UNION, every SELECT statement must have the same number of columns with the same data type and must be in the same order. It will return the distinct values by removing the duplicate rows.

Syntax:

SELECT columnList  FROM table1 UNION SELECT columnList FROM table2;

Example:

SELECT EMP_NAME, SALARY FROM EMPLOYEE 
UNION 
SELECT EMP_NAME, AGE FROM EMPLOYEE1;

Output:

EMP_NAME	SALARY
Nidhi	        28
Nidhi	        48000
Parbhjot	28
Parbhjot	35000
Parbhjot	46000
Parmender	45000

SQL UNION ALL Operator.

It works the same as UNION operator only difference is that it returns the duplicate rows.

Syntax:

SELECT columnList  FROM table1 UNION ALL SELECT columnList FROM table2;

Example:

SELECT EMP_NAME, SALARY FROM EMPLOYEE 
UNION ALL 
SELECT EMP_NAME, AGE FROM EMPLOYEE1;

Output:

EMP_NAME	SALARY
Parbhjot	35000
Parmender	45000
Nidhi	        48000
Parbhjot	46000
Parmender	45000
Parbhjot	28
Nidhi	        28