loading..
Русский    English
19:15

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:

  1. A UNION JOIN B :=
  2. (A FULL JOIN B)
  3. EXCEPT
  4. (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

  1. A UNION JOIN B :=
  2. ((A LEFT JOIN B)
  3. UNION
  4. (A RIGHT JOIN B))
  5. EXCEPT
  6. (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:

  1. SELECT * FROM
  2. (SELECT DISTINCT maker FROM Product WHERE type='pc') m_pc
  3. UNION JOIN
  4. (SELECT DISTINCT maker FROM Product WHERE type='printer') m_printer
  5. 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
  1. SELECT * FROM
  2. (SELECT DISTINCT maker FROM Product WHERE type='pc') m_pc
  3. FULL JOIN
  4. (SELECT DISTINCT maker FROM Product WHERE type='printer') m_printer
  5. 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
  1. SELECT m_pc.maker m1, m_printer.maker m2  FROM
  2. (SELECT maker FROM Product WHERE type='pc') m_pc
  3. FULL JOIN
  4. (SELECT maker FROM Product WHERE type='printer') m_printer
  5. ON m_pc.maker = m_printer.maker
  6. EXCEPT
  7. SELECT * FROM
  8. (SELECT maker FROM Product WHERE type='pc') m_pc
  9. INNER JOIN
  10. (SELECT maker FROM Product WHERE type='printer') m_printer
  11. 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:

  1. WHERE m_pc.maker IS NULL OR m_printer.maker IS NULL
or even

  1. m_pc.maker + m_printer.maker IS NULL
in view of the fact that concatenation with NULL gives NULL.

Console
Execute
  1. SELECT * FROM
  2. (SELECT DISTINCT maker FROM Product WHERE type='pc') m_pc
  3. FULL JOIN
  4. (SELECT DISTINCT maker FROM Product WHERE type='printer') m_printer
  5. ON m_pc.maker = m_printer.maker   
  6. 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
  1. SELECT COALESCE(m_pc.maker, m_printer.maker) FROM
  2. (SELECT DISTINCT maker FROM Product WHERE type='pc') m_pc
  3. FULL JOIN
  4. (SELECT DISTINCT maker FROM Product WHERE type='printer') m_printer
  5. ON m_pc.maker = m_printer.maker   
  6. 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.

Next page

Bookmark and Share
Pages 1 2 3 4
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.