SQL for QA: Tutorial#8 – UNION Clause
QAs also use UNION clause frequently if need to do through testing of application functionalities with data.
By using the UNION operator, we can combine the results of 2 or more SELECT statements and at the same time it removes duplicate rows between the different SELECT statements.
Some points need to be taken care while using UNION operator.
- SELECT statement included within UNION operator must have the same number of columns.
- UNION combines the result set by column POSITION not by column name, so write down columns in each SELECT statement in the same order.
- The columns must also have similar data types.
SYNTAX:
|
1 2 3 |
SELECT Column_Names(s) FROM <Table1> UNION SELECT Column_Names(s) FROM <Table2>; |
We will consider following Employee and Manager table to demonstrate UNION operator.
Table#1 : Manager
|
1 2 3 4 5 6 7 |
M_ID | M_Name | M_Salary | M_Department | M_Address -----+---------+----------+--------------+----------- 201 | Michal | 95000 | HR | Pune 203 | Merry | 89000 | Testing | Goa 204 | Monica | 99000 | IT | Mumbai 205 | Penny | 79000 | Database | Bangalore 206 | Ryan | 69000 | Analysis | Bangalore |
Table#2 : Employee
|
1 2 3 4 5 6 7 8 9 10 |
E_ID | E_Name | E_Salary | E_Department | E_Address -----+------ -+-----------+--------------+----------- 101 | Sam | 30000 | IT | Mumbai 102 | Jenny | 15000 | Admin | Pune 103 | Jack | 45000 | HR | Pune 104 | Anny | 65000 | Testing | Pune 105 | Jerry | 65000 | Testing | Bangalore 106 | Mike | 75000 | IT | Bangalore 107 | John | 33000 | Database | Mumbai |
Example 1: UNION Clause
Suppose we need to display unique addresses from Employee and Manager table
|
1 |
SELECT E_Address FROM Employee UNION SELECT M_Address FROM Manager; |
OUTPUT:
|
1 2 3 4 5 6 |
E_Address ----------- Pune Goa Bangalore Mumbai |
In above query you can see in result set, column name is showing E_Address, so it is usually equal to the column names in the first SELECT statement in the UNION.
Example 2: UNION ALL Clause
Suppose we need to display all addresses from Employee and Manager table
|
1 2 3 |
SELECT E_Address FROM Employee UNION ALL SELECT M_Address FROM Manager; |
OUTPUT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
E_Address ----------- Mumbai Pune Pune Bangalore Bangalore Mumbai Pune Pune Goa Mumbai Bangalore Bangalore |
Example 3: UNION ALLwith ORDER BY clause
Suppose we need to display all addresses from Employee and Manager table in ascending order
|
1 2 3 |
SELECT E_Address FROM Employee UNION ALL SELECT M_Address FROM Manager ORDER BY E_Address; |
OUTPUT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
E_Address ----------- Bangalore Bangalore Bangalore Bangalore Goa Mumbai Mumbai Mumbai Pune Pune Pune Pune |
Example 4: UNION With WHERE clause
Suppose we need to display unique emplaoyee names of IT department from Employee and Manager table
|
1 2 3 |
SELECT E_Name FROM Employee WHERE E_Department='IT' UNION SELECT M_Name FROM Manager WHERE M_Department='IT'; |
OUTPUT:
|
1 2 3 4 5 |
E_Name -------- Monica Sam Mike |
Example 5: UNION With Alias
Suppose we want to display unique employee names and department from Employee and Manager table with different column names
|
1 2 3 |
SELECT E_Name AS Staff, E_Department AS Department FROM Employee WHERE E_Address='Pune' UNION SELECT M_Name, M_Department FROM Manager WHERE M_Address='Pune'; |
OUTPUT:
|
1 2 3 4 5 6 |
Staff | Department --------+------------ Anny | Testing Jenny | Admin Jack | HR Michal | HR |
In the next and last article of this series we will cover JOINS.
Please let us know if you have any queries / questions for us.
Tutorial Index:
- SQL For QA : Tutorial#1 – Select Query
- SQL For QA : Tutorial#2 – Where Clause
- SQL for QA: Tutorial#3 – INSERT INTO Statement
- SQL for QA: Tutorial#4 – UPDATE Statement
- SQL for QA: Tutorial#5 – DELETE Statement
- SQL for QA: Tutorial#6 – ORDER BY Clause
- SQL for QA: Tutorial#7 – GROUP BY Clause
- SQL for QA: Tutorial#8 – UNION Clause
- SQL for QA: Tutorial#9 – JOIN Clause
