Differences between Standard SQL and the SQL version in MS Access

[magnusstefan]

Hi, all!



(I am not sure that this is the right category for the question, but I try here first since I could not find any SQL specific category in the main board.)

I need some help from you with pinpointing the differences between Standard SQL, for example 2003 SQL Standard, and the version used in MS Access. Since I couldn’t find any source for this I studied the subject from some books, and now tried to make a little list myself.

I have divided “the suspects” in four groups. (I am aware, that a really strict and logical categorization of this subject may not be possible. Nevertheless, I have made the categorization below just to make it possible to ask the question and also discuss the subject!)

1. SQL commands only in SQL Standard: NATURAL INNER JOIN, USING (together with any kind of JOIN), FULL OUTER JOIN.
2. SQL commands which works the same way in Access as in SQL standard, but has another “name”: “LEFT OUTER JOIN” is “LEFT JOIN” in Access. “RIGHT OUTER JOIN” is “RIGHT JOIN” in Access
3. SQL commands/functions which exists only in SQL standard, but who has a reasonable correspondence in Access: “CAST”. In Access you can do the same things as in CAST, but the functionality is split in several different functions like Cbyte(), Cint(), and so on.
4. SQL commands or functions that exists in Access, but not in SQL Standard: TOP, DISTINCTROW, TRANSFORM – PIVOT, Parameter queries: [], iif(), switch(), partition() and nz().

Have I missed or misunderstood something essential, or am I fairly well on track?

[pbaldy]

For starters, FULL OUTER JOIN is not supported in Access. TOP is certainly a standard, I just used it in SQL Server yesterday. I'll dig up a document Leigh Purvis put together when I'm at my computer, it has several differences discussed.

[magnusstefan]

For starters, FULL OUTER JOIN is not supported in Access. TOP is certainly a standard, I just used it in SQL Server yesterday. I'll dig up a document Leigh Purvis put together when I'm at my computer, it has several differences discussed.

Excellent! I am looking forward to your next answer!

/ML

[pbaldy]

He has a great FAQ here:

Beginning Sql Server Development - UtterAccess Discussion Forums

That is now a downloadable PDF. The part I was referencing is down near the bottom, titled "Core SQL Dialect Differences". If you're not a member there and can't get the PDF, I can copy the relevant part here.

[magnusstefan]

He has a great FAQ here:

Beginning Sql Server Development - UtterAccess Discussion Forums

That is now a downloadable PDF. The part I was referencing is down near the bottom, titled "Core SQL Dialect Differences". If you're not a member there and can't get the PDF, I can copy the relevant part here.

Actually: I created an account on the Utteraccess-site, and then it worked just fine to use the link you gave and download the pdf-file!
I now briefly went through the part "Core SQL Dialect Differences" and it is obviously useful for me!

In the bottom of the pdf there was also a link: http://sqlserver2000.databases.aspfa...ql-server.html A very good one with lot of further info about the differences between Access and SQL Server. (At this Point, I do not really need to compare with any other SQL-system.)

Thank's again, and I hope I can help you with something later on!

/ML

[pbaldy]

Happy to help!

[magnusstefan]

Hi again!

As I did write below, a did this a few Days ago:
"Actually: I created an account on the Utteraccess-site, and then it worked just fine to use the link you gave and download the pdf-file!
I now briefly went through the part "Core SQL Dialect Differences" and it is obviously useful for me!

In the bottom of the pdf there was also a link: http://sqlserver2000.databases.aspfa...ql-server.html A very good one with lot of further info about the differences between Access and SQL Server. (At this Point, I do not really need to compare with any other SQL-system.)"

I then I downloaded the document and used all the three links in the bottom of it. No problem. But today the first link (called "What are the main differences between Access and SQL Server?") Just does not work anymore! I goes to some kind of advertisement site. And the same thing happens if I go to to the actual discussion on utteraccess.com http://www.utteraccess.com/forum/index.php?&CODE=00

And of course, it it just this very document (web page) I need...

What can be wrong? Can you help me get the information on the page in any way)
(As I remember it, it had crucial facts about the syntax differences using JOIN queries, and that I need to clarify as soon as possible, and it is really hard to find valuable information about this...)

Regards,
magnusstefan