How UNION works in SQL? Best UNION examples

How UNION works in SQL? Best UNION examples

When working with SQL, sometimes we need to combine multiple result sets into one. That’s where the UNION operator comes in handy. If you’ve ever wondered how UNION works in SQL or wanted the best examples of its usage, you’ve come to the right place. Let’s dive into it.

What is UNION in SQL?

The UNION operator in SQL is used to combine the result sets of two or more SELECT queries into a single result set. The main advantage of using UNION is that it eliminates duplicate rows by default.

Basic Syntax of UNION in SQL

The syntax for using UNION in SQL is pretty straightforward:


SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

Both SELECT statements must have:

  • The same number of columns
  • Columns with compatible data types
  • Columns in the same order

Example: Using UNION to Combine Data

Let’s consider two simple tables: customers_usa and customers_europe.

id name country
1 Alice USA
2 Bob USA

customers_europe:

id name country
3 Charlie Germany
4 David France

Now, if we want a list of all customers from both tables, we can write:


SELECT id, name, country FROM customers_usa
UNION
SELECT id, name, country FROM customers_europe;

The result will be:

id name country
1 Alice USA
2 Bob USA
3 Charlie Germany
4 David France

How UNION Handles Duplicates

By default, UNION removes duplicate rows. If you want to keep duplicates, you can use UNION ALL.


SELECT id, name, country FROM customers_usa
UNION ALL
SELECT id, name, country FROM customers_europe;

This will return all rows including duplicates.

Performance Considerations

UNION has to remove duplicate records, which requires sorting the data. This can slow down performance, especially for large datasets. If you’re sure duplicates aren’t an issue, use UNION ALL to improve speed.

When to Use UNION

The UNION operator is useful in several scenarios:

  1. Combining data from multiple tables with the same structure
  2. Fetching records from different sources while ensuring uniqueness
  3. Generating reports from similar but separate datasets

Common Mistakes When Using UNION

  • Mismatched column count: Both queries in UNION must select the same number of columns.
  • Incompatible data types: The corresponding columns should have the same data type.
  • Sorting before applying UNION: Use ORDER BY at the end of the final query, not in individual SELECT statements.

Conclusion

The UNION operator in SQL is a powerful tool for merging results from multiple queries while automatically eliminating duplicates. If you’re looking for efficiency, consider using UNION ALL when deduplication isn’t necessary.

 

Other interesting article:

How SELF JOIN works in SQL? Best SELF JOIN examples