>> UNION and UNION ALL in SQL

UNION  and  UNION ALL

UNION and UNION  ALL are used to combine the result of two or more SQL select statements. While combining the result , UNION returns the unique records from the select statements used in UNION , ie, removes duplication.

To use UNION and UNION ALL, the following conditions should be fulfilled:

• ·         The number of columns in all the queries must be same.
• ·         The data types of respective  columns in each select statements must  be same.
• ·         The columns in each select statements should be in the same order.
• ·         If any column is not available in any select statement, null should be used.

Though the data types and the order of columns must be same , the name of the columns can be same or different in two tables  and the column name in the result will be the column name of the first select statement unless other names/aliases are given.

UNION:

Let's take below two tables:

 NEW_EMPDATA

EMPNUM	NAME	POSITION	HIREDATE	SALARY	DEPTNUM
7499	ALLEN	HR	20-Feb-81	800	30
7521	WARD	AGENT	22-Feb-81	1250	30
7654	MARTIN	SALESMAN	28-Sep-81	1250	30
7844	TURNER	HOD	8-Sep-81	1500	30

OLD_DATA

ID	FIRST_NAME	CAPACITY	JOINDATE	MONTLY_SAL	ZONE
7566	JONES	MANAGER	2-Apr-81	2975	20
7788	SCOTT	ANALYST	9-Dec-82	3000	20
7902	FORD	DEALER	3-Dec-81	3000	20
7900	JAMES	CLERK	3-Dec-81	1300	30

1.) UNION combines two select statements :

Query:

SELECT * FROM NEW_EMPDATA

UNION

SELECT * FROM OLD_DATA ;

Output:

EMPNUM	NAME	POSITION	HIREDATE	SALARY	DEPTNUM
7499	ALLEN	HR	20-Feb-81	800	30
7521	WARD	AGENT	22-Feb-81	1250	30
7566	JONES	MANAGER	2-Apr-81	2975	20
7654	MARTIN	SALESMAN	28-Sep-81	1250	30
7788	SCOTT	ANALYST	9-Dec-82	3000	20
7844	TURNER	HOD	8-Sep-81	1500	30
7900	JAMES	CLERK	3-Dec-81	1300	30
7902	FORD	DEALER	3-Dec-81	3000	20

Query2(2nd Example):

SELECT EMPNUM, POSITION, SALARY FROM NEW_EMPDATA

WHERE SALARY > 1000

UNION

SELECT ID, CAPACITY, MONTHLY_SAL FROM OLD_DATA

WHERE  CAPACITY   IN ('ANALYST', 'DEALER')

ORDER BY 3;  Note: ORDER BY 3 means order by the third column in the query , ie, SALARY.

Output:

EMPNUM	POSITION	SALARY
7521	AGENT	1250
7654	SALESMAN	1250
7844	HOD	1500
7788	ANALYST	3000
7902	DEALER	3000

2.)  Using aliases as column name .

As we see in above examples, the output will display the first select statement headers(column names) as the headers in the output.

But if we want to give a different header name , we can do it using aliases as below:

Query:

SELECT  EMPNUM  AS  EMP_ID,  POSITION AS  WORK_POSTION ,  SALARY  AS  SAL_MONTHLY  FROM  NEW_EMPDATA

UNION

SELECT  ID,  CAPACITY,  MONTHLY_SAL  FROM OLD_DATA;

Output:

EMP_ID	WORK_POSITION	SAL_MONTHLY
7499	HR	800
7521	AGENT	1250
7566	MANAGER	2975
7654	SALESMAN	1250
7788	ANALYST	3000
7844	HOD	1500
7900	CLERK	1300
7902	DEALER	3000

3.)  Use "null" if any column value is not available:

If any column value is not available in any of the select statements , we can use "null" as used in below example because the number of columns on both SELECT statements should be same:

Suppose we have two tables:

NEW_EMPDATA

EMPNUM	NAME	POSITION	HIREDATE	SALARY	DEPTNUM
7499	ALLEN	HR	20-Feb-81	800	30
7521	WARD	AGENT	22-Feb-81	1250	30
7654	MARTIN	SALESMAN	28-Sep-81	1250	30
7844	TURNER	HOD	8-Sep-81	1500	30

OLD_DATA

ID	FIRST_NAME	CAPACITY	JOINDATE	ZONE
7566	JONES	MANAGER	2-Apr-81	20
7788	SCOTT	ANALYST	9-Dec-82	20
7902	FORD	DEALER	3-Dec-81	20
7900	JAMES	CLERK	3-Dec-81	30

