93

How to use UNION in SQL

What is UNION in SQL?

The UNION is a 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 the 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.

How to use Union in SQL

If you want to use UNION operator in Structured Query Language, then you have to create two different tables and adds the multiple records in both tables.

The following query creates the Old_Worker table with four fields:

The following query creates the New_Worker table with four fields:

The following INSERT query inserts the record of old Workers into the Old_Worker table:

The following query shows the details of the Old_Worker table:

Table: Old_Worker

The following INSERT query inserts the record of new Workers into the New_Worker table:

The following query shows the details of the New_Worker table:

Table: New_Worker

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

Output:

Where Clause with the UNION operator

We can also use the WHERE clause with the UNION SQL operator which helps in selecting the specific record from one or mutliple tables.

Syntax of UNION with WHERE clause

Example of UNION with WHERE Clause

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

Union ALL Operator in SQL

The SQL Union ALL Operator is the 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 take the above two tables and perform the UNION ALL operator on both tables.

The following query shows all the unique and common records from both tables in one table using the UNION ALL operator:

---