SQL in Access and SQL in MS SQL Server

Question

I have heard that SQL is mostly the same from program to program, but there are some differences. I am wondering if there are any differences in SQL between Access (2007 if it matters) and MS SQL Server? I wonder because I regularly use Access and want to learn SQL from a book, and I wonder if a book using MS SQL Server will serve my purposes? I am considering "Access 2007 Pure SQL" and "Beginning SQL Joes 2 Pros", the second of which uses MS SQL Server. Thanks for any help!

Answer 1

I would say that Access SQL and T-SQL (SQL Server) have more differences than similarities. Any appearance of similarity are due to 1) both being based on the SQL-89 Standard (but both T-SQL and the Standards have moved on greatly, Access not so), 2) the SQL Server team tried but failed to make Access2000 (Jet 4.0) compliant with entry level SQL-92 Standard (the de facto "bare minimum" Standard).

Take for example the UPDATE statement. In its simplest form, i.e. involving a literal or input parameter (scalar) values, the two broadly are the same. However, when updating one table using the values from another table, the latest T-SQL syntax (2008) supports the SQL-92 scalar subquery syntax, the SQL-99 and SQL:2003 Standards' MERGE syntax with useful proprietary extensions, plus its older proprietary UODATE..FROM syntax (which should be avoided nowadays because it allows potentially ambiguous results), all of which can optionally use SQL:2003 common table expressions (useful for simplifying the SQL-92 scalar subquery syntax).

For Access you are compelled to use its proprietary UPDATE..FROM syntax, which is not the same as the T-SQL proprietary UPDATE..FROM syntax but has the same problem of allowing potentially ambiguous results (but this time cannot be avoided!), unless the query involves aggregated values in which case you cannot use SQL at all (!!) and must resort to client side (non-SQL) procedural code (because Access does not support procedural SQL code, another huge difference from T-SQL).

Answer 2

MS Access uses JET SQL while SQL Server uses Transact SQL. For the most part, they are very similar. SQL in general is a programming language designed for managing data in relational database management systems. So all the flavors feature a common subset. But there are differences too. For more info, refer to this article on Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet. There are numerous other resources on web, but this should give you a quick picture of some differences.

Answer 3

There's multiple differences, even down to simple things like the string concatenation operator. Access uses &, SQL Server uses +. SQL is like English. There's British English, Canadian English, American English, Australian English, etc... Multiple dialects, mostly but not totally compatible with each other.

That's not to say that things are totally imcompatible - learning SQL on any DBMS is of use, because the core concepts of relational databases remain the same regardless of which DBMS you're on. It's just how you interface with them that's different.