Writing SQL Queries support multiple RDBMS engines (using ANSI SQL standards)

In this post Im trying to discuss some best practices when writing SQL queries to support multiple RDBMS's. In many cases UPDATE, DELETE, INSERT operation syntaxes are standard but when it comes to SELECT operations we need to focus on writing generic queries to support multiple RDBMS engines.

• Use COALESCE key word to handle null. 

eg:

SELECT COALESCE(CITY_NAME, 'Colombo')
FROM CITY

NVL() is for Oracle and ISNULL() is for RDBMS's like MS-SQL/MySql while COALESCE() is an ansi standard

• Do not user hardcoded boolean result when using COALESCE 

eg:

SELECT COALESCE(IS_MARRIED, 'FALSE')
FROM EMPLOYEE

This query will not execute some RDBMS engines like MySql where boolean datatype is treated as a binary.  So to avoid this we need to pass the default value (FALSE) as a boolean parameter

eg: 

SELECT COALESCE(IS_MARRIED, ?)
FROM EMPLOYEE

boolean isMarried = false;

ps.setBoolean(1, isMarried);

• Try to avoid String concatenation from SQL level. AFAIK there's no generic syntax to concatenate strings. (eg:  + sign is used in MS-SQL while || sign is used in Oracle to concatenate strings)

• Do not use 'AS' key word for making table name alias's 

eg: 

SELECT EMP.*
FROM EMPLOYEE AS EMP

This syntax is not generic, instead use;

SELECT EMP.*
FROM EMPLOYEE EMP

• Aggregate functions are generic in most RDBMS engines (eg: MIN, MAX, AVG, COUNT(*), COUNT)

• Key words like UNION, UNION ALL, DISTINCT are generic.

• Do not use ALL as a column/table alias

• Do not use alias partially

eg:

SELECT * FROM (
SELECT EMP1.EMP_ID, EMP1.EMP_NAME
FROM EMPLOYEE1 EMP1
UNION ALL
SELECT EMP2.EMP_ID, EMP2.EMP_NAME
FROM EMPLOYEE2 EMP2)
ORDER BY EMP_ID

This query will execute in engines like H2 but it will fail in MySql. If you are using alias's, use it everywhere.

SELECT EMP_ALL.* FROM (
SELECT EMP1.EMP_ID, EMP1.EMP_NAME
FROM EMPLOYEE1 EMP1
UNION ALL
SELECT EMP2.EMP_ID, EMP2.EMP_NAME
FROM EMPLOYEE2 EMP2) EMP_ALL
ORDER BY EMP_ALL.EMP_ID

Regarding DDL statements, always keep the entity names equal or below 30 characters.