Query:

SELECT EMPNUM, NAME, POSITION, HIREDATE, SALARY, DEPTNUM  FROM NEW_EMPDATA

UNION

SELECT ID, FIRST_NAME,CAPACITY, JOINDATE, NULL , ZONE  FROM OLD_DATA ;

Output:

EMPNUM	NAME	POSITION	HIREDATE	SALARY	DEPTNUM
7499	ALLEN	HR	20-Feb-81	800	30
7521	WARD	AGENT	22-Feb-81	1250	30
7566	JONES	MANAGER	2-Apr-81	(null)	20
7654	MARTIN	SALESMAN	28-Sep-81	1250	30
7788	SCOTT	ANALYST	9-Dec-82	(null)	20
7844	TURNER	HOD	8-Sep-81	1500	30
7900	JAMES	CLERK	3-Dec-81	(null)	30
7902	FORD	DEALER	3-Dec-81	(null)	20

4.) UNION removes duplicates present in the SELECT statements.

Let's have below tables:

NEW_EMPDATA:

EMPNUM	NAME	POSITION	HIREDATE	SALARY	DEPTNUM
7499	ALLEN	HR	20-Feb-81	800	30
7521	WARD	AGENT	22-Feb-81	1250	30
7654	MARTIN	SALESMAN	28-Sep-81	1250	30
7844	TURNER	HOD	8-Sep-81	1500	30

OLD_EMPDATA:

ID_NO	FIRST_NAME	JOB_ROLE	JOINDATE	SALARY	CODE
2342	Smith	null	11-Jan-98	200	null
3543	DAVID	MGR	12-Mar-98	300	10
7521	WARD	AGENT	22-Feb-81	1250	30
3543	DAVID	MGR	12-Mar-98	300	10

As we see in above tables, 2nd record of NEW_EMPDATA  and 3rd record of OLD_EMPDATA are same. Similarly, 2nd record of OLD_EMPDATA and 4th record of OLD_EMPDATA are also same.

When we run the UNION script, duplication will be removed, ie, only one record will be displayed for the records having same data in more than one records.

Let's see:

Query:

SELECT * FROM NEW_EMPDATA

UNION

SELECT * FROM OLD_EMPDATA;

Output:

EMPNUM	NAME	POSITION	HIREDATE	SALARY	DEPTNUM
2342	Smith	null	11-Jan-98	200	null
3543	DAVID	MGR	12-Mar-98	300	10
7499	ALLEN	HR	20-Feb-81	800	30
7521	WARD	AGENT	22-Feb-81	1250	30
7654	MARTIN	SALESMAN	28-Sep-81	1250	30
7844	TURNER	HOD	8-Sep-81	1500	30

5.) UNION can be used to combine more than two "SELECT" statements:

Let's take three tables as below:

NEW_EMPDATA:

EMPNUM	NAME	POSITION	HIREDATE	SALARY	DEPTNUM
7499	ALLEN	HR	20-Feb-81	800	30
7654	MARTIN	SALESMAN	28-Sep-81	1250	30
7844	TURNER	HOD	8-Sep-81	1500	30

OLD_EMPDATA:

ID_NO	FIRST_NAME	JOB_ROLE	JOINDATE	SALARY	CODE
2342	Smith	null	11-Jan-98	200	null
7521	WARD	AGENT	22-Feb-81	1250	30
3543	DAVID	MGR	12-Mar-98	300	10

OLD_EMPDATA1:

ID_NO	FIRST_NAME	JOB_ROLE	JOINDATE	SALARY	CODE
2000	JACK	null	null	500	10
1000	LUIS	null	null	null	20

Now, let's create query to UNION these three table data:

Query:

SELECT * FROM NEW_EMPDATA

UNION

SELECT * FROM OLD_EMPDATA

UNION

SELECT * FROM OLD_EMPDATA1

Output:

EMPNUM	NAME	POSITION	HIREDATE	SALARY	DEPTNUM
1000	LUIS	null	null	null	20
2000	JACK	null	null	500	10
2342	Smith	null	11-Jan-98	200	null
3543	DAVID	MGR	12-Mar-98	300	10
7499	ALLEN	HR	20-Feb-81	800	30
7521	WARD	AGENT	22-Feb-81	1250	30
7654	MARTIN	SALESMAN	28-Sep-81	1250	30
7844	TURNER	HOD	8-Sep-81	1500	30

Click here for UNION ALL


Go To Main SQL Page


.





.