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.
SQL Union Syntax
The syntax of the SQL
UNION command is:
SELECT column1, column2, ... FROM table1 UNION SELECT column1, column2, ... FROM table2;
column1,column2, ...are the column names required for the union
table2are the names of the tables to fetch the columns from
UNIONcombines the columns in the tables
Note: If the selected columns from the tables contain the same data, those columns are only included once in the result set.
Example: SQL UNION
-- 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.
Things to Note While Using UNION
UNION in SQL, we must always remember,
- The column count in all tables must be the same. For example, both the Teachers and Students 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 the Students table.
- The columns must be in the same order in each table. For example, the order of columns is id-name-age in both Teachers and Students tables.
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
SQL UNION ALL Operator
UNION ALL operator selects fields from two or more tables similar to
UNION. However, unlike
UNION ALL doesn't ignore duplicate fields.
Let's try the previous SQL command again using
UNION ALL instead of
-- 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.
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
||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 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 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)|
To learn more, visit SQL JOIN.