In the real world, there are cases when we would like to merge the result set from two different SELECT queries. For such cases, SQL has provided a feature called Union. The literal meaning of Union is to combine. SQL Union is also very similar to its literal meaning.

SQL Union

SQL Union can be used to combine the result set of multiple queries. But, in order to use Union, there are a certain set of rules.

Rules for Usage of SQL Union

  • The queries should have the same number of columns as part of the select query.
  • The data type for the result sets should be the same.
  • Union uses column position for combination and not column name.
  • The column in each select query must be in the same order.

Syntax for SQL Union

SQL Union Example

Let us consider the following two table for SQL Union.

Customer Table

Customer Id Customer Name State Country
1 John California United States
2 Henry Texas United States
3 Amit Karnataka India

Supplier Table

Supplier Id Supplier Name State Country
1 Apple California United States
2 Texas Instruments Texas United States
3 HCL Karnataka India

Here is the SQL query to create these tables and insert test data.

Let’s look into some examples of SQL Union queries with these tables.

    1. Union based on Country column.

Output:

State
California
Texas
Karnataka

In the above result-set, the states are combined from both the select queries. Also, duplicate results are eliminated.

    1. Union output order by state.

Output:

State
California
Karnataka
Texas

The output above is sorted post union based on the state in ascending order.

SQL Union All

The major difference between SQL Union ALL and SQL Union is that SQL Union ALL allows duplicate values as part of the combination.

SQL Union All Example

We will reuse the Customer and Supplier tables defined earlier.

    1. SQL Union All based on Country column.

Output:

sql-union-all-example

In the above result-set, the states are combined from both the select queries. Also, duplicate results are not eliminated.

    1. SQL Union All output order by state.

Output:

The output above is sorted post union based on the state in ascending order.

That’s all for SQL Union and Union All clause examples.

By admin

Leave a Reply