In MySQL, UNION is a keyword used to combine the result sets of two or more SELECT statements into a single result set. The UNION operation removes any duplicate rows present in the result set.
The syntax of the UNION statement is as follows:
SELECT column1, column2, ... FROM table1
UNION [DISTINCT | ALL]
SELECT column1, column2, ... FROM table2
[UNION [DISTINCT | ALL] SELECT column1, column2, ... FROM table3 ...]
Here, column1, column2, etc. are the column names to select from the tables, and table1, table2, etc. are the names of the tables to select from.
The UNION statement is used to combine the result sets of two or more SELECT statements. The DISTINCT keyword is optional and is used to remove duplicates from the result set. The ALL keyword is used to retain duplicates in the result set. If neither DISTINCT nor ALL is specified, DISTINCT is used by default.
Here’s an example of using UNION in MySQL:
Suppose we have two tables employees and customers. The employees table has columns id, name, and salary, and the customers table has columns id, name, and city. We can use the UNION statement to combine the names of employees and customers into a single list, like this:
SELECT name FROM employees
UNION
SELECT name FROM customers;
This will return a list of all the names of employees and customers, with duplicates removed.
UNION can also be used to combine results from more than two SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.
Difference between union and union all
UNION and UNION ALL are two SQL operators used to combine the results of two or more SELECT statements into a single result set.
The main difference between UNION and UNION ALL is that UNION only returns distinct rows, while UNION ALL returns all rows, including duplicates. Here are some more detailed explanations of each:
- UNION: The UNION operator combines the results of two SELECT statements into a single result set, eliminating duplicates. The columns in the SELECT statements must match in number and data type. If there are duplicates in the result sets, only one copy of each row is included in the final result set. The syntax for a UNION query is:
SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2;
- UNION ALL: The UNION ALL operator combines the results of two SELECT statements into a single result set, including duplicates. The columns in the SELECT statements must match in number and data type. If there are duplicates in the result sets, all copies of each row are included in the final result set. The syntax for a UNION ALL query is:
SELECT column1, column2, ... FROM table1
UNION ALL
SELECT column1, column2, ... FROM table2;
In general, UNION ALL is faster than UNION, because it does not need to perform a distinct operation. However, if you need to eliminate duplicates, then UNION is the appropriate operator to use.
About The Author
