SQL UNION Operator: Combining Query Results

Introduction

In the world of databases, the SQL UNION operator plays a vital role in combining the results of multiple SELECT statements into a single result set. It allows you to merge rows from different tables or queries based on their column structure and data types. This article will provide a comprehensive guide to understanding the SQL UNION operator, along with relevant examples and code snippets.

1. What does UNION operator do in SQL?

The UNION operator in SQL allows you to combine the result sets of two or more SELECT statements into a single result set. It removes duplicate rows by default, providing a distinct set of records. The columns in the SELECT statements must have the same number of columns and compatible data types.

2. Syntax of the SQL UNION operator

The basic syntax for using the UNION operator in SQL is as follows:

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

3. Example 1: Combining Two SELECT Statements

Let’s consider an example where we have two tables, Customers and Suppliers. We want to retrieve a list of all the unique names from both tables. Here’s how you can use the UNION operator to accomplish this:

SELECT name
FROM Customers
UNION
SELECT name
FROM Suppliers;

This query will combine the names from the Customers table with the names from the Suppliers table and present a distinct list of names from both tables.

4. Example 2: Using UNION with ORDER BY Clause

The UNION operator can also be used with the ORDER BY clause to sort the combined result set. Let’s consider an example where we want to retrieve a sorted list of all the cities from the Customers and Suppliers tables:

SELECT city
FROM Customers
UNION
SELECT city
FROM Suppliers
ORDER BY city;

The result will contain a sorted list of cities from both tables in ascending order.

5. Example 3: UNION ALL – Including Duplicates

By default, the UNION operator removes duplicate rows from the result set. However, if you want to include duplicate rows, you can use the UNION ALL operator. Let’s take an example where we want to retrieve all the names from the Customers and Suppliers tables, including duplicates:

SELECT name
FROM Customers
UNION ALL
SELECT name
FROM Suppliers;

This query will combine the names from both tables, including any duplicate names.

6. Example 4: UNION with WHERE Clause

The UNION operator can also be used with the WHERE clause to filter the rows in the SELECT statements. Let’s say we want to retrieve the names of customers who reside in a specific city and also the names of suppliers who operate in the same city. Here’s how you can achieve this:

SELECT name
FROM Customers
WHERE city = 'New York'
UNION
SELECT name
FROM Suppliers
WHERE city = 'New

 York';

This query will combine the names of customers and suppliers from the city of New York.

7. Difference between UNION and JOIN in SQL

While both UNION and JOIN are used to combine data from different tables, they serve different purposes:

  • UNION: It combines rows from different tables or queries vertically, creating a single result set. The column structure and data types must be the same.
  • JOIN: It combines rows from different tables horizontally, based on a common column between them. It expands the result set by merging the rows based on the specified condition.

Conclusion

The SQL UNION operator is a powerful tool for merging the results of multiple SELECT statements into a single result set. It enables you to combine data from different tables or queries, remove duplicates, and apply sorting or filtering as needed. By understanding the syntax and examples provided in this article, you can leverage the UNION operator to streamline your database queries effectively.


FAQs

Q: What does UNION operator do in SQL?
A: The UNION operator in SQL combines the result sets of two or more SELECT statements into a single result set, removing duplicate rows by default.

Q: What is union in SQL with example?
A: The UNION operator in SQL is used to combine the results of two or more SELECT statements into a single result set. For example:

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

Q: How to combine 2 queries in SQL?
A: To combine 2 queries in SQL, you can use the UNION operator. Ensure that both queries have the same number of columns and compatible data types. Here’s an example:

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

Q: What is the difference between UNION and join in SQL?
A: The UNION operator combines rows vertically from different tables or queries, while the JOIN operation combines rows horizontally based on a common column. UNION removes duplicates, while JOIN expands the result set.

Q: Does UNION remove duplicates?
A: Yes, the UNION operator in SQL removes duplicate rows from the result set. If you want to include duplicates, you can use the UNION ALL operator instead.

Leave a Comment