SQL Union – What is a Union and how to combine two or more SQL Select queries

The SQL UNION Operator is used to combine two or more SELECT statements together into one set of results.

Things to know about SQL Union Operator

To execute a SQL UNION statement successfully, you need to be away of the requirements of a UNION statement.

• Each SELECT statement within the UNION must have the same number of columns
• Columns in each SELECT statement must be in the same order
• Columns within the SELECT statements must have similar data types

Example using SQL UNION

In our example we have 2 tables, one table called Fruit and another called Vegetables, and we want to join the SELECT results of both tables.

To UNION two SELECT statements, we would do something like this:

SELECT name from fruit UNION SELECT name from vegetables;

// or

SELECT name, shelf_life, 'Fruit' as type from fruit UNION select name, shelf_life, 'Vegetables' as type from vegetables;

Using the example SQL statement, we would get a set of results which is a combination of the two tables.

Notice that in both SELECT statements we are selecting the same number of columns.

If we were to change the statement to select additional columns from the vegetables table, like the query below, we would exepct an error.

SELECT name from fruit UNION SELECT name, shelf_life from vegetables;

Exectuting this statement would give the following error: ERROR: each UNION query must have the same number of columns.

If we were to request a different data type in one of the SELECT statements, we would get the following error: ERROR: UNION types bigint and character varying cannot be matched.

MySQLPostgreSQLSQL