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:

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

Table#1 : Manager

Table#2 :  Employee

Example 1:  UNION Clause

Suppose we need to display unique addresses from Employee and  Manager table

OUTPUT:

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

OUTPUT:

Example 3:  UNION ALLwith ORDER BY clause

Suppose we need to display all addresses from Employee and  Manager table in ascending order

OUTPUT:

Example 4:  UNION With WHERE clause

Suppose we need to display unique emplaoyee names  of  IT department from Employee and  Manager table

OUTPUT:

Example 5: UNION With Alias

Suppose we want to display unique employee names and department from Employee and  Manager table with different column names

OUTPUT:

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

 

Add a Comment

Your email address will not be published. Required fields are marked *