SQL Union is a powerful technique that enables you to combine the results of two or more SELECT statements into a single result set. This can be useful when you need to merge data from multiple tables, or when you need to consolidate data from different queries. In this tutorial, we will cover everything you need to know about SQL Union, including its syntax, types of Union, and practical examples.
Also you may want to learn join types include Inner Join, Left Join and Right Join, Full Outer Join each serving specific purposes for combining data effectively.
Syntax of SQL Union
The syntax of SQL Union is as follows:
SELECT column1, column2, ...
FROM table1
UNION [ALL]
SELECT column1, column2, ...
FROM table2;In this syntax, the UNION operator is used to combine the results of two or more SELECT statements. The SELECT statements must have the same number of columns, and the columns must have compatible data types. The ALL keyword is optional and is used to include all the rows in the combined result set, including duplicate rows.
Types of SQL Union
There are three types of SQL Union:
1. Union
The UNION operator is used to combine the results of two or more SELECT statements into a single result set. This operator eliminates duplicate rows from the result set.
2. Union All
The UNION ALL operator is used to combine the results of two or more SELECT statements into a single result set. This operator includes all the rows from each SELECT statement, including duplicate rows.
3. Intersect
The INTERSECT operator is used to return only the rows that are common to two SELECT statements. This operator eliminates duplicate rows from the result set.
Examples of SQL Union
Example 1: Combining Data from Multiple Tables
Suppose you have two tables: employees and customers, and you want to combine the data from both tables into a single result set. Here’s an example:
SELECT first_name, last_name, email
FROM employees
UNION
SELECT first_name, last_name, email
FROM customers;In this example, we used the UNION operator to combine the results of two SELECT statements. Both SELECT statements have the same number of columns and the columns have compatible data types.
Example 2: Consolidating Data from Different Queries
Suppose you want to consolidate data from different queries into a single result set. Here’s an example:
SELECT product_name, sales
FROM sales_data
WHERE year = 2022
UNION
SELECT product_name, sales
FROM sales_data
WHERE year = 2021;In this example, we used the UNION operator to combine the results of two SELECT statements that retrieve sales data for different years. The result set will include the product name and sales data for both years.
Example 3: Using Intersect
Suppose you have two tables: employees and managers, and you want to retrieve the names of employees who are also managers. Here’s an example:
SELECT employee_name
FROM employees
INTERSECT
SELECT manager_name
FROM managers;In this example, we used the INTERSECT operator to retrieve only the rows that are common to both SELECT statements. The result set will include the names of employees who are also managers.
Conclusion
SQL Union is a powerful technique that enables you to combine the results of two or more SELECT statements into a single result set. This technique is useful when you need to merge data from multiple tables, or when you need to consolidate data from different queries. In this tutorial, we covered the syntax of SQL Union, the types of Union, and practical examples. Additionally, exploring techniques like SQL Group By and SQL HAVING expands our data aggregation capabilities and allows us to handle a wider range of challenges.
