Pure SQL data layers tend to disappear in favour of O/R mapper which embed they own query language. The languages of today’s O/R mapper (e.g: Hibernate, JPA, etc.) is still close to the underlying target SQL syntax. Actually, some keywords in Hibernate will be taken as-is form HQL to SQL which means that using an O/R mapper does not provide 100% database independence necessary. There are also frequently a few native queries here and there to perform data-intensive operation that are easily realized with pure SQL, and do not match well with the object approach. Therefore it is still relevant to think about SQL-92 convention and to keep the SQL and HQL the more independent as possible. Below is a checklist I wrote a few years back when I was involved in the migration of a project from Informix to Oracle.
- Outer join & inner join
Review the syntax of your joins. - AS keyword
There is no need of a special keyword to alias a column. - Function (SQL92 or not)
Try to avoid using database function. If it’s absolutely necessary prefer the SQL92 subset and make your code easily portable. - Operator ==
The SQL92 standard uses = for equality. - Case type: num vs char
Complex SQL using case statement can be rewritten to be SQL92 compliant - lock columm name (reserved words)
Each RDMS has a list of reserved keywords. Try to avoid such name to make your SQL portable. - No usage of *
Do never use * to return rows with select. The order of your column is part of the physical database model and your code should work with the logical database model. Chances are, that the column order with change over time due to migration, or extension of the database model.
://ewernli 