Can I write portable SQL statements?

I am wondering if it is possible to write SQL statements that are 100% interoperable with most or all databases including:

• MariaDB/MySQL/Percona

• Postgres

• Microsoft SQL

• Oracle

• SQLite

(Eg, can I just follow a specific SQL standard? Eg, is there something similar to POSIX compliance standards for SQL?)

If so, are there any linting tools available that I can use in a git post-receive hook to reject SQL usage that doesn't follow such a standard or non-compliant SQL code without having to try to commit the code on all DBs?

No, not for any significant and practical amount of code anyway. You can try to hew to standards (e.g., use COALESCE rather than ISNULL) but there are too many differences, big and small. Off the top of my head:

• SQL Server supports double quotes and square brackets for identifiers; MySQL uses backticks.

• SQL Server supports TOP, most other databases use LIMIT.

• PostgreSQL only relatively recently implemented stored procedures, though in practice you could use functions instead.

• MySQL is case-sensitive for table names (but not field names) when running on UNIX, but not when running on Windows. SQL Server is case-insensitive either way (except when it is).

• CTEs and windowed functions are not available on all systems, and are not always implemented the same.

• SQL Server does not require a command delimiter (except when it does), but MySQL and Oracle do. MySQL also requires using an alternate delimiters when defining stored procedures; MS SQL does not support any such thing.

• Security is almost always different for different vendors.

• Error handling is always different.

• All of the above can change, and may have since I last worked with those systems.

Many people have written software with the goal of letting one write RDBMS-agnostic queries. Most of these experiments failed, and a few broke out of the lab, lurching across the landscape spreading destruction in their wake. But even the best won't have the performance of code written with the target system in mind.

StackExchange Database Administrators Q#257305, answer score: 61