First among SQLs

Stob Legend has it that Edgar 'Ted' Codd got the idea for SQL while attending a 'Sky at Night' spin-off lecture. Patrick Moore, pointing at the blackboard, said: 'Select a star from the table'. 'That's it!', cried Codd, and ran out the door to follow up his inspiration forthwith, missing a good discussion of vulcanoids.

Though born in the '70s, SQL is sentimentally the last of the 1950s languages. While kernighan & pike & ritchie & thompson were going all lower case & curly brackets, the relational pioneers wanted S.Q.L.'s natural temporal home to be with F.O.R.T.R.A.N. and A.L.G.O.L and line printers the size of an Austin Mini. To this day, proper SQL is still mostly written in CAPS - ask any Oracle.

In the same way that COBOL was designed so that businessmen could use it, SQL was similarly imagined so that lawyers could easily pick it up. If you look carefully, you can still detect hints of this legal element in modern SQL code. Its best syntax has that quality of redundant thoroughness that one associates with the small print in licence agreements. A well-drafted SQL statement should look like shouting, but shouting in a nerdy-wordy way. Think John Major defending the Maastricht treaty at the dispatch box:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

or

GRANT SELECT, INSERT, UPDATE ON suppliers TO my_right_honourable_friend

Nonetheless some of the function names do have a certain kind of strange beauty, which the strstr() vowel-evacuated standard C library rather lacks. For example

COALESCE(ColumnName, ReplacementValue)

sounds like something that happens to null values if they are left out too long in the sun. And so it proves.

The early years of SQL were unhappily mostly taken up with fierce, unproductive disputes regarding which level of normal form was appropriate for practical database design. There was a period when admitting that one only designed one's tables to anything less than the ultra-strict Seventh Normal Form ('values of attributes in a string column not allowed to rhyme, grasshopper') was a social faux pas equivalent to being caught pinching the Pick 'n' Mix in Woolworths.

I will draw a veil over those unhappy times, and also over some of the escapades of the rather excitable denormalisation movement, which famously climaxed in the so-called 'Six O'Clock News incident', with one of their members humiliatingly being sat on by Nicholas Witchell.

SQL, the language

SQL is not without visionary simplifications of the kind that underlie all great programming languages. Whereas lesser parsers report a range of errors in the code fed to them, a strictly standard SQL may only report one:

error near ","

which was cunningly devised to be true-ish under nearly all circumstances.

(Most real-life RDBMSes do not adhere to this rule, but nonetheless stick to the spirit of error concealment. A colleague thoughtlessly named a column Address Id (sic), and in due course received the brusque and baffling warning

JOIN operator not supported

whereas what was actually meant was, of course,

Don't put spaces in column names you dork

He complained bitterly about this, but I cannot agree. Twenty-first century programmers are already ridiculously feather-bedded. Mutter mutter grump.)

Those of you familiar with modern exception-handling will now be wondering if SQL has an EXCEPT keyword. Certainly it does. However, there are two minor problems:

1. The version of SQL you are using doesn't implement it
2. It has nothing to do with exceptions.

Devised as part of SQL's cap-doffing to set theory, and of course the late Dr John Venn and his marvellous diagrams, EXCEPT is a sibling operator to the more familiar UNION; the former differing from the latter only in the detail that pretty well nobody knows what it means, and those who do know what it means don't use it.

In fact, great swathes of the SQL standard are unimplemented in actual RDBMSs. SQL standards define features that aren't implemented in the same way that Lieutenant Scheisskopf cancelled non-existent parades in Catch-22. So much lives below the water line of implementation, the language is an ISO iceberg. For example:

• CHECK constraints - you were supposed to be able to include SELECT statements in them. Bet your database software doesn't allow this.
• MERGE - combines UPDATE and INSERT, in the same way truth and justice may be combined to produce the superior concept of  'trustice'.
• Collection types - declare your columns to be arrays and multisets, a nod to the objects craze of the early 1990s.
• CUBE and ROLLUP - a nod to the data warehousing craze of the late 1990s.
• A formidable wodge of XML stuff you wouldn't touch with a proverbial - a nod to the XML craze of the early 2000s
• WIDTH_BUCKET - no idea what it is supposed to do; just liked the name.

This is but a tiny proportion of what your relational database probably doesn't offer you. Cheer up. At least this way you don't waste time thinking of ways to use this stuff.