SQL UNION

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

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

Here,

  • column1,column2, ... are the column names required for the union
  • table1 and table2 are the names of the tables to fetch the columns from
  • UNION combines 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.

How to use UNION in SQL
Example: SQL UNION

Things to Note While Using UNION

To use 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 type of columns must be the same. For example, the age column in both the Teachers and Students table is integer.
  • 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 UNION operation.

Example: SQL UNION With WHERE Clause

-- select the union of age columns from both Teachers and Students tables where age >= 20
SELECT age, name FROM Teachers
WHERE age >= 20
UNION
SELECT age, name FROM Students
WHERE age >= 20;

Here, the SQL command selects the age column (only the unique values) from both tables where the age is greater than or equal to 20.


SQL UNION ALL Operator

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.

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

Example: SQL UNION ALL With WHERE Clause

-- select the union of age columns from both Teachers and Students tables where age >= 20
SELECT age, name FROM Teachers
WHERE age >= 20
UNION ALL
SELECT age, name FROM Students
WHERE age >= 20;

Here, the SQL command selects the age column from both tables (including duplicate values) where the age is greater than or equal to 20.


More on SQL UNION

SQL UNION vs. UNION ALL
SQL UNION SQL UNION ALL
It only returns unique values 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 faster as there is no need of filtering the result sets for removing duplicate 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.


Also Read:

Did you find this article helpful?

Our premium learning platform, created with over a decade of experience and thousands of feedbacks.

Learn and improve your coding skills like never before.

Try Programiz PRO
  • Interactive Courses
  • Certificates
  • AI Help
  • 2000+ Challenges