SQL UNION Operator
SQL UNION Operator
The SQL UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. The SELECT statements must have the same number of columns, with compatible data types in corresponding columns.
The syntax for the UNION operator is as follows:
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
The result set of the UNION operator contains all the rows that are returned by the two SELECT statements, with duplicates removed. If you want to include duplicates, you can use the UNION ALL operator instead.
Here’s an example of using the UNION operator:
SELECT first_name, last_name
FROM employees
WHERE department = 'Sales'
UNION
SELECT first_name, last_name
FROM contractors
WHERE department = 'Sales';
In this example, we’re combining the result sets of two SELECT statements that select the first name and last name of employees and contractors who work in the Sales department. The result set will contain all the unique names from both tables, with duplicates removed.
here are some examples of using the SQL UNION operator with different scenarios:
- Combining data from two tables:
Let’s say we have two tables with similar columns, and we want to combine their data into a single result set. Here’s an example:
SELECT name, age, gender
FROM employees
UNION
SELECT name, age, gender
FROM contractors;
This will return a result set that contains all the unique rows from both the employees and contractors tables.
- Combining data from two tables with different column names:
If the two tables have columns with different names, we can use aliases to ensure that the columns match when we use the UNION operator. Here’s an example:
SELECT name, age, gender
FROM employees
UNION
SELECT employee_name, employee_age, employee_gender
FROM contractors;
In this example, we’re using aliases to rename the columns in the second SELECT statement so that they match the columns in the first SELECT statement.
- Combining data from three or more tables:
We can use the UNION operator to combine data from more than two tables by using additional SELECT statements. Here’s an example:
SELECT name, age, gender
FROM employees
UNION
SELECT name, age, gender
FROM contractors
UNION
SELECT name, age, gender
FROM freelancers;
In this example, we’re combining data from three tables – employees, contractors, and freelancers – into a single result set.
- Combining data and keeping duplicates:
By default, the UNION operator removes duplicate rows from the result set. If we want to keep duplicates, we can use the UNION ALL operator instead. Here’s an example:
SELECT name, age, gender
FROM employees
UNION ALL
SELECT name, age, gender
FROM contractors;
In this example, we’re combining data from the employees and contractors tables and keeping any duplicates that appear in both tables.