SQL UNION

UNION is an SQL operator which combines the result of two or more SELECT queries and provides the single set in the output.

Syntax of UNION in SQL:

The data type and the number of fields must be same for every SELECT statement connected with the UNION operator. The database system uses the UNION operator for removing the duplicate values from the combined result set.

Example of UNION operator in SQL

Let's create two different tables and insert records in both tables.

The following query creates the Old_Employee table with four fields:

The following query creates the New_Employee table with four fields:

The following INSERT query inserts the record of old employees into the Old_Employee table:

The following query shows the details of the Old_Employee table:

Table: Old_Employee

The following INSERT query inserts the record of new employees into the New_Employee table:

The following query shows the details of the New_Employee table:

Table: New_Employee

The following query shows all records of both tables in one table using the UNION operator:

Output:

Where Clause with the UNION operator

The WHERE clause can also be used with UNION operator to filter the records from one or both tables.

Syntax of UNION with WHERE clause

Example of UNION with WHERE Clause

The following query shows those records of employees from the above tables whose salary is greater than and equal to 29000:

Output:

Union ALL Operator in SQL

The SQL Union ALL Operator is same as the UNION operator, but the only difference is that UNION ALL operator also shows the common rows in the result.

Syntax of UNION ALL Set operator:

Example of UNION ALL

Let's create two different tables and insert records in both tables.

The following query creates the Passed_Students table with four fields:

The following query creates the New_Students table with four fields:

The following INSERT query inserts the record of passed students into the Passed_Students table:

The following query shows the details of the Passed_Students table:

Table: Passed_Students

The following INSERT query inserts the record of new students into the New_Students table:

The following query shows the details of the New_Students table:

Table: New_Students

The following query shows all duplicate and unique records from both tables:

Output: