MySQLism and PostgreSQLism: DELETE on JOINS requires the USING syntax
SQL-99 does not allow to delete data in a table when using JOINS. The reasons for this are quite obscure. Maybe it was considered too
MySQLism and PostgreSQLism: avoid deleting data in several tables in one query
SQL-99 does not allow to delete data in several tables in one query. The reasons for this are quite obscure. Maybe that it was considered
MySQLism: MySQL considers INT and BOOLEAN the same
SQL-99 standard does not consider that a boolean is an integer with 0 or 1 value. It may also be NULL, which is a third value. Therefore,
MySQLism: MySQL will automatically cast INT into VARCHAR/CHAR
SQL-99 forbids to cast data between integer and alphanumerical values. The reason is that such a cast may vary depending on locales and the
MySQLism: Use CASE instead of IF().
MySQL has a easy syntax that lets you execute IF/ELSE statements in SQL. For example:
MySQLism: Use SELECT(DISTINCT ) only for one field, use SELECT COUNT(*) FROM (SELECT DISTINCT ... ) ... for multiple
SQL99 specifies COUNT(DISTINCT ) as only taking a single parameter. MySQL and DB/2 support a list of fields for this fuction, Postgres will
MySQLism: avoid UPDATE on duplicate
MySQL has a nice SQL feature to update records when a duplicate key exist.
MySQLism: avoid ambiguous queries
See bug: http://drupal.org/node/1044410
MySQLism: avoid nested ORDER BY, use nested queries
SQL-99 allows nested queries, but forbids nested ORDER BY. The reason is that nested queries improve the ability of the parser to understand
MySQLism: avoid using IF() in SELECT use CASE()
Use CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END not IF()
MySQLism: avoid using MySQL IGNORE syntax
MySQL allows the use of IGNORE in UPDATE or REPLACE queries.
MySQLism: avoid using MySQL REPLACE syntax
MySQL allows running INSERT or UPDATE in one single query, which is extremely useful.
MySQLism: avoid using double quotes for alphanumerical values
According to SQL-99, double quotes should only be used to reference a column name or a table name.
MySQLism: use TEXT values and not VARCHAR(255)
When inserting TEXT into a VARCHAR(255), MySQL trims the value to the first 255 characters. PostgreSQL complains and returns an error, which
Oracleism: Don't use AS for table aliases
In both MySQL and PostgreSQL, when giving tables aliases in the FROM clause, it is optional to use the AS keyword.
PostgreSQLism: CONCAT() can only take two arguments (D6 only, fixed in D7)
The CONCAT() is supported on both MySQL and Postgresql, but only with two parameters. Where you could write (in MySQL):
PostgreSQLism: all non-aggregated fields to be present in the GROUP BY clause
This issue is seen very frequently.
PostgreSQLism: avoid mutiple ROW inserts (PostgreSQL < 8.2 only)
PostgreSQL 8.2, 83 and 8.4 are not affected by this issue.
Testing SQL 99 compliance
Writing SQL-99 compliant code is highly recommended. Mimer validator allows to test SQL-99 compliance online. Writing SQL 99 code ensures
Writing code compliant with both MySQL and PostgreSQL
This section explains how to write compatible SQL code for both MySQL and PostgreSQL and more generally any SQL99 compatible database. It