SQL Union Operator Explained [Easy Examples]

SQL Union & Union All Operator

SQL Union Operator or clause is a one of the SQL Set operators which is used to merge or combine two or more SQL Select Query results into single result set.

There are a few rules that need to be followed to avoid errors, listed as follows:

• The number of columns in the SELECT statements must be the same.
• The order of the columns in the SELECT statements must be the same.
• The data types of the columns must be the same or of a compatible type.
• The ORDERBY clause can only be used on the final SELECT statement.

 

Difference between SQL Union and SQL Union All

You have two choices when using UNION, as follows:

• UNION: This removes duplicate rows without using DISTINCT in the SELECT statements.
• UNION ALL: This does not remove duplicate rows, and they will remain in the final result. This will perform faster than UNION because it doesn't have to remove duplicates.

Venn diagram for Union - Excludes duplicates

 

Venn diagram for Union All - Includes duplicates

 

Difference Between SQL Union & SQL Join

Joins and Unions both are used to combine data of two or more tables.  The difference lies in how the data is combined.

 

SQL Union Operator Rules for merging result sets of Queries

• The datatype of columns, number of expressions and the order of the columns must be the same in all queries.
• Subsequent SQL statement row sets must match the data type of the first query
• GROUP BY and HAVING clauses can be applied with SQL Select Statement query

 

SQL Union or Union All Operator Syntax

SELECT Column_name1, Column_name2, … 
FROM table_name
[WHERE condition(s)]
[GROUP BY Column(s]]
[HAVING condition(s)]
UNION [ALL]
SELECT Column_name1, Column_name2, … 
FROM table_name
[WHERE condition(s)];
[GROUP BY Column(s]]
[HAVING condition(s)]
ORDER BY Column_name1,Column_name2…

• Column_name1, Column_name2...:It is column name of table of which data will be retrieve.
• table_name: It is the table name from which data is going to fetch
• Union: Specifies that multiple result sets are to be combined and returned as a single result set.
• Union ALL: Incorporates all rows into the results, including duplicates.

 

SQL Union & Union All Operator Examples

Consider following two tables department and employee to make query examples with SQL Select statement

Employee Table (tblemp)

Department Table (tbldept)

 

Let’s take simple example of merging same column of two tables using union operator

SELECT city
FROM tbldept
UNION
SELECT city
FROM tblemp

In above query we make union of two tables department table and table employee with common city

OUTPUT:

SELECT city
FROM tbldept
UNION ALL
SELECT city
FROM tblemp

In prevision query we have used Union all operator to make concatenation of results of two select statement without removing duplicate records

OUTPUT:

 

SQL Union & Union All with where condition

To display the merge report of department name which are located to Surat as well as the employee who are living in Surat using Union Operator

SELECT Dept_id, Dept_name
FROM tbldept
WHERE (city = 'Surat')
UNION
SELECT Emp_id, Emp_name
FROM tblemp
WHERE (city = 'Surat')

• In exceeding query to fetch the resulting set of department table and employee table we have used SQL Union Operator with two SQL Select statement with where condition.
• Union operator will remove duplicate rows from result set

OUTPUT:

 

SQL Union Operator with SQL Aliases & Order By clause

SQL Aliases are used to give a table or a column a temporary name. An alias only exists for the duration of the query

SELECT 'Department' as Type,Dept_name, city
FROM tbldept
UNION
SELECT 'Employee', Emp_name,city
FROM tblemp

In above query we have used alias name type to filter department and employee city name after combined resulting set

OUTPUT:

 

SQL Union Operator with SQL Select into clause

SQL Select into clause create new dummy table, it will create one new table in same database with column with is mentioned with SQL Select statement

For example we store above first query result into one new table named as ‘newTable’

SELECT 'Department' as Type,Dept_name, city into newTable
FROM  tbldept
UNION
SELECT 'Employee', Emp_name,city
FROM  tblemp

• Result set of first query is store into table named newTable
• To see the resulting of it, we need to use SQL Select statement to retrieve records of newTable.

OUTPUT:

SELECT Type, Dept_name, city
FROM newTable

 

SQL Union Operator with Group By clause

SQL Union operator with Group by Clause is used to arrange resulting set of two queries into groups. Group by clause can be applied to each select query or it can be used outer join query also.

To count each cities department and employee use SQL Union operator with Group by

SELECT city, COUNT(*) AS Expr1
FROM (SELECT Dept_id, Dept_name, city
FROM  dbo.tbldept
UNION
SELECT Emp_id, Emp_name, city
FROM dbo.tblemp) AS derivedtbl_1
GROUP BY city

OUTPUT:

 

SQL Union All Operator with Group By clause

SQL Union All Operator add duplicate records of two or more combine result set of SQL Select statement. When we used Group By clause with union all each query has group by clause ang groups of both queries than combined with union all

(SELECT  count(*),city
FROM tbldept
GROUP BY city)
UNION all
(SELECT  count(*),city
FROM tblemp GROUP BY city)

• In above example, two queries are connected with Union All operator
• First query, will return count and name of city of by making group of each department city
• Likewise, second query will return counting of cities after make group same city name of each employee together
• Resulting sets of both queries will be merge together using Union All so name city name

OUTPUT:

 

SQL Union Operator with SQL Joins | SQL Join with Three Table

The SQL Union operator can be used with SQL JOINS to retrieve data from more than two different tables

SELECT patient.patient_id, patient.name, 'ID number' AS Doctor, patient.doctor_id AS Doctor
FROM patient LEFT OUTER JOIN
doctor ON patient.doctor_id = doctor.doctor_id
UNION
SELECT bill.bill_no, patient_1.name, 'Charges' AS Expr1, bill.doctor_charge
FROM bill RIGHT OUTER JOIN
patient AS patient_1 ON bill.patient_id = bill.patient_id

• In above queries we have applied union operator on three tables to merge data of three tables.
• In first query we have join two tables doctor and patient using left outer join to retrieve data of patient and doctor
• In second query we have join two tables bill and doctor to fetch bill no and doctor charges
• To merge this two result set we use SQL Union Operator

OUTPUT:

 

SQL Union All Operator with Joins | Union of more than two table with Joins

Like above example, SQL Union All Operator can be applied on resulting set of two queries to display the combine result of both queries output without removing same value, to retrieve result from more than two tables we can use SQL join to connect tables

SELECT patient.patient_id, patient.name, lab_no, amount
FROM patient LEFT OUTER JOIN
laboratory ON patient.patient_id = laboratory.patient_id
UNION All 
SELECT doctor.doctor_id, doctor.name, lab_no , amount
FROM doctor RIGHT OUTER JOIN
laboratory ON laboratory.doctor_id = doctor.doctor_id

• In previous query, we join three tables together by using Left Outer join and Right Outer join
• First query SQL Left outer join is applied on patient and laboratory table will return resulting set of patient id with name and laboratory number with amount on condition if patient id record of particular patient present in laboratory table
• Second query SQL Right Outer join is applied to join doctor and laboratory tables to get data of doctor whose patient is having laboratory report entry in laboratory table
• To merge both queries resulting set Union All Operator has applied

OUTPUT:

 

Summary

In this Article, we have seen the different ways to use the Union and Union All Operator to merge result set of two queries, in the starting of this Article use of SQL Union and Union All operator is covered with difference between both operators, also covered with particle example. In sub section SQL Union operator with SQL Aliases & Order By clause, SQL Union Operator with SQL Select into clause and SQL Union operator and Union All with group by example has explained.

 

References

Set Operators - UNION