How does Access differ from SQL 92 standards?

  • Thread starter Thread starter Access versus SQL 92
  • Start date Start date
A

Access versus SQL 92

Was just wondering in what ways does MS Access differ from SQL 92 standards.
I have noted that Access uses the '*' instead of the '%' when implementing
wildcards in database queries.
 
JET (the data engine in Access) supports a subset of the full SQL standard,
plus some Microsoft extensions that are not part of the SQL standard.

The actual differences vary with different versions of JET. Examples:

a) the TOP predicate is supported but, full outer joins are not.

b) DDL operations are more limited in Access than standard SQL.

c) JET 4 (Access 2000) supported more subquery operations than previous
versions.

d) Access 2007 supports multi-valued fields, completely non-standard, and
not supported in previous versions.

e) Access SQL uses different names for some data types. Comparison:
http://allenbrowne.com/ser-49.html

f) Access SQL has different bugs than the SQL standard, i.e. it yields
different results in several cases. see:
http://allenbrowne.com/tips.html#Bug
The section on 'Engine-level bugs' gives examples of how JET SQL can fail
on:
- PARAMETERS clause
- SELECT clause
- DISTINCT predicate
- FROM clause
- WHERE clause
- GROUP BY clause
- ORDER BY clause
 
Apart from 'bugs' and 'features', Jet SQL fits between
SQL 89 and SQL 92. It is a superset of 89, containing
important features that later made it into SQL 92.

This lead to the odd historical fact that early criticisms of
Access being 'not standard', up through the mid 90's
almost always meant the features which were the same as
SQL 92 (not standard with SQL 86), and after that started
to be about the features different from SQL 92

(david)
 
Back
Top