Learn about SQL UNION with the help of examples. Learn how to combine the results of two or more SQL queries into a single result set with the SQL UNION operator.
In SQL, the UNION
operator selects fields from two or more tables.
-- select the union of name columns from two tables Teachers and Students
SELECT name
FROM Teachers
UNION
SELECT name
FROM Students;
Here, the SQL command selects the union of the name columns from two different tables: Teachers and Students.
The syntax of the SQL UNION
command is:
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
Here,
column1,column2, ...
are the column names required for the uniontable1
and table2
are the names of the tables to fetch the columns fromUNION
combines the columns in the tablesNote: If the selected columns from the tables contain the same data, those columns are only included once in the result set.
-- select the union of age columns from two tables Teachers and Students
SELECT age
FROM Teachers
UNION
SELECT age
FROM Students;
Here, the SQL command returns the age columns from the Teachers and the Students tables, ignoring the duplicate fields.
Example: SQL UNION
To use UNION
in SQL, we must always remember,
Note: Our online compiler is based on SQLite, which converts the values in one of the columns to match the data type of the other column while performing a UNION
operation.
The UNION ALL
operator selects fields from two or more tables similar to UNION
. However, unlike UNION
, UNION ALL
doesn't ignore duplicate fields.
Let's try the previous SQL command again using UNION ALL
instead of UNION
.
-- select the union of age from Teachers and Students tables
SELECT age
FROM Teachers
UNION ALL
SELECT age
FROM Students;
Here, the SQL command selects fields from both tables, including the duplicate fields.
Example: SQL 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. |
For improved performance, It is recommended you use UNION ALL
when you know the selected fields will only have unique values.
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 them in the output. |
Any number of columns can be present in the tables. | Column count must be the same in both of the tables. |
Data type of columns can be different. | Data type of columns should ideally be the same (except for some databases like SQLite) |
#sql