SQL Criticism

Below are some criticisms of standard SQL. Note that I am not necessarily suggesting that SQL be abandoned, just that it may need some work done on it to improve its rough spots.

Divisability

For example, if the same equation appears more than once in an SQL statement, there is no way to factor them into one via subroutine-like syntax.

In larger statements, the nesting approach can get quite cumbersome. Although proprietary versions of SQL or the combining of strings using SQL API's can allow some of this, why is a solution not part of the standard? (See Alternative to SQL.)

Another syntactical annoyance is field selection. If a tables has 100 fields, and you want to specify about 30 of them, it gets tedius to name each one. One, but perhaps not only, solution to this is the use of field set groups in Data Dictionaries.

Set Orientation Often Not Intuitive To Most

Suppose you wanted to list the oldest worker at each location (lodge). Here is one SQL solution from [insert reference]:

  select name, lodging, age from worker w where age = 
    (select max(age) from worker where w.lodging = lodging)
    order by lodging

Although this SQL statement is fairly brief, I do not find it very intuitive. Part of the reason is that it is tough to break it into mental (or actual) parts in order to understand it bit by bit.

Many programmers, including me, often solve problems by envisioning how we would do it by hand, and then convert that to program code. I have yet to find a way to envision the above SQL as a straightforward physical task.

Let's see how we could break the problem down into a more procedural approach.

One approach that comes readily to mind is to sort by descending age and pick who comes to the top. We can also sort by lodge to group by location. Using simple SQL, or any other multi-field sorting utility, we could do this:

select * from worker order by lodging, age desc

If you run your finger down the table, the oldest person in a given lodge is the first name of each lodge group. In procedural programming, one usually detects a new group by comparing a "current" record to the previous. Thus, we can directly translate our physical approach to code:

  prev = "[init]"
  open "worker" as w
  addTempColumn(w, "mark", "")        // add a temp, empty column called 'mark'
  scan w order by lodging, age desc   // traverse each record with a loop
     if w.lodging != prev then w.mark = 'X'   // Mark 1st in new group
     prev = w.lodging
  endScan
  select * from w where mark = 'X'

The first SQL solution reminds me of Recursion in some ways. Recursion is not very intuitive to most programmers. Sure, if you use it often you will get used to it, and maybe it will even become intuitive. However, tree traversal is not a problem that is frequently encountered by most programmers.

The problem is that non-recursive solutions to tree traversal are rarely simple and intuitive. Therefore, recursion is still often the best solution to tree traversal, despite it's oddity.

In this case, our procedural solution has roughly 2 or 3 times more parts than the SQL solution. It is my opinion that the procedural solution is still preferable because it is much more intuitive than the SQL approach to most people. Thus, the advantage of its intuitiveness weighs more than the disadvantage of solution size.

(You could possibly argue that most programmers are "stupid", but if that is what is out there, then techniques and languages which claim to be "general use" must deal with that alleged fact.)

Now, let's see if we can provide language constructs which could simplify our procedural approach.

  select * from worker where lodging != prev.lodging
    order by lodging, age desc

There! Now we have a concise procedural way to solve the problem that is also intuitive, unlike the set-oriented SQL approach.

Another procedural way to break the problem into parts is to have an outer loop that loops for each category, and an inner loop to find the max of that given category. It would resemble this simple pseudo-code structure:

   for x = each lodge group in worker {
      find max(age) in x
   }

Or, perhaps:

   for x = each lodge group in worker {
      find record of max(age) in x
   }

1. Split the data into groups by lodge

2. Find the max age in each group (lodge).

In our example, "x" is simply the record set for one lodge (at a time). This is a very natural and intuitive algorithm generation process. It allows us to consider the problem from the viewpoint of a single lodge. The issue that there are multiple lodges has been moved to an outer loop, allowing to inside to deal with only one lodge at a time. (Note that it is possible that there could be more than one person with the same max age in each group, but it is only a relatively minor change in our example to deal with this.)

This also highlights a related annoyance of SQL. It is easy to find the minimum, maximum, etc. VALUE of a set or groups, but fairly tricky to return the ENTIRE record(s) that has the extreme value, especially when groups are involved. This tends to violate Bertrand Meyer's "continuity" principle. (Small changes in requirements should only result in small changes of code.)

Most SQL problems that require correlated sub-queries are generally much more intuitive when done with a procedural approach. I know of only one person who claims correlated sub-queries (CSQ) are intuitive, yet he was unable to explain them by providing written steps of the mental process to arrive at them. (He referred us to books about mathematical set theory.)

However, we could always provide a stepwise breakdown of our procedural approaches (general-to-specific), or at least provide a hands-on equivalent (pencil, paper, and rolodexes). CSQs so far appear immune to stepwise reduction in the traditional sense. I have yet to find a book that even tries to explain how to solve CSQ problems. They simply say, "here is the solution."

