SQL Union

From Exterior Memory
Jump to: navigation, search

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;