First of all, this item is like a curiosity to me.
I was writing some code for a utility library and was adding methods to run different kinds of SQL join statements. All good, but at some point I came across the obscure UNION JOIN clause included in the SQL-92 spec (page 179).
I don't know how useful it can be (I never used it) and it's implemented (AFAIK) in HyperSQL only.
For the record, here's how it works. If we have two tables T and U with any numbers of colums/rows:
====== T ===== === U ===
a b c d e
---- ---- ---- ---- ----
1 2 3 10 11
4 5 6 12 13
Then:
select * from T union join U
Produces (in no particular row order):
a b c d e
---- ---- ---- ---- ----
1 2 3 null null
4 5 6 null null
null null null 10 11
null null null 12 13
Anyway, I wanted to know which databases do actually support it, or if you have seen it in the wild.
I was thinking about also asking what it can be good for, but I don't want this question to be closed as "Primarily Opinion Based".
In theory, ANY SQL supports it
As documented in "SQL Problems and Solutions" by Moiseenko:
This join type have been introduced in SQL-92 language standard, but disappeared in later versions of 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)
In practice, SAS supports it, at least version 9.3. Ref: http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#p0o4a5ac71mcchn1kc1zhxdnm139.htm