SQL UNION

In this tutorial, we'll learn to use the UNION operator in SQL with the help of examples.

In SQL, the UNION operator selects rows from two or more tables.

If rows of tables are the same, those rows are only included once in the result set. For example,

SELECT age
FROM Teachers
UNION
SELECT age
FROM Students;

Here, the SQL command returns the age column from the Teachers table and the Students table, ignoring the duplicate rows.

How to use UNION in SQL
Example: SQL UNION

Things to Note While Using UNION

To use UNION in SQL, we must always remember,

  • Column count in all tables must be the same. For example, Teachers and Students both tables have three columns.
  • The data types of columns must be the same. For example, the age column in Teachers is integer, so is the age in Students table.
  • The columns must be in the same order in each table. For example, the order of columns is id-name-age in Teachers, so in the Students table.

SQL UNION ALL Operator

The UNION ALL operator selects rows from two or more tables similar to UNION. However, unlike UNION, UNION ALL doesn't ignore duplicate rows.

Let's try the previous SQL command again using UNION ALL instead of UNION.

SELECT age
FROM Teachers
UNION ALL
SELECT age
FROM Students;

Here, the SQL command selects rows from both tables including duplicate rows.

How to use UNION ALL in SQL
Example: SQL UNION ALL

SQL UNION Vs UNION ALL

SQL UNION SQL UNION ALL
It only returns distinct rows from the result set of two queries. It returns the duplicate values from the result set of two queries.
Slower in comparison to the UNION ALL operator. Executes fast as there is no need to filter the result-sets by removing duplicate values.

It is recommended to use UNION ALL when we know the result set will have unique values as it improves the performance.


SQL UNION Vs SQL JOIN

SQL JOIN SQL UNION
It is used to combine data into new columns from different tables. It is used to combine data into new rows from the result of different queries.
It uses the common column in both of the tables to fetch the data. It selects data from two tables and combines the output.
Any number of columns can be present in tables. Column counts must be the same in both of the tables.
Data type of columns can be different. Data type of columns must be the same.

To learn more, visit SQL JOIN.

Did you find this article helpful?