MySQL SELECT queries are commands that pull data from your database tables according to conditions each record must meet.
Complex queries will often need to combine the results from two or more SELECT queries – this is what the UNION operator does.
SELECT column [, column2, column3...] FROM first_table UNION SELECT column [, column2, column3...] FROM second_table;
- Each SELECT statement must have the same number of columns in its results
- Data type in each of the columns must match
- The columns in the results from each statement must be in the same order
- SELECT statements can include WHERE, LIMIT, and other clauses and constraints
- The column names in the results will usually be the same as the column names in the first SELECT statement
- The results from a UNION will omit duplicate rows
The UNION ALL operator works the same way as the UNION operator, but it will include duplicate rows:
SELECT column [, column2, column3...] FROM first_table UNION ALL SELECT column [, column2, column3...] FROM second_table;
SELECT car_name AS name, colour FROM cars UNION SELECT fruit_name, colour FROM fruits;
The UNION operator has merged the results from both tables, using the column names defined in the first SELECT query.
The MySQL UNION operator makes combining the results from SELECT queries quick and painless and is a good tool for getting a quick combined result set from several tables with similar columns.