SQL UNION

STC Admin

12 years ago

In previous article we have learned about SQL JOINS. In this section of Software Testing Class we are concentrating on “SQL UNION”. In this post we are covering what is SQL UNION, examples of UNION, why use SQL UNION & difference between SQL JOIN and UNION.

What is a SQL UNION?

This operator allows you to combine the result of two or more SQL SELECT queries.

The only requirement to work this operator query is both the SQL SELECT queries must having the same number of columns in the result set & with same data type.

SQL UNION Syntax:

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2;

If we use UNION operator in the query then all unique records are returned in the result set. The duplicate records from the result set are displayed once.

SQL UNION example:

Let take example, we have following two tables called Table1 and Table2:

First we will see how SQL UNION operator is combining the results of the two select statements:

SELECT Column1, Column2 FROM Table1

UNION

SELECT Column1, Column2 FROM Table2;

Following is the result upon executing the above SQL UNION query, the only unique records are returned from both tables.

Note: If we not specify any keyword with UNION operator then by default it take DISTINCT keyword i.e. “UNION DISTINCT”

Now we will see how combining the results of the two select statements:

SELECT Column1, Column2 FROM Table1

UNION ALL

SELECT Column1, Column2 FROM Table2;

Following is the result set upon executing the above SQL UNION ALL query, the result set includes all records including duplicate records from both tables, so the duplicate rows are repeated in the result set.

UNION operator is by default returns the unique records. If you want to get the all rows including duplicate records then you can use “ALL” keyword with UNION.

Difference between SQL JOIN and UNION:

In the JOINS, the columns may be different in the tables but in the UNION the number of column must be same.
In case of UNION result set put rows one after each other i.e. result set puts vertically. But in case of JOIN result set put column one after each other i.e. result set puts horizontally. UNION makes a Cartesian product.
UNION is used for joining 2 result sets and JOIN is used for joining two or more tables, views, table-valued functions.

Conclusion:

UNION operator is used to combine the result sets of two SELECT statements & combined result set includes all records from both tables.
The number of columns & column data types must be same for using the UNION operator.
The UNION DISTINCT keyword is returns the duplicate records from the result set. In the UNION operator if no any keyword is specified then it acts as DISTINCT keyword.
UNION ALL simply joins 2 result sets including all the rows,does not remove the duplicate records from the result set.

Over to you on SQL UNION’s:

Have you worked on Database testing? If yes, I would like all of you to please join this discussion and add more valuable points to it. If not please ask questions in comment below, also don’t forget to subscribe here to get software testing articles in your inbox. Click here to subscribe to your email address or enter your email id below.

⇓ Subscribe Us ⇓

If you are not regular reader of this website then highly recommends you to Sign up for our free email newsletter!! Sign up just providing your email address below:

Happy Testing!!!