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

Else Logic: Group Selection in SQL

Else Logic - Existential Logic Simplified - is a method for organizing, reusing and shortening code. Unlike e.g. procedural programming and object orientation Else Logic has a very specific and limited domain of application: subqueries that select data (mostly) from the same data sources as the main query that contains them.
Another aspect that sets Else Logic apart from most other code reuse methods is that Else Logic does not rely on naming the code to be reused, it relies on context instead.

A simple example

Subqueries repeating part of their containing query are a common type of subquery used for example for group based selections that show what other customers bought who bought what you bought. The code for of these group based selections is much longer using subqueries than in natural languages.

To demonstrate this we start in the Tutorial with this example:

"show the sales of departments selling apples"

The SQL version of this query is a bit longer:

SELECT Department, Product, Sales 
  FROM SalesOverview 
  WHERE Department IN 
    (SELECT Department 
       FROM SalesOverview 
       WHERE Product = 'Apples')

This query contains an obvious amount of repetition; it is this repetition that Else Logic removes. If the SELECT statement in the subquery was missing we could use the linked to columns in the main query as default columns and use them instead. This happens quite often when the FROM statement uses the same data source in the main query and the subquery. Speaking of which, the FROM is repeated verbatim. If we assume the data sources of the main query to be some kind of default data store we could leave the second FROM out as well.

So using Else Logic we create a new - shorter - syntax that allows us to leave all this out. By assuming a WHERE as a different kind of automatic default, we get the Else Logic version of the previous query:

SELECT Department, Product, Sales 
  FROM SalesOverview 
  WHERE Department IN (Product = 'Apples')

As this site demonstrates we can use this shorter query to generate a longer version of this query that is understood by current Relational Databases.

There is more to Else Logic than this example - otherwise I would not have bothered with it - but this example does show the core of Else Logic. The fact that current SQL can be generated from the shorter code shows that Else Logic is not some non-relational add-on to SQL, but a 100% relational query language that is just written slightly different.

A more complex example

As link columns and data sources are reused by Else Logic, so are filter statements that would otherwise be repeated. Take the example query:

"show the January sales of apples with the pear sales for departments not selling apples"

This natural language query translates to this SQL query:

SELECT Department, Product, Sales, Period
    FROM SalesByDate
    WHERE Period BETWEEN '2009-01-01' AND '2009-01-31'
      AND (
          Product = 'Apples'
            Product = 'Pears'
            Department NOT IN (SELECT Department
             FROM SalesByDate WHERE (Period BETWEEN '2009-01-01' AND '2009-01-31')
                             AND (Product = 'Apples')))

One reason why the natural language version of the query is shorter is because it implicitly reuses the January filter. Else Logic formalizes this approach with new versions of the existing logical operators that mark reuse of the filter code. This allows us to mention even the filter for apples only once. Consequently the previous SQL query can be generated using only this Else Logic query:

SELECT Department, Product, Sales, Period
    FROM SalesByDate
    WHERE Period BETWEEN '2009-01-01' AND '2009-01-31'
      AND THEN (
          Product = 'Apples'
        OR ELSE
            Product = 'Pears'
            Department NOT EXISTS())

Personally I find the Else Logic version simpler to read and comprehend. Not only is it shorter than the original query; but the extra syntax does show the relation between the different parts of the filter statement better than code repetition does.

Please do tell me if you agree after you have finished the Tutorial.

Compared to window functions

Else Logic does look a bit like the window functions introduced in SQL 2003. Window functions allow column expressions to calculate group values over a chosen partition:

SUM(Sales) OVER (PARTITION BY Department)

However window functions work only on the final result set and do not allow access to rows not in the final result set of the query. Neither is Else Logic set to replace window functions. One can imagine the previous column calculation in Else Logic using something like this:

(SELECT SUM(Sales) WHERE Department = ..Departement)

Here we use ..Department to point to the department column in the main query. However this would offer no advantage over windows functions and the current implementation of Else logic does not contain such a main query lookup operator. If you know a good example that could use this operator, do let me know. This would require a significant rewrite of the current translation engine, so it may take some time to implement, but as this is an experimental site that would not be a problem.

Compared to optimization

Else Logic does not compare to subquery optimization techniques like subquery unnesting as Else Logic is not a technique for speed optimization but one for coding optimization; two optimization classes generally at odds with each other. So while subquery rewriting can significantly speed query execution it does so - when done by the programmer - by obfuscating the original meaning of the code.

The other side of this coin is that Else Logic might make the job of the database optimizer somewhat simpler as it makes code reuse explicit. Furthermore Else Logic forces a certain structure on subqueries that might make them easy to optimize. Knowing little about query speed optimizations I would like to hear from anyone who does.

But there is more...

The funny thing is that none of these examples have anything to do with why I started developing Else Logic. These reasons I will explain on the XQuery Example Page.

Conditions of use, Copyrights © & Intellectual Property Rights