1

(I'm asking this because I was reviewing this older question I asked and because I'm currently writing some queries and getting annoyed at how much I need to repeat myself).

In ISO-compliant SQL implementations, expressions and computed-columns in a SELECT clause cannot be referenced by other expressions in the same query (i.e. not in other expressions in the same SELECT, nor the same query's WHERE, GROUP BY and HAVING clauses). The result is that queries that perform simple text-processing operations end-up unnecessarily verbose.

Consider a query that returns the first-substring delimited by angle-brackets '<text goes here>' without causing runtime errors if the text is '> like this <' or 'this>':

SELECT
    CASE
        WHEN
            CHARINDEX( '<', textBlob ) > 0 AND CHARINDEX( '>', textBlob, CHARINDEX( '<', textBlob ) > CHARINDEX( '<', textBlob ) )
            THEN 
                SUBSTRING(
                    textBlob,
                    /*start:*/ CHARINDEX( '<', textBlob ),
                    /*length:*/ CHARINDEX( '>', textBlob, CHARINDEX( '<', textBlob ) ) - CHARINDEX( '<', textBlob )
                ) AS angleBracketText
    END
FROM
    table
-- Alternatively, move the CASE WHEN expression to the WHERE clause.

The only alternative which avoids any repetition of scalar function call-sites is to use derived-table inner-queries - which introduces repetition of column projections (which isn't as bad as scalar function repetition, but still tedious):

SELECT
    SUBSTRING( textBlob, openIdx, closeIdx - openIdx ) AS angleBracketText
FROM
    (
        SELECT
            textBlob,
            openIdx,
            closeIdx
        FROM
            (
                SELECT
                    textBlob,
                    openIdx,
                    CHARINDEX( '>', textBlob, openIdx ) AS closeIdx
                FROM
                    (
                        SELECT
                            textBlob,
                            CHARINDEX( '<', textBlob ) AS openIdx
                        FROM
                            table
                    ) AS q1
                WHERE
                    openIdx >= 0
            ) AS q2
        WHERE
            closeIdx > openIdx
    ) AS q3

Did Codd, et al. really design SQL to be used like this? I know that they intended for SQL to be a "natural language" (which was all the rage in the 1970s), which surely implies a degree of terseness and eliminating redundant logic (because natural-language has lots of self-references like "it", "this", "that", etc), so I doubt they really intended their language-rules to result in this kind of query being written...

So, at risk of asking an un-answerable question (as the original designers of SQL are away from the keyboard right now): Why did Codd, et al. prohibit intermediate scalars in SELECT - and how would they have recommended avoiding syntactic repetition like in the above queries?


Footnote:

(If I had my way (I wish...) I'd be able to express it like this...)

SELECT
    $openIdx  = CHARINDEX( '<', textBlob ),
    $closeIdx = CASE WHEN $openIdx >= 0 THEN CHARINDEX( '>', textBlob, $openIdx ) END,
    CASE WHEN $openIdx >= 0 AND $closeIdx >= 0 THEN
        SUBSTRING( textBlob, $openIdx, $closeIdx - $openIdx )
    END AS angleBracketText
FROM
    table
2
  • MySQL has user-defined variables which gets you some of that functionality - also, servers' (of all stripes) procedural extensions (e.g. PL/pgSQL, PL/SQL, T-SQL... &c.) can do what you require! – Vérace Jun 16 '20 at 8:34
  • @Vérace Right - though with T-SQL and Oracle this would have to be changed into a Scalar UDF as only MySQL supports (in non-ISO-compliant-mode) using named expressions in a query (Oracle does have macros, but I don't know if they cover this use-case). But to be clear: I am not asking "how can I do this?" - my question is "Did the (original) designers of SQL really intend for conceptually simple queries to end-up this complicated? If not, then how should it be done?". – Dai Jun 16 '20 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.