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.
SQL
