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:
sqlSELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
Explanation:
- The
SELECT
statements before and after theUNION
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:
sqlSELECT 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.
0 Comments