The UNION operator is used to combine the result-set of two or more SELECT statements.
SELECT UNION Syntax:SELECT column_name(s) FROM table1UNIONSELECT column_name(s) FROM table2;
The UNION operator selects only distinct values by default.
To allow duplicate rows, use UNION ALL:SELECT column_name(s) FROM table1UNION ALLSELECT column_name(s) FROM table2;
• Every SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in every SELECT statement must also be in the same order
EXAMPLE:
In this example, we want to display all participants from the database. The participants are stored in two different tables called "registration" and "metabase_registration_v0". We need to combine these two tables so that we get a single result.
If you want to combine three tables, you need to create three SELECT statements and join them with the UNION operator (SELECT 1 UNION SELECT 2 UNION SELECT 3).
In our example, we can see that we renamed our columns in the SELECT statements using MySQL aliases so that they have the same names. For more information about using aliases, see the following link.
Also, try to keep in mind the notes listed above: same number of columns, same data type, and same order in both selections.
Let's continue with this example and sort the results by age in ascending order. For this, we need to use ORDER BY clause. To find out how to use this clause in the SELECT statement, please read the next article.
