Group selection made easy, Outer joins made powerful
Site owned & produced by MagnaFacta

column IS column

Working with outer product means working a lot with NULL values. A null-safe equality operator comes in handy with this. Of course MySQL already defines null-safe equality with the <=> operator; but I found this ugly and have implementended my own, re-using the SQL IS operator as a null-safe equality operator. This is minimally invasive and seems more in the spirit of SQL to me.

A short message for anyone who thinks introducing a null-safe equality operator implies that I do not understand NULL values or relational theory. This is an SQL generator. It translates the use of this operator into normal SQL. It should be obvious therefore that there is nothing non-relational about this operator, but that it is just is shorthand for a 100% relational expression.

The query example

As an example combine people with the outer product of companies to find the companies with the people. When the PCid fields is empty it should be matched against the empty row added by the outer product:

show the people and their company information if applicable

Wait a minute! Isn't this a left outer join? Don't we need subqueries for that? Well... not for outer joins on foreign keys from child to parent. However, the foreign key constraint really has to hold, when a PCid field contains a value that does not occur in the companies table it will not show up in the result. As such error rows were inserted on purpose some people are missing (XXXI and XXXIII to be precise).

Conditions of use, Copyrights © & Intellectual Property Rights