It is my opinion that SQL focuses on a set-oriented approach instead of a procedural/cursor-oriented approach because it is based on fields of mathematics familiar to the originators of SQL. Procedural/cursor oriented approaches, even though most would find them more intuitive, are (unfortunately) not related to any common school curriculum.

Note that some readers have claimed that set orientation is more optimize-able for speed and resources than procedural/cursor (P/C) approaches for table manipulation. The jury is still out on this, however, in many circumstances they appear to be right so far.

Even if CSQ approaches do turn out to be inherently faster than P/C approaches (which I don't buy at this time), the programmer should still be given a choice of intuitiveness over speed if desired, such as when execution speed is not a bottleneck for a given problem.

These next examples suggests a variation of SQL that is more "group friendly":

  SELECT PER GROUP TOP 1 name, lodging, age
    FROM worker GROUP BY lodging 
    ORDER BY lodging, age desc

  SELECT PER GROUP name, lodging, age FROM worker
    WHERE age = MAX(group.age)
    GROUP BY lodging
    SORT BY  lodging, age desc

Someone also suggested using the HAVING clause for a similar purpose. Their solution resembled:

  SELECT name, lodging, age FROM worker
    GROUP BY lodging
    HAVING age = MAX(age)
    SORT BY  lodging, age desc

One reader has suggested that SQL is missing a "universal qualifier", sometimes known as "FOR ALL". This universal qualifier is part of standard set theory, but is missing in SQL for some reason.

These are only preliminary suggestions for improving grouping operations. These approaches may also make auto-optimization easier than the for-each-group example does.

Native Calls Lacking

Working with SQL API's in a programming language can be likened to dealing with a bank teller. All transactions have to go through a little window between you and the bank. Actually, a country border crossing booth is a more accurate analogy because all your stuff has to be packaged a certain way such that it can be properly carried and inspected.

SQL is a protocol between your program and the database server. Although SQL is quite powerful, there are times when you want to manipulate the tables, yet do something that also requires a complex algorithm.

For example, suppose you created a nifty function in your favorite language that translated English to Spanish. Example usage:

  spanish = Eng2Spanish("Pull my finger")

  Update parts set descript = Eng2Spanish(descript)

Thus, the power of your SQL server is somewhat isolated from the power of your favorite programming language.

It does not have to be this way. In XBase, for example, one can easily say:

  Use parts
  Replace all descript with Eng2Spanish(descript)

Virtual Direct Access To Tables

To manipulate or reference individual fields, especially for dealing with GUI's, one often ends up doing something roughly similar to this:

  record = getfromSQL(sql_access_statment)
  title = record.getField("title")     // store to memory variable
  displayGUI title, ...[other fields]
  record.setField("title") = title    // transfer to field
  record.save

Also note that some variations require one to replace the equivalent the setField operation with code to put the value into an SQL Update command.

Rather than doing the get/set thing to translate fields to memory variables and then back to fields when we are done with them, wouldn't it be nice to "talk" to the field variable as if it was a regular variable? For example:

  open emp as e
  locate(e, where firstName = "Fred")   // find a Fred
  displayGUI e.firstname
  e.firstname = "Freddy"
  close e

Note that this approach can be done through an SQL translation layer. The "get" and "set" and their translation to SQL are done in the background. Also note that Data Dictionaries can also help with this process by automating or hiding the set/get process from the programmer.

A related topic is that sometimes returning a "result set" is not an efficient or natural paradigm for our needs. For example, suppose we had a table that had all the phone book entries for a big city. We want an application where one puts in a last name and the closest match is displayed in the middle of a scrollable grid, sorted by last name. If we looked up "Scrooge" for instance, we might get this:

We want the user to be able to scroll up or down as far as they want. Many of the non-set oriented database systems allowed one to issue a commend to return a "record pointer" to the nearest match. One could then scroll forward or backward one at a time using the record pointer. Here is an example:

  open phonebook as b
  selectIndex(b, "Lastname")   // show in last name order
  set near on           // setting to find nearest match 
  locate(b, where Lastname="Scrooge")  // put record pointer to nearest match
  while not eot(b) {    // while not end-of-table
     display b.firstname, b.lastname, b.phone
     skip(b, 1)         // move 1 record foward
  } 
  skip(b, -1)    // we could also go backward

Many SQL-based systems can do something similar with proprietary cursors; however, many of them also must first generate a result set on which the cursor will traverse. Returning a result set of a million names may not be very efficient or practical. (There are games one can play to reduce the result set, but they are often imperfect and kludgy.)

Thus, the set oriented paradigm is not always appropriate.

I have found that treating tables similar to memory-accessible entities makes many tasks more natural and strait-forward than the set-oriented approach.

Let's assume that you have a sort of hash-like table where you want to also track the number of times that a hash is accessed. Suppose we want the record deleted after we reach 10 accesses:

TABLE: hashy
FIELDS: thekey, thevalue, count

In Clipper you could do something like this:

Proc hashit(tablehand, mykey, myvalue)
  select (tablehand)
  seek myvalue   // assumed indexed selected (xbase is weird at this)
  if not found()
    append blank   // insert new record
    thekey = mykey
  endif
  thevalue = myvale
  count = count + 1   // it might also accept ++
  if count > 10 
    delete
  endif
Return

If I used my preferences it might look more like:

sub hashit(tablehand, mykey, myvalue)
  private t = tablehand  // shorten the reference a bit
  if not found(t, _where thekey = mykey)
    insert_blank(t)
    t.thekey = mykey
  endif
  t.thevalue = myvale
  t.count++
  if t.count > 10 do delete_rec(t)
endSub


However, SQL would require something like this:

procedure hashit(tablename, mykey, myvalue)
  private sql, mycount, rs
  mycount = 0
  sql = "select * from $tablename where thekey = '$mykey'"
  rs = runSQL(sql)
  if rs.eof   // not found
    sql = "insert into $tablename (thekey) values ($mykey)"
    runsql(sql)
  else
    mycount = rs("count")
  end if
  rs.close
  sql = "update $tablename set thevalue = '$myvalue', "
  sql = sql & " count = count + 1 where thekey = '$mykey'"
  runSQL(sql)
  if mycount > 9
    sql = "delete $tablename where thekey = '$mykey'"
    runSQL(sql)
  end if
endProcedure

Note that this does not necessarily require that the record be locked during the duration of our activities. The changes could (hopefully at our option) be buffered until we tell it to write them or until a waiting period is met.

SQL triggers can perhaps be used. However, triggers may separate the logic from where it is needed/used. The example(s) I gave may only need to be done for one application or script. Triggers are generally meant for global business rule enforcement. Even if you created and dropped triggers within the code, I find that solution a little on the "hacky" side. What if the program croaked in the middle of setting the triggers and thus never got around to removing them? (They are essentially global) It is also not as compact as my original suggestion.

Inconsistency

Simplifying Joins

  SELECT *
  JOIN invoices TO invcDetails,
       invoices TO customers

If we want to specify outer joins, perhaps something like:

  SELECT *
  JOIN invoices TO invcDetails OUTER,
       invoices TO customers OUTER

In case somebody forgets to mention fields (not wanting the standard join), perhaps an explicit clause should be required:

  SELECT *
  JOIN invoices TO invcDetails STD,
       invoices TO customers STD OUTER

Alternative or Complimentary Specimens

In Xbase, one traverses tables by selecting (or making) a single "controlling" index. What appears in "next" and "previous" record pointer (cursor) movements depends on the controlling index. (Note that an SQL engine has to make a temporary index anyhow for orders that do not already have an index for them. However, the fact that SQL does it automatically is nice.)

When a new record is added, it defaults to a blank record (or as described by any defaults). This remains the current record until the cursor is moved. Thus, one can add, change, and delete the same record multiple times without having to reissue queries, unlike SQL.

Relational operations are done by specifying the links between two or more tables. When the cursor for one of the related tables moves, the cursor for the others is automatically moved. (An index is needed for the the relation key.)

Note that even though there is only one cursor at a time, disk buffering in RAM generally improves the speed of access. Thus, XBase does not have to necessarily physically read one record at a time from disk. (This of course depends on the size and order of physical records.)

XBase is far from perfect, and contains(ed) some archaic concepts. However, it can serve as a guide to cursor-oriented approaches and to integrating collection handling into the language to reduce DB-engine-to-code-and-back translations. (The cursor languages built into some of the commercial RDBMS SQL systems still lack this integration, beyond API's.)

There are times when the set-oriented approach is preferred and times when a cursor-oriented approach is preferred. Perhaps someday somebody will find ways to integrate all of them to give us the best of both worlds in a single package.

I have found that the API's needed for cursor and set approaches have enough similarities to perhaps simplify the integration. For one, when traversing records, set-orientation tends to first collect a "result set" into an internal buffer, whereas cursor-orientation tends to fetch each record as it is requested. (Note that buffering may still be used to speed up cursoring, however is not tied to a logical result-set.)

To the application programmer, these approaches look nearly the same in the code body. Most of the differences will be in the query or table handle setup code. Thus, if a collection system offered both approaches, one could switch from one to the other without major code changes. Thus, we avoid any major continuity problems.

   t = openRecordSetHandle(setUpStuff)
   while getNext(t)
      print t.a
      print t.b
      t.c = foo
      t.b = bar
   end while
   t.save   // may not be needed in some cases