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

The Full Outer Join With Else Logic

Else Logic could not be applied to the previous query as the FROM of the subqueries did not refer to all the tables in the main query. Here we have rewritten the query so that these are the same. The result is a query that becomes very simple once Else Logic is applied. The generated SQL is of course a lot less simple.

A good question is: "is this more complicated SQL result scalable?" In a current RDBMS this is probably not the case, so it may be worthwile to prune the output SQL if the result is to slow. However, this does not mean that a newer version of an RDBMS would not be able to optimize the execution of this query. After all: all these subqueries do is reuse the exact data set created by the earlier filters in the query.

The query example

We use the same example as before.

show all the people and all the companies - linked if applicable

You can change this query into a left or right outer join by removing one of the two sides of the standalone OR operator. Again we can see from the invalid foreign keys that this is a real outer join.

Conditions of use, Copyrights © & Intellectual Property Rights