Home » SQL » Union in SQL Query

Union in SQL Query

Overview: In this tutorial you will learn and understand how to use the Union in SQL query to join two or more tables using examples.

Introduction

UNION is a SQL operator that combines the results of two or more SELECT statements into a single result set. Unlike a JOIN operation, which combines rows from two tables based on a related column, UNION combines the results of SELECT statements, which can be from different tables or the same table.

The basic syntax of UNION in SQL is as follows:

SELECT [columns]
FROM [table1]
UNION
SELECT [columns]
FROM [table2];

Here, [table1] and [table2] are the names of the tables to be combined, and [columns] is the column or columns to be displayed in the results. The UNION operator is used to combine the results of the two SELECT statements.

The number of columns in each SELECT statement must be the same, and the data types of the columns must be compatible. UNION also eliminates duplicates by default. If duplicates are desired, you can use UNION ALL instead.

For example, to retrieve all the customers from two separate customer tables, you could use the following SQL statement:

SELECT name, address, city

FROM Customers1

UNION

SELECT name, address, city

FROM Customers2;

This statement combines the results of two SELECT statements, each of which retrieves customer information from different customer tables. The result of this statement will be a single result set containing all the customer information from both tables, with duplicates removed

Limitations of UNION in SQL:

There are a few limitations to be aware of when using the UNION in SQL query:

  • Column Compatibility: Each SELECT statement in the UNION must return the same number of columns, and the data types of the columns must be compatible. If the columns don’t match, you’ll receive an error.
  • Duplicate Rows: The UNION operator removes duplicates from the final result set, so each row in the result set will be unique. If you need to retain duplicates, you can use the UNION ALL operator instead.
  • Order of SELECT Statements: The order of the SELECT statements in the UNION can affect the final result set. For example, if the first SELECT statement returns more rows than the second, the final result set will include more rows from the first SELECT statement.
  • Performance: Depending on the size of the tables being combined and the complexity of the SELECT statements, the UNION operator can be a slow operation. If performance is a concern, it may be necessary to consider alternative methods of combining data, such as using a join.
  • Database Compatibility: Not all databases support the UNION operator. If you’re working with a database that doesn’t support UNION, you’ll need to use alternative methods of combining data, such as using a join or subquery.

Example #1: UNION in SQL for multiple tables of more then two tables

To use UNION with more than two tables, you can chain multiple UNION statements together, like this:

SELECT column1, column2, …
FROM table1

UNION

SELECT column1, column2, …
FROM table2

UNION

SELECT column1, column2, …
FROM table3

It’s important to note that each SELECT statement in the UNION must return the same number of columns, and the data types of the columns must be compatible.

Additionally, the UNION operator removes duplicates from the final result set, so each row in the result set will be unique.

Example #2: UNION in SQL using Group by Clause

You can use the GROUP BY clause in conjunction with the UNION in SQL query. The UNION operator combines the result sets of two or more SELECT statements into a single result set, and the GROUP BY clause groups the rows in the result set based on the values in one or more columns.

Below is the example code:

SELECT column1, SUM(column2)
FROM (
     SELECT column1, column2
     FROM table1
     UNION
     SELECT column1, column2
     FROM table2
) as combined_tables
GROUP BY column1


In this example, the two SELECT statements in the UNION combine their results into a single result set. The result set is then grouped by the values in column1, and the SUM function is used to aggregate the values in column2. The result is a single row for each unique value in column1, with the sum of the values in column2 for each group.

Example #3: Apply condition on UNION using WHERE clause

You can use the WHERE clause in conjunction with the UNION in SQL query. The WHERE clause is used to filter rows based on certain conditions, and the UNION operator combines the result sets of two or more SELECT statements into a single result set.

Below is the example code:

SELECT column1, column2

FROM table1

WHERE condition1

UNION

SELECT column1, column2

FROM table2

WHERE condition2


In this example, the WHERE clauses are used to filter the rows in each SELECT statement. The UNION operator then combines the resulting filtered result sets into a single result set.

It’s important to note that each SELECT statement in the UNION must return the same number of columns, and the data types of the columns must be compatible. Additionally, the UNION operator removes duplicates from the final result set, so each row in the result set will be unique.