SQL UNION Operator

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 same number of columns with same data type and must be in 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 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

No comments: