55

Can anyone recommend a good ANSI SQL reference manual?

I don't necessary mean a tutorial but a proper reference document to lookup when you need either a basic or more in-depth explanation or example.

Currently I am using W3Schools SQL Tutorial and SQL Tutorial which are ok, but I don't find them "deep" enough.

Of course, each major RDBMS producer will have some sort of reference manuals targeting their own product, but they tend to be biased and sometime will use proprietary extensions.

EDITED: The aim of the question was to focus on the things database engines have in common i.e. the SQL roots. But understanding the differences can also be a positive thing - this is quite interesting.

Adrian
  • 6,013
  • 10
  • 47
  • 68
  • 2
    What would be the point of having such a document? To actually use an actual database you need the document from the RDBMS producer with the product bias and product extensions. What are you trying to do? – S.Lott Nov 11 '09 at 11:31
  • 22
    I usually try to avoid proprietary extensions if an Ansi SQL alternative is available. – Adrian Nov 11 '09 at 12:48
  • 2
    Why? The proprietary features are usually more efficient. I never use ansii standard if a better t-sql version is available. Since most databases don't implement the standard completely or with the same results for the same queries (Oracle and SQl Server handle somethings differently and so the ANSII standard code will not always give the same results in both databases with the same records), using ANSII standard doesn't help you out much. – HLGEM Nov 11 '09 at 18:11
  • 10
    Not all the time. Sometimes they can be equivalent e.g. Oracle's join syntax http://www.dba-oracle.com/oracle_news/2004_2_19_rittman.htm. That said, I don't mean proprietary extensions are evil, just that the options should be considered. – Adrian Nov 12 '09 at 22:47
  • 1
    HLGEM: What if the standard feature and T-SQL-specific feature are the same, as far as you know or are concerned? (I think it's pretty obvious that if you consider one better, you'd use it instead of the other.) –  Nov 19 '09 at 21:13
  • 9
    @S.Lott Adrian doesn't need a reason to ask the question. And any explanation he gives is irrelevant, as there can be others who have the same need for a different reason. For example, this morning i am trying to implement a relational wrapper around an ISAM database. I can't find the SQL-92 standard anywhere, or what the INFORMATION_SCHEMA views contain. Why i want it is irrelevant. – Ian Boyd Mar 06 '16 at 13:13
  • 1
    Current ANSI/ISO SQL-2016 reserved words can be found here: https://en.wikipedia.org/wiki/SQL_reserved_words – jarlh Oct 13 '20 at 17:41

10 Answers10

38

Here's the ‘Second Informal Review Draft’ of SQL:1992, which seems to have been accurate enough for everything I've looked up. 1992 covers most of the stuff routinely used across DBMSs.

