Comparison of Microsoft Jet SQL and ANSI SQL
Microsoft Jet database engine SQL is generally ANSI-89 Level 1 compliant. However, certain ANSI SQL features are not implemented in Microsoft® Jet SQL. With the release of Microsoft Jet version 4.X, the Microsoft OLE DB Provider for Jet exposes more ANSI-92 SQL syntax. Conversely, Microsoft Jet SQL includes reserved words and features not supported in ANSI SQL.
Major Differences
- Microsoft Jet SQL and ANSI SQL each have different reserved words and data types. For more information, see Microsoft Jet Database Engine SQL Reserved Words and Equivalent ANSI SQL Data Types. Using the Microsoft OLE DB Provider for Jet with Jet 4.X, there are additional reserved words.
- Different rules apply to the Between...And construct, which has the following syntax:
expr1 [NOT] Between value1 And value2
In Microsoft Jet SQL, value1 can be greater than value2; in ANSI SQL, value1 must be equal to or less than value2.
- Microsoft Jet SQL supports both ANSI SQL wildcard characters and Microsoft Jet-specific wildcard characters to use with the Like operator. The use of the ANSI and Microsoft Jet wildcard characters is mutually exclusive. You must use one set or the other and cannot mix them. The ANSI SQL wildcards are only available when using Jet 4.X and the Microsoft OLE DB Provider for Jet. If you try to use the ANSI SQL wildcards through Microsoft Access or DAO, then they will be interpreted as literals. The opposite is true when using the Microsoft OLE DB Provider for Jet and Jet 4.X.
Matching character Microsoft Jet SQL ANSI SQL Any single character ? _ (underscore) Zero or more characters * %
- Microsoft Jet SQL is generally less restrictive. For example, it permits grouping and ordering on expressions.
- Microsoft Jet SQL supports more powerful expressions.
Enhanced Features of Microsoft Jet SQL
Microsoft Jet SQL provides the following enhanced features:
· The TRANSFORM statement, which provides support for crosstab queries.
- Additional aggregate functions, such as StDev and VarP.
· The PARAMETERS declaration for defining parameter queries.
ANSI SQL Features Not Supported in Microsoft Jet SQL
Microsoft Jet SQL does not support the following ANSI SQL features:
- DISTINCT aggregate function references. For example, Microsoft Jet SQL does not allow SUM(DISTINCT columnname).
- The LIMIT TO nn ROWS clause used to limit the number of rows returned by a query. You can use only the WHERE clause to limit the scope of a query.