SQL UNION And INTERSECT

When working with databases, we often need to combine results from multiple queries. SQL provides powerful operators like UNION and INTERSECT to handle such situations. These operators help merge or filter data sets, making your queries more efficient and meaningful. In this article, we’ll explain both operators with examples so you can understand how to use them in real-world applications.

What is SQL UNION?

The UNION operator is used to combine the results of two or more SELECT queries into a single result set.

• It removes duplicate rows by default.
• Each SELECT query inside the UNION must have the same number of columns, with similar data types.
• The column names in the final result are usually taken from the first query.

Syntax of UNION

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

Example of UNION

Suppose we have two tables:

Table: Students_2024

Table: Students_2025

Now, let’s combine both lists using UNION:

SELECT Name FROM Students_2024
UNION
SELECT Name FROM Students_2025;

Result:

Notice that Aman appears only once, even though he exists in both tables. That’s because UNION removes duplicates automatically.

UNION ALL

If you want to keep duplicates in the result, use UNION ALL.

SELECT Name FROM Students_2024
UNION ALL
SELECT Name FROM Students_2025;

Result with UNION ALL:

So, UNION ALL is faster but doesn’t remove duplicates.

What is SQL INTERSECT?

The INTERSECT operator returns only the rows that are common in the result sets of two or more SELECT queries.

• Like UNION, the queries must have the same number of columns and compatible data types.
• It automatically removes duplicates from the result.

Syntax of INTERSECT

SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;

Example of INTERSECT

Using the same student tables:

SELECT Name FROM Students_2024
INTERSECT
SELECT Name FROM Students_2025;

Result:

Only Aman is common in both tables, so the INTERSECT operator returns just that.

Key Differences Between UNION and INTERSECT

When to Use UNION and INTERSECT?

• Use UNION when you need to merge data from different sources or periods, like combining customer lists from different years.
• Use INTERSECT when you need to find common data, like customers who bought from your store in both 2024 and 2025.

Final Thoughts

Both UNION and INTERSECT are essential SQL operators that simplify working with multiple result sets.

• UNION helps you bring together all data.
• INTERSECT helps you find what’s common.

By understanding these operators, you can write cleaner and more powerful SQL queries.