SQL Union

A SQL Union is a method to merge data from multiple tables.

Union Example

Here is an example. Let's assume you have a company that both sells goods and services, and you have the following two tables:

Goods:

id	name	price	instock
1	Wood	$5.00	12
2	Metal Plate	$10.00	23
3	100 Screws	$2.00	38

Services:

id	name	price	hours	expert
1	Make Wooden Dresser	$200.00	16	John
2	Repaint Furniture	$80.00	3	Michael

If you want to create a list of all products (both services and goods) with price, you can of course do two queries:

SELECT * FROM goods;
SELECT * FROM products;

However, this does not allow you to sort based on price. The solution is to make union of these two queries. To do so, you must only select the rows present in both tables:

SELECT name, price, "good" AS type FROM goods 
UNION
SELECT name, price, "service" AS type FROM services;

Views

It is even possible to store this query using a view:

CREATE VIEW products AS
SELECT name, price, "good" AS type FROM goods 
UNION
SELECT name, price, "service" AS type FROM services;

The VIEW products now contains the table:

name	price	type
Wood	$5.00	good
Metal Plate	$10.00	good
100 Screws	$2.00	good
Make Wooden Dresser	$200.00	service
Repaint Furniture	$80.00	service

The real advantage is that you can now sort on price or name:

SELECT * FROM products ORDER BY price;