UNION in SQL

UNION in SQL

Overview: The UNION operator in SQL is used to combine the result sets of two or more SELECT statements into a single result set. It allows you to retrieve data from multiple tables or queries and present it as a single result set. The columns in the SELECT statements must have the same data types and appear in the same order.

Syntax:

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

Explanation:

• The SELECT statements before and after the UNION keyword represent the individual queries that you want to combine.
• The column names in the SELECT statements must match and be in the same order for the UNION operation to work correctly.
• The UNION operator removes duplicate rows from the result set. If you want to include duplicate rows, you can use the UNION ALL operator instead.

Example:

Consider two tables employees and managers with the following structure and data:

Table: employees

emp_id	emp_name	department
1	John	Sales
2	Mary	Marketing
3	David	Finance

Table: managers

mgr_id	mgr_name	department
1	Mike	HR
4	Sarah	Operations
5	Lisa	Sales

To combine the employee and manager data using UNION, you can use the following SQL query:

sql
SELECT emp_id, emp_name, department
FROM employees
UNION
SELECT mgr_id, mgr_name, department
FROM managers;

Result:

emp_id	emp_name	department
1	John	Sales
2	Mary	Marketing
3	David	Finance
1	Mike	HR
4	Sarah	Operations
5	Lisa	Sales

In the result set, you can see that the data from both tables has been combined, and duplicate rows have been removed.

Note: If you want to include duplicate rows, you can use the UNION ALL operator instead of UNION.