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.

1. SELECT statement included within UNION operator must have the same number of columns.
2. UNION combines the result set by column POSITION not by column name, so write down columns in each SELECT statement in the same order.
3. The columns must also have similar data types.

SYNTAX:

SELECT Column_Names(s) FROM xTable1x
UNION
SELECT Column_Names(s) FROM xTable2x;

We will consider following Employee and Manager table to demonstrate UNION operator.

Table#1 : Manager

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

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

SELECT E_Address FROM Employee UNION SELECT M_Address FROM Manager;

OUTPUT:

 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

SELECT E_Address FROM Employee
UNION ALL
SELECT M_Address FROM Manager;

OUTPUT:

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

SELECT E_Address FROM Employee
UNION ALL
SELECT M_Address FROM Manager ORDER BY E_Address;

OUTPUT:

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

SELECT E_Name FROM Employee WHERE E_Department='IT'
UNION 
SELECT M_Name FROM Manager WHERE M_Department='IT';

OUTPUT:

 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

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:

 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:

1. SQL For QA : Tutorial#1 – Select Query
2. SQL For QA : Tutorial#2 – Where Clause
3. SQL for QA: Tutorial#3 – INSERT INTO Statement
4. SQL for QA: Tutorial#4 – UPDATE Statement
5. SQL for QA: Tutorial#5 – DELETE Statement
6. SQL for QA: Tutorial#6 – ORDER BY Clause
7. SQL for QA: Tutorial#7 – GROUP BY Clause
8. SQL for QA: Tutorial#8 – UNION Clause
9. SQL for QA: Tutorial#9 – JOIN Clause

x