Home » forums » LtU Forum

Slashdot asks: "how would you improve SQL?"

The charming people over at Slashdot are presently discussing how SQL could be improved. The usual trolls and bad jokes are of course present. But there are also a few interesting discussions, as well as references to LINQ and Tutorial D.

By Allan McInnes at 2005-11-04 02:24 | LtU Forum | previous forum topic | next forum topic | other blogs | 7779 reads

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

For the most part, I like SQL...

But two things that bug me (off the top of my head) are:

- AS: What language assigns a value to the name in the sequence of EXPRESSION AS NAME? Most languages that I'm familiar with do NAME = EXPRESSION. The reason that this bothers me is that the name of a resulting column gets placed at the end of a long expression. 

   SELECT
      CASE 
         WHEN Score >= 90 THEN 'A'
         WHEN Score >= 80 THEN 'B'
         WHEN Score >= 70 THEN 'C'
         ELSE 'F'
      END AS Grade
   FROM Test

A better solution is use the more standard binding operator (Note: Transact-SQL supports this non-standard extension to SQL).

   SELECT
      Grade = CASE 
                 WHEN Score >= 90 THEN 'A'
                 WHEN Score >= 80 THEN 'B'
                 WHEN Score >= 70 THEN 'C'
                 ELSE 'F'
              END
   FROM Test

(And AS long as I'm on this complaint, I dislike languages that put the type in front of the name - C, Java, etc. Names are what makes programs readible. Types are the ascriptions placed on names).

- Second complaint is the lack of binding expressions within the query. For example, the Grade phrase above can not be used in either other items within the SELECT clause or in the WHERE clause. Means you end up repeating a lot of expressions:

   SELECT
      CASE 
         WHEN Score >= 90 THEN 'A'
         WHEN Score >= 80 THEN 'B'
         WHEN Score >= 70 THEN 'C'
         ELSE 'F'
      END AS Grade
   FROM Test
   WHERE
      CASE 
         WHEN Score >= 90 THEN 'A'
         WHEN Score >= 80 THEN 'B'
         WHEN Score >= 70 THEN 'C'
         ELSE 'F'
      END IN('A','F')

Most large queries seem to have a lot of repitition involved.

By Chris Rathman at Sat, 2005-11-05 06:48 | login or register to post comments

Binding Expressions

I would have thought that a binding expression as part of the query was the wrong way to go. Having a binding expression implies some order of evaluation, and as far as I am aware, SQL is intended to be (correctly, I think) a declaritive language.

Having said that, you are right that the repetition of code, just like in other languages, is annoying, more difficult to maintain and more prone to mistakes.

I would have thought that a "standard" macro language on top of the basic SQL syntax (a la scheme or lisp) would be the best solution to this.

By Daniel Marlay at Sun, 2005-11-06 23:54 | login or register to post comments

Binding in the declarative sense

Declarative style programming allows binding of names to expressions/values - just not in the sense of state type reassignments - or an imperative style. In the example, the SQL binds the value of "Score" in each row to the name "Score" in the query. What it doesn't allow you to do is build your own expression to name binding on top of these.

Can't vouch for whether this is SQL standard, but it works in SQLServer:

   SELECT *
   FROM
      (SELECT 
         CASE 
            WHEN Score >= 90 THEN 'A'
            WHEN Score >= 80 THEN 'B'
            WHEN Score >= 70 THEN 'C'
            ELSE 'F'
         END AS Grade
       FROM Test) Test
   WHERE
      Grade IN('A','F')

Anyhow, the problem is not one of stepping outside of a declarative model. It's more of being able to assign names to expressions on a per row basis in the query.

By Chris Rathman at Mon, 2005-11-07 01:11 | login or register to post comments

Guess what I should say....

...is that I want a LET type clause.

By Chris Rathman at Mon, 2005-11-07 01:25 | login or register to post comments

LET-style binding via SELECT and AS

Can't you get LET-style binding using SELECT and AS? The down-side is that you get the idiosyncratic right-to-left SQL scoping.

LET x = <expression> IN <expression using x>

would be written as

SELECT <expression using x> FROM <expression> AS x

By Per Vognsen at Mon, 2005-11-07 03:12 | login or register to post comments

A bit like Haskells: wher

A bit like Haskells:

<expression using x> where x = <expression>

By Felicia Li Svilling at Mon, 2005-11-07 03:17 | login or register to post comments

Yes it is standard SQL

I can't recall which variant of the SQL standard introduced it, but subqueries are definitely a standard and supported across most SQL database (excluding MySQL of course).

By Daniel Marlay at Mon, 2005-11-07 01:32 | login or register to post comments

I would have thought that a b

I would have thought that a binding expression as part of the query was the wrong way to go. Having a binding expression implies some order of evaluation, and as far as I am aware, SQL is intended to be (correctly, I think) a declaritive language.

No, it's not incompatible with declarativeness. It's true that in a declarative system you don't specify the order in which operations happen (or even which operations will happen), but you do specify dependencies between various values, which in turn may impose constraints on the order in which things are evaluated.

SQL has a notion of scope built into it, but it's really obscured by the syntax. Essentially, the FROM clause binds relation variables, which are visible in the WHERE, SELECT and GROUP BY clauses; the SELECT clause binds column variables, which are visible in the ORDER BY clause, and in expressions that embed the present query.

By em at Mon, 2005-11-07 06:51 | login or register to post comments