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

Table* (num)

Writing out a full UNION statement for every us of the outer product is cumbersome. Se here we have a qucik alternative.

To signal an outer product on a table, this site proposes to just put a Kleene star ("*") after the name, as this is the common method for signalling an item can be empty. A problem with that is that most RDBMS's require you to have an equal number of columns in all tables in a union. Actually this is not necessary when you implement Codd's outer union operator, but this is not part of the SQL standard. This need not be a problem as an RDBMS can easily look up the required number of columns for the table. However, this site cannot look that information up, so you have to add this information.

So we must add * (number_of_columns) to a table namefor a correct result. If you leave out the number of columns the generator will add just a single NULL to the SELECT.

The query example

We use the same use case as the previous query, but introduce the next table: the companies table. To get a result you need to specify e.g. "teen" instead of "xx".

Notice that the replacement rules makes sure that the outer product result has the same table name as the original table.

Conditions of use, Copyrights © & Intellectual Property Rights