Explicit join operations page 3

UNION JOIN

This join type have been introduced in SQL-92 language standard, but disappeared in later versions of SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL standard. Particularly, it is absent from SQL2003 (ANSI and ISO). As many other structures of SQL, UNION JOIN is excessive because it can be expressed as substraction of full outer join and inner join. Formally, we can write this expression as follows:

A UNION JOIN B :=
(A FULL JOIN B)
EXCEPT
(A INNER JOIN B)

If DBMS does not support FULL JOIN (MySQL), it can be obtained via union of left and right outer joins. So our formula takes the form

A UNION JOIN B :=
((A LEFT JOIN B)
UNION
(A RIGHT JOIN B))
EXCEPT
(A INNER JOIN B)

To demonstrate the case where this type of join could be useful, let's consider the following task.

Find out the makers which produce printers but not PCs or produce PCs but not printers.

Having the ability to use UNION JOIN, we could solve the task as follows:

SELECT * FROM
(SELECT DISTINCT maker FROM Product WHERE type='pc') m_pc
UNION JOIN
(SELECT DISTINCT maker FROM Product WHERE type='printer') m_printer
ON m_pc.maker = m_printer.maker;

Let's use the formula. Full join of PC makers with printer makers gives us those who produce only one type of product as well as those who produce both types - PCs and printers.

Console

Execute

SELECT * FROM
(SELECT DISTINCT maker FROM Product WHERE type='pc') m_pc
FULL JOIN
(SELECT DISTINCT maker FROM Product WHERE type='printer') m_printer
ON m_pc.maker = m_printer.maker;

Now we'll subtract from result obtained above those who produce the both types of products (inner join):

Console

Execute

SELECT m_pc.maker m1, m_printer.maker m2  FROM
(SELECT maker FROM Product WHERE type='pc') m_pc
FULL JOIN
(SELECT maker FROM Product WHERE type='printer') m_printer
ON m_pc.maker = m_printer.maker 
EXCEPT
SELECT * FROM
(SELECT maker FROM Product WHERE type='pc') m_pc
INNER JOIN
(SELECT maker FROM Product WHERE type='printer') m_printer
ON m_pc.maker = m_printer.maker;

In so doing I remove from the solution superfluous DISTINCT keywords because EXCEPT will exclude duplicate rows.
This is a single useful lesson here, as substraction operation (EXCEPT) can be replaced by a simple predicate:

WHERE m_pc.maker IS NULL OR m_printer.maker IS NULL

or even

m_pc.maker + m_printer.maker IS NULL

in view of the fact that concatenation with NULL gives NULL.

Console

Execute

SELECT * FROM
(SELECT DISTINCT maker FROM Product WHERE type='pc') m_pc
FULL JOIN
(SELECT DISTINCT maker FROM Product WHERE type='printer') m_printer
ON m_pc.maker = m_printer.maker   
WHERE m_pc.maker IS NULL OR m_printer.maker IS NULL;

At last, to deduce the result in one column, let's use COALESCE function:

Console

Execute

SELECT COALESCE(m_pc.maker, m_printer.maker) FROM
(SELECT DISTINCT maker FROM Product WHERE type='pc') m_pc
FULL JOIN
(SELECT DISTINCT maker FROM Product WHERE type='printer') m_printer
ON m_pc.maker = m_printer.maker   
WHERE m_pc.maker IS NULL OR m_printer.maker IS NULL;

It is needless to say that this is only one of possible methods to solve the task. The purpose of approach presented was to demonstrate a possible replacement of the deprecated join type.

I don't know any DBMS which support UNION JOIN.

Pages

inner join join operations FULL JOIN EXCEPT SQL-92 UNION JOIN

Previous [Using multiple tables in a query]

[Equi-joins] Next

SQL Problems and solutions

S. I. Moiseenko

SELECT statement

Using multiple tables in a query

Explicit join operations page 3

UNION JOIN