UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> SQL    
SQL

SQL (Structured Query Language) is the language and syntax used to communicate with a database. It can be used to manipulate structure, as well as change data. Data Definition Language (DDL) is the subset of SQL that manages table and index structure. Data Manipulation Language (DML) is the subset of SQL used to add, update, and delete data.

Every database engine has its own particular flavor of SQL.

  • Access uses Jet-SQL (Jet is Access' default internal database engine)
  • SQL Server uses T-SQL (Transact-SQL)
  • MySQL uses MySQL Dialect

And there are plenty of differences between the different versions.

For example, here are few differences between different dialects. Please be aware that all vendors listed provide a means of enabling a different syntax (e.g. enabling ANSI-92 for Access) so all SQL listed below represents the default, out of the box syntax.

Object Identifiers
Access Jet-SQL [ObjectName]
SQL Server T-SQL [ObjectName]
MySQL Dialect `ObjectName`

Note: Be aware that MySQL uses backtick (`), which is different from single quote ('). Backticks are used for objects while single quotes are used for strings or dates as shown below.

Date delimiter & default date formatting
Access Jet-SQL #01/01/10#
SQL Server T-SQL '01/01/10'
MySQL Dialect '2010-01-01'


To write an IF...THEN...ELSE type of query
Access Jet-SQL IIF([MyField] > 100, "High", "Low")
SQL Server T-SQL CASE WHEN [MyField] > 100 THEN 'High' ELSE 'Low' END
MySQL Dialect IF(`MyField` > 100, 'High', 'Low')


To write an SELECT CASE... OR SWITCH... type of query
Access Jet-SQL Switch([MyField] > 200, "Very High", [MyField] > 100, "High", [MyField] > 0, "Low")
SQL Server T-SQL CASE WHEN [MyField] > 200 THEN 'High' WHEN [MyField] > 100 THEN 'High' ELSE 'Low' END
MySQL Dialect CASE WHEN `MyField` > 200 THEN 'High' WHEN `MyField` > 100 THEN 'High' ELSE 'Low' END


To restrict number of rows that may be returned
Access Jet-SQL SELECT TOP N ...
SQL Server T-SQL SELECT TOP N ...
MySQL Dialect SELECT ... LIMIT N, M


For more complex examples highlighting some commonalities and when it may be portable (or not). Note lack of object identifiers.

To write a query with 3-way joins Notes
Access Jet-SQL
SELECT ...
FROM (
   table_A
   INNER JOIN table_B
   ON table_A.ID = table_B.ID
) 
INNER JOIN table_C
ON table_B.ID = table_C.ID;
Note the usage of () to describe how join should be done. This is required in Access even though this is rarely the case in other vendors' SQL. Thus, the T-SQL used below would be considered invalid by Access database engine. However, both SQL Server and MySQL will accept this syntax as valid.
SQL Server T-SQL
SELECT ...
FROM table_A
JOIN table_B
ON table_A.ID = table_B.ID
JOIN table_C
ON table_B.ID = table_C.ID;
For this specific type of query, T-SQL is the closest to ANSI SQL compared to the other provided examples. MySQL's syntax below would be considered invalid because of the USING keyword.
MySQL Dialect
SELECT ...
FROM table_A
JOIN table_B
USING (ID)
JOIN table_C
USING (ID);
USING keyword is a MySQL extension replacing the equi-join for where column names from both tables are same. For non-equi-joins or where column names are not used, ON keyword must be used instead. The sample used for the T-SQL is considered valid by MySQL.

This makes it a little challenging when moving from one database to another, as your queries may need to be re-written to accomodate the new engine.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 14,355 times.  This page was last modified 01:43, 10 February 2012 by Jack Leach. Contributions by LPurvis, BananaRepublic, Walter Niesz and Ace  Disclaimers