Explicit join operations page 3
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:
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
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:
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.
Now we'll subtract from result obtained above those who produce the both types of products (inner join):
In so doing I remove from the solution superfluous DISTINCT keywords because EXCEPT will exclude duplicate rows.
At last, to deduce the result in one column, let's use COALESCE function:
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.