bobince
  • 528,062
  • 107
  • 651
  • 834
  • 11
    ISO has a [list of freely available standards](http://www.iso.org/PubliclyAvailableStandards). It contains the _Information technology – Database languages – SQL – Part 1: Framework (SQL/Framework)_ (ISO/IEC 9075-1:2008). (This should be the first part of series of standards, which are _not so_ publicly available.) Its in PDF format, so it should be a _better_ read the (older?) text version. – Kohányi Róbert Nov 23 '11 at 15:58
  • 1
    The 2008 standard seems to be missing from the list. The 2011 standard is there. That, in turn, has been superseded by the 2016 standard, which now has json support. I haven't found a free version of 2016 anywhere. – ccleve Jun 28 '18 at 20:23
  • 1
    Oops -- the 2011 document isn't useful. It's only part 1, and part 2 is the one that contains actual sql syntax. – ccleve Jun 28 '18 at 20:31
17

SQL isn't like C or Java, where there is a standard for the language, and then a number of companies and organizations are implementing the language as best they can, following the standard.

Instead, the major databases came before the SQL standard, and the standard is a sort of compromise where every database vendor wanted to get their particular dialect and features in the standard.

Therefore, there is much more variety between databases than between typical programming language compilers, and to use a database, you really need to know that particular SQL dialect.

Having said that, I've got Gultzan and Peltzer's SQL-99 Complete, Really here in my bookshelf. It is a good book if you need to know what the standard really contains. (And yes, there is a newer version since SQL-99, but noone seems to care.)

EDIT: Actually, there is not just one newer version since SQL-99, but three: SQL:2003, SQL:2006, and SQL:2008. And still noone seems to care. Actually, many don't even care about SQL-99, so SQL-92 is still, in a way, "the standard".

Thomas Padron-McCarthy
  • 27,232
  • 8
  • 51
  • 75
  • 1
    +1 I use the same book as my reference for standard SQL. Quite a few vendors *have* implemented features from SQL-99 and SQL:2003, so it's not like no one cares! It just takes them 10+ years to get around to it. :-) – Bill Karwin Nov 17 '09 at 07:27
  • 6
    Isn't C exactly like SQL in this way, with lots of different competing extensions before the standardization effort? (Which produced C89, so it may look *now* like everyone always followed the standard, 20 years later.) –  Nov 19 '09 at 21:10
7

ANSI documents can all be purchased from -- you guessed it -- ANSI.

http://webstore.ansi.org/

S.Lott
  • 384,516
  • 81
  • 508
  • 779
5

The main problem with an ANSI SQL reference manual is that you can't find a DB which implements it. And when it does, then you'll find that ANSI SQL can't solve some of the daily problems. Which is why all professional databases define extensions.

So at work, you'll need a reference manual for the specific version of the database which you use.

Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
  • don't there is it sql lite who is full ansi-92? for study it could perfectly fit. And yeah ansi sql don't solve everyday problems but so does every sql dialect, meaning you need the procedural part and so on. but that's the sql spirit, the Q is just for query here, it's correct to do the rest wiht other specifics tools, are they language extensions or a languege where sql is embedded as a library – netalex Jul 09 '13 at 13:22
  • 1
    I have been working with Oracle, DB2, MySQL, Sybase, H2 and several more. None of them are very alike even when you just look at the Q (query) part. Oracle supports recursive queries and optimizer hints, for example. I'm not sure which parts of which standard SQL Lite implements but it's a moot point. If you limit your studies to ANSI-92, then you will stumble over a lot of extra details when you try to apply it the first time. I, for example, haven't felt a need to look into the standard since I left college 20 years ago. – Aaron Digulla Jul 09 '13 at 14:24
  • i think we are sayng the same thing. i mean, the non standard part in sql dialect is the part that for sqllite have to be played by the host programming language (c, javascript...) – netalex Jul 11 '13 at 18:07
2

This reminds me of my 2nd year university course where we learn relational theory instead of SQL.

Read a good book on Relational Theory. Database theory and practice have evolved since Edgar Codd originally defined the relational model back in 1969. Independent of any SQL products, SQL and Relational Theory draws on decades of research to present the most up-to-date treatment of the material available anywhere. Anyone with a modest to advanced background in SQL will benefit from the many insights in this book.

alt text

Oreilly January 2009

Community
  • 1
  • 1
Yada
  • 30,349
  • 24
  • 103
  • 144
2

I found the best way to learn SQL was to actually get to writing queries and understanding the nature of joins/conditionals etc. I found this link with a lot of DIY examples to be the best : http://sqlzoo.net/

Ritesh M Nayak
  • 8,001
  • 14
  • 49
  • 78
  • +1: although the site is a bit opaque, I've referenced sqlzoo.net often enough for DB-specific details. Good one. – BalusC Nov 20 '09 at 21:20
0

It's a littel outdated, but this book is really helpful is looking at how the differnt vendors implement things, I belive it includes ANSII standard.

http://www.amazon.com/SQL-Nutshell-2nd-Kevin-Kline/dp/0596004818/ref=sr_1_1?ie=UTF8&s=books&qid=1257963172&sr=8-1

HLGEM
  • 94,695
  • 15
  • 113
  • 186
0

I really like just about anything Joe Celko has written Celko's Books

Michael Riley - AKA Gunny
  • 5,074
  • 4
  • 42
  • 89
0

I think this may be helpful to you.

Understanding the ANSI SQL standard By: Kevin Kline

http://www.amazon.com/gp/product/1565927443/102-0105946-4028970?v=glance&n=283155

srikanth rongali
  • 1,463
  • 4
  • 28
  • 53
-1

The DevGuru resources always worked well for me: http://www.devguru.com/technologies/t-sql/home.asp

Although I must admit it's not strictly an 'ANSI' focused resource. I've always been MS SQL centric, and it was helpful to me when I was starting out. IMHO Your best bet will be to use several resources - specifically including at least one of for each DB platform you want to use.

To Quote the DevGuru intro for their T-SQL resource:

Although there are standards for SQL, such as ANSI SQL92 and SQL99, most databases use their own dialect and/or extentions. Microsoft's flavor of SQL used in SQL Server 7 and SQL Server 2000 is called T-SQL. While many of the examples in this quick reference may work on other databases, it is assumed that SQL Server 2000 is used, especially for advanced topics such as stored procedures.

Adrian K
  • 9,880
  • 3
  • 33
  • 59