SQL Union, SQL Union All With Examples [Latest]

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

Supplier Table

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. 1. Union based on Country column.

Select state from Customer
Union
Select state from Supplier where Country = 'India';

Output:

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

1. 1. Union output order by state.

Select state from Customer
Union
Select state from Supplier order by state asc;

Output:

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. 1. SQL Union All based on Country column.

Select state from Customer
Union ALL
Select state from Supplier where Country = 'India';

Output:

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

1. 1. SQL Union All output order by state.

Select state from Customer
Union ALL
Select state from Supplier order by state asc;

Output:

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.