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

· MySQL · PostgreSQL · SQL

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.

SQL 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.

SQL UNION combine two or more SQL statements

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.


0 Comment

Was this helpful? Leave a comment!

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Posts

How to grant full privileges to a user in MySQL

Updated 16th August 2022

After setting up a lamp stack development environement with phpmyadmin locally on Linux you may notice that your phpmyadmin user (or other user name) doesn’t have the correct privileges to create new databases. To use the phpmyadmin user to create new databases and users for those databases you need to grant full privileges to the

How to transform geometries with PostGIS

Updated 2nd October 2023

ST_Transform in PostGIS

PostGIS is a power extention for PostgreSQL databases. It provides a suite of tools to handle and manipulate geographic data. At times, you may need to transform geometries with PostGIS for different base maps or to perform more accurate calculations, such as calculating the area of a polygon. Transform geometry with PostGIS PostGIS has a

How to setup a remote MySQL database connection in PHPStorm

Updated 1st October 2023

PHPStorm is packed full of features designed to make the development process for PHP developers efficient and streamlined. Although Jetbrains have a standalone product for interacting with databases, named DataGrip, PHPStrom ships with the Database Tools and SQL plugin. This tools is powerful, integrated directly within the IDE, and provides a convinient way to intract