SQL UNION
UNION
The SQL UNION operator combines the results of two or more queries and makes a result set which includes fetched rows from the participating queries in the UNION.
Contents:
Basic rules for combining two or more queries using UNION
Basic rules for combining two or more queries using UNION :
1.) number of columns and order of columns of all queries must be same.
2.) the data types of the columns on involving table in each query must be same or compatible.
3.) Usually returned column names are taken from the first query.
By default the UNION behaves like UNION [DISTINCT] , i.e. eliminated the duplicate rows; however, using ALL keyword with UNION returns all rows, including duplicates.
Difference between SQL JOIN and UNION
1.) The columns of joining tables may be different in JOIN but in UNION the number of columns and order of columns of all queries must be same.
2.) The UNION puts rows from queries after each other( puts vertically ) but JOIN puts the column from queries after each other (puts horizontally), i.e. it makes a cartesian product.
Syntax:
SELECT <column_list>t [INTO ] [FROM ] [WHERE ] [GROUP BY ] [HAVING ] [UNION [ALL] SELECT <column_list> [FROM ] [WHERE ] [GROUP BY ] [HAVING ]...] [ORDER BY ]
The queries are all executed independently but their output is merged.
In the following example, no clause have been added with UNION, so, by default UNION is acting as UNION [DISTINCT] and only the unique rows are available in the result set.
Sample table: product
PROD_CODE PROD_NAME COM_NAME LIFE ---------- --------------- ---------- ---------- PR001 T.V. SONY 7 PR002 DVD PLAYER LG 9 PR003 IPOD PHILIPS 9 PR004 SOUND SYSTEM SONY 8 PR005 MOBILE NOKIA 6
Sample table: purchase
PUR_NO PROD_CODE PROD_NAME COM_NAME PUR_QTY PUR_AMOUNT
---------- ---------- --------------- ---------- ---------- ----------
2 PR001 T.V. SONY 15 450000
1 PR003 IPOD PHILIPS 20 60000
3 PR007 LAPTOP H.P. 6 240000
4 PR005 MOBILE NOKIA 100 300000
5 PR002 DVD PLAYER LG 10 30000
6 PR006 SOUND SYSTEM CREATIVE 8 40000
SQL Code:
-- Selecting prod_code and prod_name from the product table
SELECT prod_code, prod_name
-- Combining the results with the ones from the following SELECT statement using UNION
FROM product
-- Retrieving distinct values from the product table
UNION
-- Combining the results with the ones from the previous SELECT statement
SELECT prod_code, prod_name
-- Selecting prod_code and prod_name from the purchase table
FROM purchase;
Explanation:
- This SQL query retrieves prod_code and prod_name from two different tables, product and purchase.
- The UNION operator is used to combine the results of the two SELECT statements and remove duplicate rows.
- The first SELECT statement selects prod_code and prod_name from the product table.
- The second SELECT statement selects prod_code and prod_name from the purchase table.
- The query returns a result set containing all unique combinations of prod_code and prod_name from both tables.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
PROD_CODE PROD_NAME ---------- --------------- PR001 T.V. PR002 DVD PLAYER PR003 IPOD PR004 SOUND SYSTEM PR005 MOBILE PR006 SOUND SYSTEM PR007 LAPTOP
Pictorial Representation
SQL UNION ALL
In the following example, the optional clause ALL have been added with UNION for which, all the rows from each query have been available in the result set. Here in the above output, the marking rows are non-unique but it has been displayed. If ignored ALL clause, the marking rows would have come once.
SQL Code:
-- Selecting prod_code, prod_name, and com_name from the product table
SELECT prod_code, prod_name, com_name
-- Combining the results with the ones from the following SELECT statement using UNION ALL
FROM product
-- Retrieving all values including duplicates from the product table
UNION ALL
-- Combining the results with the ones from the previous SELECT statement
SELECT prod_code, prod_name, com_name
-- Selecting prod_code, prod_name, and com_name from the purchase table
FROM purchase;
Explanation:
- This SQL query retrieves prod_code, prod_name, and com_name from two different tables, product and purchase.
- The UNION ALL operator is used to combine the results of the two SELECT statements and retain all rows, including duplicates.
- The first SELECT statement selects prod_code, prod_name, and com_name from the product table.
- The second SELECT statement selects prod_code, prod_name, and com_name from the purchase table.
- The query returns a result set containing all rows from both tables, including any duplicates present in each individual table.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
SQL UNION ALL using where
In the following example, the two queries have been set using two different criteria including WHERE clause. So all the retrieve rows (including duplicates) have displayed in the result set. Here in this example, the marking rows are identical, but it has been displayed for the ALL clause along with UNION. If ignored ALL clause the marking rows would have come once.
SQL Code:
-- Selecting prod_code, prod_name, and com_name from the product table
SELECT prod_code, prod_name, com_name
-- Filtering rows based on the condition life greater than 6
FROM product
WHERE life > 6
-- Combining the results with the ones from the following SELECT statement using UNION ALL
UNION ALL
-- Combining the results with the ones from the previous SELECT statement
SELECT prod_code, prod_name, com_name
-- Selecting prod_code, prod_name, and com_name from the purchase table
FROM purchase
-- Filtering rows based on the condition pur_qty greater than 10
WHERE pur_qty > 10;
Explanation:
- This SQL query retrieves prod_code, prod_name, and com_name from two different tables, product and purchase.
- The first SELECT statement selects prod_code, prod_name, and com_name from the product table, filtering rows based on the condition life greater than 6.
- The second SELECT statement selects prod_code, prod_name, and com_name from the purchase table, filtering rows based on the condition pur_qty greater than 10.
- The UNION ALL operator combines the results of the two SELECT statements and retains all rows, including duplicates.
- The query returns a result set containing all rows from both tables that meet the respective filtering conditions.
Output:
SQL UNION a table to itself
In the following example, the two queries have been set using two different criteria for the same table. So all the retrieved rows ( including duplicates ) have displayed. Here in this example, the marking rows are identical, but it has been displayed for the ALL clause along with UNION.
SQL Code:
-- Selecting prod_code, prod_name, and com_name from the purchase table
SELECT prod_code, prod_name, com_name
-- Filtering rows based on the condition pur_qty greater than 6
FROM purchase
WHERE pur_qty > 6
-- Combining the results with the ones from the following SELECT statement using UNION ALL
UNION ALL
-- Combining the results with the ones from the previous SELECT statement
SELECT prod_code, prod_name, com_name
-- Selecting prod_code, prod_name, and com_name from the purchase table
FROM purchase
-- Filtering rows based on the condition pur_amount greater than 100000
WHERE pur_amount > 100000;
Explanation:
- This SQL query retrieves prod_code, prod_name, and com_name from the purchase table.
- The first SELECT statement selects prod_code, prod_name, and com_name from the purchase table, filtering rows based on the condition pur_qty greater than 6.
- The second SELECT statement selects prod_code, prod_name, and com_name from the purchase table, filtering rows based on the condition pur_amount greater than 100000.
- The UNION ALL operator combines the results of the two SELECT statements and retains all rows, including duplicates.
- The query returns a result set containing all rows from the purchase table that meet either of the specified filtering conditions.
SQL UNION with different column names
In the following example, the two queries have been set using two different criteria and different columns. The different columns in two statements are 'life' and 'pur_qty'. But as the data type are same for both the columns so, result has displayed. Usually returned column names are taken from the first query.
SQL Code:
-- Selecting prod_code, prod_name, and life from the product table
SELECT prod_code, prod_name, life
-- Filtering rows based on the condition life greater than 6
FROM product
WHERE life > 6
-- Combining the results with the ones from the following SELECT statement using UNION
UNION
-- Combining the results with the ones from the previous SELECT statement
SELECT prod_code, prod_name, pur_qty
-- Selecting prod_code, prod_name, and pur_qty from the purchase table
FROM purchase
-- Filtering rows based on the condition pur_qty less than 20
WHERE pur_qty < 20;
Explanation:
- This SQL query retrieves prod_code, prod_name, and life from the product table and prod_code, prod_name, and pur_qty from the purchase table.
- The first SELECT statement selects prod_code, prod_name, and life from the product table, filtering rows based on the condition life greater than 6.
- The second SELECT statement selects prod_code, prod_name, and pur_qty from the purchase table, filtering rows based on the condition pur_qty less than 20.
- The UNION operator combines the results of the two SELECT statements and removes duplicate rows.
- The query returns a result set containing all unique combinations of prod_code, prod_name, and either life or pur_qty from the product and purchase tables respectively, where the specified conditions are met.
Output:
PROD_CODE PROD_NAME LIFE ---------- --------------- ---------- PR001 T.V. 7 PR001 T.V. 15 PR002 DVD PLAYER 9 PR002 DVD PLAYER 10 PR003 IPOD 9 PR004 SOUND SYSTEM 8 PR006 SOUND SYSTEM 8 PR007 LAPTOP 6
SQL UNION with Inner Join
In the following example, the union made by two queries. The queries are two inner join statement. In the first query, the join takes place between two tables where the prod_code of both tables are same and in the 2nd query the join take place between two tables where the prod_name of both tables are same.
SQL Code:
-- Selecting prod_code, prod_name, pur_qty, and pur_amount from the product and purchase tables using INNER JOIN
SELECT product.prod_code, product.prod_name, purchase.pur_qty, purchase.pur_amount
-- Joining the product and purchase tables on prod_code
FROM product
INNER JOIN purchase
ON product.prod_code = purchase.prod_code
-- Combining the results with the ones from the following SELECT statement using UNION
UNION
-- Combining the results with the ones from the previous SELECT statement
SELECT product.prod_code, product.prod_name, purchase.pur_qty, purchase.pur_amount
-- Selecting prod_code, prod_name, pur_qty, and pur_amount from the product and purchase tables using INNER JOIN
FROM product
INNER JOIN purchase
-- Joining the product and purchase tables on prod_name
ON product.prod_name = purchase.prod_name;
Explanation:
- This SQL query retrieves prod_code, prod_name, pur_qty, and pur_amount from the product and purchase tables.
- The first SELECT statement uses INNER JOIN to join the product and purchase tables based on prod_code.
- The second SELECT statement also uses INNER JOIN to join the product and purchase tables based on prod_name.
- The UNION operator combines the results of the two SELECT statements and removes duplicate rows.
- The query returns a result set containing prod_code, prod_name, pur_qty, and pur_amount from the product and purchase tables, where the join conditions are met either based on prod_code or prod_name.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
PROD_CODE PROD_NAME PUR_QTY PUR_AMOUNT ---------- --------------- ---------- ---------- PR001 T.V. 15 450000 PR002 DVD PLAYER 10 30000 PR003 IPOD 20 60000 PR004 SOUND SYSTEM 8 40000 PR005 MOBILE 100 300000
SQL: Union vs Union All
The basic difference between UNION and UNION ALL is, UNION removes duplicate records but UNION ALL does not. Let apply these two commands on two tables table1 and table2.
Rows in table1:
FIELD1
---------
1
4
2
3
Rows in table2 :
FIELD1
----------
2
4
2
1
UNION Example (Removes all duplicate records):
SQL Code:
-- Selecting field1 from table1
SELECT field1
-- Combining the results with the ones from the following SELECT statement using UNION
FROM table1
-- Combining the results with the ones from the previous SELECT statement
UNION
-- Selecting field1 from table2
SELECT field1
FROM table2;
Explanation:
- This SQL query retrieves field1 from two different tables, table1 and table2.
- The first SELECT statement selects field1 from table1.
- The second SELECT statement selects field1 from table2.
- The UNION operator combines the results of the two SELECT statements and removes duplicate rows.
- The query returns a result set containing all unique values of field1 from both tables.
Output:
FIELD1
----------
1
2
3
4
UNION ALL Example:
SQL Code:
-- Selecting field1 from table1
SELECT field1
-- Combining the results with the ones from the following SELECT statement using UNION ALL
FROM table1
-- Retrieving all values including duplicates from the table1
UNION ALL
-- Combining the results with the ones from the previous SELECT statement
SELECT field1
-- Selecting field1 from table2
FROM table2;
Explanation:
- This SQL query retrieves field1 from two different tables, table1 and table2.
- The first SELECT statement selects field1 from table1.
- The second SELECT statement selects field1 from table2.
- The UNION ALL operator combines the results of the two SELECT statements and retains all rows, including duplicates.
- The query returns a result set containing all values of field1 from both tables, including duplicates, without removing any rows.
Output:
FIELD1
----------
1
4
2
3
2
4
2
1
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Nested subqueries
Next: Create View