Example: UNION Clause

The following query retrieves the supplier numbers for all suppliers having a status equal to 30 or more, and then appends to this result the numbers of suppliers who carry green parts.

SELECT sno
FROM supplier
WHERE status >= 30
  UNION
SELECT sno
FROM part p, partsupp ps
WHERE p.pno = ps.pno
  AND p.color = "GREEN"

This query yields the results shown below. Notice that duplicate rows have been omitted from the result since UNION ALL was not specified:

If an ORDER BY clause is used to sort the combined row results of a UNION query, the ORDER BY clause must specify the number of an output column on which to sort the combined output. The first query output column listed in the SELECT clause is column 1, the second column 2, etc.

Also notice in the following sample query how the string "Part" is padded with spaces to make it the same length as the "Supplier" string. Output columns combined with a UNION clause must be of the same data type and character width.

SELECT "Part    ", pno, "Made in England   "
FROM part
WHERE city = "LONDON"
  UNION
SELECT "Supplier", sno, "Located in England"
FROM supplier
WHERE city = "LONDON"
  ORDER BY 2 DESC

The result of this query is:

In the final sample query below, two corresponding columns of two hypothetical tables, PART_ON_HAND and PART_ON_ORDER are both defined as Total_Value by the AS clause.

SELECT 'Available', partnum,
  q_on_hn * price AS Total_Value
  FROM part_on_hand
UNION ALL
SELECT 'On Order', partnum,
  q_on_ord * price AS Total_Value
  FROM part_on_order
ORDER BY partno, Total_Value

The corresponding column of the union of the two tables is also named Total_Value, even though it is the result of derived columns.