SQL Union, SQL Union All

Filed Under: SQL

In the real world, there are cases when we would like to merge the result set from two different SELECT queries. For such cases, SQL has provided a feature called Union. The literal meaning of Union is to combine. SQL Union is also very similar to its literal meaning.

SQL Union

SQL Union can be used to combine the result set of multiple queries. But, in order to use Union, there are a certain set of rules.

Rules for Usage of SQL Union

  • The queries should have the same number of columns as part of the select query.
  • The data type for the result sets should be the same.
  • Union uses column position for combination and not column name.
  • The column in each select query must be in the same order.

Syntax for SQL Union


SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;

SQL Union Example

Let us consider the following two table for SQL Union.

Customer Table

Customer Id Customer Name State Country
1 John California United States
2 Henry Texas United States
3 Amit Karnataka India

Supplier Table

Supplier Id Supplier Name State Country
1 Apple California United States
2 Texas Instruments Texas United States
3 HCL Karnataka India

Here is the SQL query to create these tables and insert test data.


CREATE TABLE `Customer` (
  `customer_id` int(11) unsigned NOT NULL,
  `customer_name` varchar(20) NOT NULL DEFAULT '',
  `state` varchar(20) NOT NULL DEFAULT '',
  `country` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `Supplier` (
  `supplier_id` int(11) unsigned NOT NULL,
  `supplier_name` varchar(20) NOT NULL DEFAULT '',
  `state` varchar(20) NOT NULL DEFAULT '',
  `country` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `Customer` (`customer_id`, `customer_name`, `state`, `country`)
VALUES
	(1, 'John', 'California', 'United States'),
	(2, 'Henry', 'Texas', 'United States'),
	(3, 'Amit', 'Karnataka', 'India');

INSERT INTO `Supplier` (`supplier_id`, `supplier_name`, `state`, `country`)
VALUES
	(1, 'Apple', 'California', 'United States'),
	(2, 'Texas Instruments', 'Texas', 'United States'),
	(3, 'HCL', 'Karnataka', 'India');

Let’s look into some examples of SQL Union queries with these tables.

  1. Union based on Country column.
  2. 
    Select state from Customer
    Union
    Select state from Supplier where Country = 'India';
    

    Output:

    State
    California
    Texas
    Karnataka

    SQL Union Example

    In the above result-set, the states are combined from both the select queries. Also, duplicate results are eliminated.

  3. Union output order by state.
  4. 
    Select state from Customer
    Union
    Select state from Supplier order by state asc;
    

    Output:

    State
    California
    Karnataka
    Texas

SQL Union example with order by clause

The output above is sorted post union based on the state in ascending order.

SQL Union All


SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2;

The major difference between SQL Union ALL and SQL Union is that SQL Union ALL allows duplicate values as part of the combination.

SQL Union All Example

We will reuse the Customer and Supplier tables defined earlier.

  1. SQL Union All based on Country column.
  2. 
    Select state from Customer
    Union ALL
    Select state from Supplier where Country = 'India';
    

    Output:

    sql union all example

    In the above result-set, the states are combined from both the select queries. Also, duplicate results are not eliminated.

  3. SQL Union All output order by state.
  4. 
    Select state from Customer
    Union ALL
    Select state from Supplier order by state asc;
    

    Output:
    sql union all order by example

    The output above is sorted post union based on the state in ascending order.

That’s all for SQL Union and Union All clause examples.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages