Table of Contents
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.
|Two tables are "unioned" together||Two tables are "joined" together|
|SQL Union operator merges the result-set of two or more SQL SELECT statements||SQL Join merged data from two or more tables based on a match condition|
|It combined data into new rows||It combines data into new columns|
|Datatypes of corresponding columns selected from each table should be same||Datatypes of corresponding columns selected from each table can be different|
|It returns distinct rows||It may not return distinct columns|
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)
Emp_id Emp_name street city Emp_contact Salary Dept_id
101 jone althan Surat 1111111 20000 10001
102 cartin udhna Surat 2222222 15000 20001
103 krish ajava Vadodara 3333333 30000 20001
104 dhiru ramnagar Vadodara 8888888 36000 30001
105 om althan Surat 7777777 22000 30001
106 adi vesu Navsari 2323232 35000 10001
107 annant shivnagar Navsari 5555555 34000 10002
108 yogi althan Surat 8989898 25000 10002
109 muskan vesu Vadodara 9999999 18000 10001
110 rudra kashi hazira 1212121 31000 20001
Department Table (tbldept)
Dept_id Dept_name Dept_location
10001 Account Surat
20001 Sales Hazira
30001 Finance Vadodara
10002 Marketing Surat
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
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
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
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
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.
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
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
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
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
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.