Relational Query Language
Alternative to SQL
SQL is the COBOL of relational languages
What if relational expressions were defined as a sequence of transformations on record sets (virtual or actual)?
The idea was inspired by three different sources. The
first is from this link:
It describes an experimental database system
called "Business System 12" (BS12) which was
a competitor to SQL around the time SQL was
being designed. Here is an
excerpt from it:
[DEFINE] T1 = JOIN(EMP, DEPT)
T2 = SUMMARY(T1, GROUP(DEPTNUM), EMPS=COUNT,
T3 = SELECT(T2, SALSUM > BUDGET)
My second influence came from functional
programming concepts, and the third from my
The format I am thinking about would be something like:
Tnew = operation(T1...Tn, expression1...expressionN)
Where Tx is a table or record-set reference. These references may be temporary, virtual, or actual. Expressions would reference (have scope on) only a maximum of two tables per operation. It is sort of like a math expression where the base operations have only 2 operands. It also allows more reuse because it is reference-based instead of nested-based, like SQL.
More on this below.
Limiting expression scope to two tables at a time simplifies things. If you want to filter based on a joined result, then you join it in a prior statement so that you are usually dealing with a "flat" view at any given operation, or at least no more than 2 "table sources" at a time.
Note that a field-list is considered a (virtual) table with one column. Thus, I did not include a "field list" parameter type. For example, if one of the parameters was "[name, start_date, serial]", then it would be internally converted into a table like:
Anyhow, typical queries may resemble:
T1 = filter(fooTable, x > y)
T2 = lookup(T1, barTable, [[amt][charge]], 1.serial = 2.ssn)
T3 = groupSum(T2, [empNo], [Amt])
You don't have to name the temporary record aliases "Tx"; it is just a convention here to make it clear what is external tables and what is internal tables, kind of like SQL table aliases. Perhaps a special symbol should be used for temporary/virtual tables, such as an ampersand or dollar sign:
$T2 = operation($T1, permTable)
The advantage over SQL is that first one can think more sequentially, and in smaller chunks at a time. Second it can be more easily debugged sequentially. For example, you could do:
T1 = filter(fooTable, "x > y")
T2 = lookup(T1, barTable, [[amt][charge]], 1.serial = 2.ssn)
usr:TempTable4 = copy(T2)
T3 = groupSum(T2, [empNo] [Amt])
"Copy" would copy the work table T2 to a specified database saving area.
Note that other programmers have complained about the non-sequential nature of SQL besides me. It is not just the thought process, but also that there is insufficient separation of scope in SQL. A column name reference has to be unambiguous, and if you have many Selects in SQL, it gets harder and harder to keep the name-space clean without lots of explicit table or alias references. Things get too "global" in SQL such that you can't really test in pieces because they are all "integrated".
It needs to be broken into more discrete chunks, or at least smaller chunks to better mentally manage and debug as isolated units.
Let's take a look at slightly more complex statement.
T2 = lookup(T1, barTable, [[amt][charge],[time][when]], 1.serial = 2.ssn)
This would copy over the amount and time of a bar tab based on a serial number lookup. (The example idea came from the "bar" in "foobar", and not from watching Cheers, BTW.)
Generally the "many" table of one-to-many joins would be the first table. Handling non-finds or multiple-finds could be specified
by a third optional column in the expression table, or additional
Choices for non-finds include, deleting target row,
replacing with nulls, replacing with zeros and spaces, or
raising an error. The null replacement would be similar
to an SQL outer-join in results, while deleting target
row would be similar to inner-joins. (The
deletion does not happen to the parameter table
reference itself, but to the operation result.
See discussion below about Functional Programming
conventions.) Choices for multi-finds
include grabbing any one of the matches, deleting
target rows, nulls, zeros
and blanks, or raising an error. Another possibility
is to supply feature(s) to easily add a (temporary) column which indicates the number of finds, and lets later operations then decide
what to do based on the match count. Note that
multi-finds probably indicates bad schema
design or lack of referential integrity. However,
programmers often inherit bad tables and
therefore need to deal with them the best they can.
The middle expression in the square brackets is actually a field translation table. The example
above would be translated into a table like:
If you have more rows, you may want to even write the statement as something like
T2 = lookup(T1, barTable, [ [amt] [charge],
], 1.serial = 2.ssn)
if you want to make it visually resemble such a table. You could also reference an actual table that contained just such columns. The square brackets are simply a "short-cut" way to define and populate a temporary table. Comma's represent row separators in the
If a field mentioned in the first column exists in the first
table, then that field is replaced (in the result), but
is added as a new column if not in the first table.
I am debating whether to allow expressions
instead of just field names. The left column would still be the destination field, but the right column could be something like "1.x + 2.y", where "1" and "2" represent the first table and the second table in the parameter list.
T2 = lookup(T1, barTable, [ [amt] [1.amt + 2.charge],
[time][2.datewhen & 2.timewhen],
[nog] [2.tog * 1.thog],
], 1.serial = 2.ssn)
If expressions are voted out and you need to do calculated fields, then calculate the value to a temporary table before or after performing the look-up, not during. There could be a "Calc" operation, for example. For more complex calculations, you may need to add some "work columns". These would usually be virtual. For example, one could first create some temporary columns that match the columns that will come from a later Lookup operation. After the columns are copied over, then calculations could be done on the new view.
In place of or as an alternative to tables of expressions,
perhaps an approach similar to BS12 (above) can be adopted:
T2 = lookup(T1, barTable, amt = 1.amt + 2.charge,
time = 2.datewhen & 2.timewhen,
nog = 2.tog * 1.thog,
grig = snig,
1.serial = 2.ssn)
However, this makes it tough to distinguish between
expressions and assignments. Plus, the table approach
can easily have additional columns for extra
settings without adding more syntax.
Perhaps a special assignment operation could be
used to create the expression table, but with
its own assignment syntax.
A1 = assigns(amt = 1.amt + 2.charge, time = 2.datewhen ....)
T2 = lookup(T1, barTable, A1, 1.serial = 2.ssn)
This complicates the overall syntax a bit, but
may be more natural than the bracket syntax.
I have not decided on what the operations would be yet. Here are some candidates:
lookup - Similar to a Join (examples given), but could
also act as kind of an Update.
Filter - Filter a table based on an expression
groupSum - Summarize or group by an expression.
(see note below).
copy - Copy one table to another. An optional
field selection list can be supplied. (Copy
and Lookup may be consolidate-able into one.
this requires some pondering.)
Calc - Column calculations on a given table. May not
be needed if we go with the expression-enabled
Lookup command, since one just does an expression-based
look-up on the same table.
Order - Change or supply an ordering to a record-set view.
Update - Insert or update row(s). Insert is consolidated
into this because one may want to insert if the
record is not there (as an option). This may also
double as a UNION-like clause since our new
approach does not distinquish between a row
and a table.
Schema - Get a column description table.
Note: These may not be the same "primitives"
that Dr. Codd listed, but
they don't have to be the same ones in order to be
considered "relational". These
operations can be built out of the Codd primitives to make them
more natural or comprehendible to a wider audience of people.
It is roughly analogous to making an XOR (exclusive "or")
operation using existing AND, OR, and NOT Boolean operators.
Note that the functional-like syntax
makes it easier to add new operations than most SQL systems
allow, so one is not stuck with these if they don't like them
or the standards change.
I am not sure if the primitives should include things like averaging or not. Averaging is not very common, and can be calculated using temporary work columns. I am also thinking of having something like this instead of GroupSum:
x = Group(origTable, grouping_expression, grouped_text_columns,
summed_columns, averaged_columns, count_columns)
If you are not using one or the other, then
simply give a blank set "". (If you are not
using any to the right, then you don't have to
supply all parameters.) Rather than lists of columns, perhaps
the table-of-expressions approach can be used, similar to
the Lookup operation in construction. This would make
it resemble the BS12 (above) approach more.
One final advantage of this approach is that it can closely be
matched in client-side programs where actual functions
return record-set handles. This would allow similar
API's to be used across the board from small client-based
collection operations to large multi-transactional systems.
Past suggestions to do things similar to this created the criticism that since the SQL approach is generally not defined by a sequence, that there is room for the database engine to optimize the query process for maximum efficiency. In other words, SQL allegedly defines what the result is to be like, and not how to achieve it.
However, the suggested approach also does not necessarily define a strict sequence. As long as the *result* is the same, it does not matter how the database engine goes about performing the request. I suppose there could be a "non-atomic mode" that could be used to inspect intermediate results, but during production a given set of such commands would be assumed atomic.
A typical command resembles:
Y = op(X, R)
This works kind of like a "copy" of X to Y, but with some transformations specified by R, an expression or expression-table. The optimizer might notice that X is not being used for anything else after this operation. (There would be a kind of atomic block unit, similar to the way an SQL statement is considered atomic.) Thus, it can alter X in place internally rather than make an actual copy if it decides that is more efficient.
Whether defining it in such a way provides fewer optimization choices than SQL probably would require detailed expert analysis. However, it should be clear that some optimization is possible.
It is not really that different from SQL anyhow. SQL tends to be infix, while this approach is more prefix-based. Infix is harder to read for long expressions in my opinion.
y a (x b c d) e
T1 = x(b c d)
T2 = y(a, T1, e)
It is true that each operation is more isolated in scope in the suggested pattern. This may limit "wide-reaching" automated optimizations, reducing potential performance improvements. On the other hand, it could make parsing faster because the context is smaller.
Also note that the second pattern offers more opportunity to re-using a given record set since it is reference-based instead of nesting-based. You can reference a given record-set view multiple times, but you generally cannot do such in a typical SQL expression without messy tricks.
To help with optimization, the rules of Functional Programming
can be assumed: no operation changes any existing tables, real
or virtual, until (or if) the whole block is complete. Note
that this would invalidate things such as:
// not allowed
T2 = foo(....)
T2 = bar(....)
Reassignments would not be allowed since it involves
altering an existing item.
Thus, a reference name can only appear as a parameter after
SQL's Annoying Split Personality
The nested nature of SQL often causes distracting
and confusing "list splits". Often smack in the middle
of a Select, From, or Where clause an entire
expression breaks the list into two or more pieces.
These expressions are usually another Where
clause nested in the middle of everything.
It brings up visions of an elephant sitting
in the middle of the busiest intersection of the town.
A reference-centric solution solves this by
allowing one to assign a name to a huge
expression, and just state the name as a place-holder
for the expression
instead of to actually present the entire bag of goods.
// Regular SQL
SELECT a, b, c, (SELECT foo FROM bar
WHERE m=n AND q=r AND.....) AS d, e, f FROM ....
// Reference-based SQL
SELECT a, b, c, $afoo AS d, e, f FROM ....
$afoo = SELECT foo FROM bar WHERE m=n AND q=r
The first approach makes it hard to visually
see that the list of Select columns ("a" through "f")
is all part of the same column selector list.
The second approach makes it easier because
our embedded "afoo" clause is only a name instead
of the whole kitten caboodle itself. Many Where
clauses can get quite long, much longer than
our example here, so that left hand
may be miles away from the right hand. The siblings
miss each other and start crying because somebody
went and built a 12-lane highway through the
middle of the house. (If you don't agree with
me by now, I threaten to unleash yet more wacky
If you think about it, subroutine calls are
essentially the same thing. Rather than nest
the entire contents of the routine, we simply
refer to the routine by name (and arguments).
Hiding the details so as not to disturb the
flow of related information is sometimes
called "abstraction". Under this definition,
SQL has poor abstraction.
Another possible advantage of "functionizing"
a relational language is that a DBA could
perhaps add functions lacking between vendors.
For example, if an Oracle database was moved
to a Sybase product, then application calls
that used syntax or functions not found in
Sybase could be added to match the Oracle
calls. SQL is too complex
to easily do this. It is a big wad of
SQL perhaps is too ingrained to expect the
industry to overhaul it. A possible compromise
may be to provide user-definable temporary views.
One could define one Select statement as a
named view that could then be referenced by subsequent
SQL statements as if it was a regular table.
I believe some vendors
offer a form of this using temporary tables,
but they don't seem to be integrated into
the optimization strategies. In other words,
there is a performance penalty. It seems to
be treated like a physical temporary table
instead of a syntactical substitution (when
possible). Some standardization would help
also. Perhaps something like this using
the INTO clause found in some dialects:
SELECT * INTO $foo FROM zag WHERE ....
SELECT * FROM bar, $foo WHERE ....
Both SELECT statements together would be treated
like a single query unit.
This is just a preliminary suggestion. SQL was purposely
designed to be more like natural language rather than
optimizing it to be programmer-friendly. This suggestion
is for a more programmer-friendly query or
data-transformation language. SQL can stay for
non-programmers, but programmers are probably
the primary user of SQL. Therefore, more thought
needs to be given to making a relational language
tuned to programmer needs.
In some ways, SQL parallels COBOL's design.
COBOL was (originally) purposely designed
to be as English-like as possible.
However, it was a little too English-like to many
programmers. The Algol language and
other block-oriented derivatives
found approaches that were more natural to most
8+ hours-per-day programmers. Programs grew
too complex for casual readers to figure them
out anyhow. Thus, benefiting occasional casual
readers at the expense of hard-core programmers
turned out to be a poor cost-benefit
tradeoff in practice.
It is like deciding whether
to optimize a car engine design around hobby
mechanics or professional mechanics. The majority
of drivers use the services of professional
mechanics for most repairs. Thus, optimizing
the design for hobbyists would hurt most
For a more refined version of the suggestions here, see Tops Query Language (TQL).