9

I want to concentrate on one of the SQL language specs for learning and reference purposes. Which version of the SQL standard is most broadly supported across DBMS'es so that I can focus my time and energy on it ?

Improve this question

4 Answers 4

Reset to default
15

I think you are trying to live in Joe Celko's dream world where you can only use standard SQL and in any given week you may have to port all of your code from SQL Server to Oracle and then Oracle to DB2 and then back to SQL Server again. Twice.

While the core and fundamental aspects of standard SQL will help you anywhere, trying to limit yourself to that set of the language for fear of future porting (or just on principle) is not a path I'd recommend. Personally, I stick to ANSI standard stuff when I can (e.g. <> vs. !=, COALESCE vs. ISNULL, CURRENT_TIMESTAMP vs. GETDATE(), etc.), but I'm also not afraid to use the SQL Server-specific stuff that makes my work easier.

It is important to understand how SQL works in general; it is equally important to understand how the language works in your RDBMS(es) - including deviations from the standard, deviations from the way another RDBMS might have implemented the same concept, and proprietary extensions that don't exist elsewhere.

SQL Server, as an example, covers a good portion of the standard, and it gets closer to full compliance with each new version. Will it ever cover 100%? Highly doubtful. Will it continue to add proprietary extensions not in the standard? Certainly. If everyone covered the standard and nobody stepped outside of it, then there would be no advantages to choosing one platform over another, and we'd all be using the same thing.

Improve this answer
1
  • 1
    +1 For mentioning that less than 100% compliance is not necessarily a bad thing. While the ability to port your code over to various platforms might seem like a great thing, the fact is that you chose your RDBMS for a reason (or at least I hope the choice was more than just an arbitrary one). In the real world, the cost of porting your code over to a new platform is often more expensive than figuring out how to do it on your current platform.
    – Matt M
    Aug 7, 2011 at 21:14
11

None of them. You can learn standard (-ish) SQL for any RDBMS.

Standard SQL isn't used in the real world for paying jobs on successful projects...

Saying that, MySQL is a good place to start and here is a tutorial

Improve this answer
6
  • "Standard SQL isn't used in the real world for paying jobs on successful projects..." -- silliest thing posted on all of stack exchange.
    – Jonesome Reinstate Monica
    Jan 9, 2014 at 4:34
  • 3
    @samsmith why? Just because you don't happen to agree doesn't make it silly. Maybe you could explain why you disagree, instead of just calling it silly?
    – Aaron Bertrand
    Jan 9, 2014 at 14:00
  • 1
    @samsmith also, and especially if you are the down-vote, then I find your previous appeal for constructive guidance incredibly hypocritical. Where is your constructive guidance here for gbn to improve his answer?
    – Aaron Bertrand
    Jan 9, 2014 at 15:45
  • 3
    @samsmith If your goal is a "succesful project", then you need to take advantage of all of your RDBMS features. Failing to do so, and just using standard SQL is just wasting capabilities of your software
    – Lamak
    Jan 9, 2014 at 21:43
  • 1
    @samsmith Where are you getting your stats for "large %"? And what % of real world database systems are being designed by developers anyway? In my personal experience, and that of every customer engagement I've ever had, I have yet to come across a SQL Server shop that adheres to the standard. Ever. It is simply foolhardy to do so, regardless of what your developer friends claim they are doing.
    – Aaron Bertrand
    Jan 9, 2014 at 21:43
11

All major RDBMSs support the different versions of the SQL spec to different degrees, with the older versions of the spec more fully supported. Not all take conformance equally seriously (for example, Oracle started supporting 'ansi joins' in 2001, nearly a decade after SQL-92), and as @gbn already said, in practice you need to know the flavour of SQL used by each product you use.

Postgres is an exception in that it "prides itself in standards compliance". For "learning and reference purposes", you need hands-on practice with a database, not just knowledge from a book or standard, and postgres is an ideal platform for learning the ropes with because:

  1. It is free and available on most platforms
  2. The aforementioned standards compliance
  3. It has excellent documentation
  4. It is mature and widely used
  5. It has many of the features found in the most popular commercial RDBMSs, and quite a few that aren't
Improve this answer
3

Agreed with Jack and gbn, there is no standard and you need to make a choice. To make this choice, you need to select RDBMS first. I'd recommend to think about following things: 1) do you want to be DB-developer or DBA? 2) Which operation system(s) do you want to work with? I agree, this question is little strange, but when I talk with students, they say that it is important.

For example (just example, maybe I'm wrong) if you want to be a DBA and don't want to work with Windows, you don't need to think about MSSQL. And if you want to be DBA and you like to work with command-string and config-files - maybe Oracle is what you need. If you want to be developer want to use your knowledge in freelance projects, maybe you need MySQL?

Improve this answer
1
  • 2
    I hope these aren't the only criteria startups use to choose their platform. :-)
    – Aaron Bertrand
    Aug 6, 2011 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.