Monday, 11 February 2013

SQL UNION and UNION ALL Operators Syntax and Examples

The SQL UNION and UNION ALL Operators
The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. UNION command selects only distinct values.

The UNION ALL also works in similar way as  UNION, but  it allows duplicate records.

The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SQL UNION Syntax :

SELECT Column_name(s) FROM Table_I
UNION
SELECT Column_name(s) FROM Table_II

Note: 

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

SQL UNION ALL Syntax :

SELECT column_name(s) FROM Table_I
UNION ALL
SELECT column_name(s) FROM Table_II

Note: 

The Column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.


Example :
Let us suppose we have two Employee Tables , called EMP_MAIN and EMP_SUB as follows :

EMP_MAIN :
Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1003
Ravi Kumar Rao
Analyst
1
10000
1004
Tamatam
Team Lead
3
25000
1005
Sai Kumar Teja
Analyst
2
15000
1006
William
Manager
3
50000
1007
Vikram Teja Reddy
Analyst
2
180000
1006
Smith
Team Lead
3
50000

EMP_SUB :
Emp_ID
E_Name
Job
Dept_ID
Salary
1002
Karun
Team Lead
2
35000
1004
Tamatam
Team Lead
3
25000
1006
William
Manager
3
50000
1008
Varun
Team Lead
1
33000
1006
Smith
Team Lead
3
50000

----------------------------------------------------------------------------------------------------
SQL UNION 
Now we want to list all the Unique Employee Names from above two tables EMP_MAIN , EMP_SUB using UNION Operator as follows :

SELECT*FROM EMP_MAIN
UNION 
SELECT*FROM EMP_SUB

Result :
Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1002
Karun
Team Lead
2
35000
1003
Ravi Kumar Rao
Analyst
1
10000
1004
Tamatam
Team Lead
3
25000
1005
Sai Kumar Teja
Analyst
2
15000
1006
Smith
Team Lead
3
50000
1006
William
Manager
3
50000
1007
Vikram Teja Reddy
Analyst
2
180000
1008
Varun
Team Lead
1
33000

Note: 
The UNION command selects only distinct values.
----------------------------------------------------------------------------------------------------
SQL UNION ALL:
Now we want to list all the Employee Names(Including duplicates) from above two tables EMP_MAIN , EMP_SUB using UNION ALL Operator as follows :

SELECT*FROM EMP_MAIN
UNION ALL
SELECT*FROM EMP_SUB


Result :
Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1003
Ravi Kumar Rao
Analyst
1
10000
1004
Tamatam
Team Lead
3
25000
1005
Sai Kumar Teja
Analyst
2
15000
1006
William
Manager
3
50000
1007
Vikram Teja Reddy
Analyst
2
180000
1006
Smith
Team Lead
3
50000
1002
Karun
Team Lead
2
35000
1004
Tamatam
Team Lead
3
25000
1006
William
Manager
3
50000
1008
Varun
Team Lead
1
33000
1006
Smith
Team Lead
3
50000


Thanks,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts