34

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?

58

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.

40

There are ANSI SQL standards, see for example the part on Interoperability and standardization in the Wikipedia article. Problem is, few actually follow these standards, which are often written and created post fact, when years of history has already tied the hands of various database products to do things differently.

Not all is lost, however. With modest goals, such as a web application with little need of complex queries and reporting, it is an achievable goal to have a list of database backends you support. For example, your list above. Only add minimum version numbers to it so you know what you actually aim to support and test for. Test, I'm afraid, you must.

In your application code, expect to limit yourself to very basic SELECTs, UPDATEs and INSERTs.

  • Do find a database abstraction layer that allows you to have parameterized, prepared queries. Escaping strings can vary wildly, even based on what settings are currently enabled in one given database product. If you must include a fixed string literal, ensure it's 'single-quoted' and can't contain control characters, nulls, backslashes, quotes and such.

  • Ensure all your identifiers - table, column names, aliases - cannot possibly be reserved keywords (from, select, left, count and so on), essentially avoiding all simple English words. Otherwise you'd need to quote them, and that's a can of worms. Best keep all of them all lowercase, but don't expect that you'll get them returned back with that casing.

  • Don't count on any SQL function other than the common aggregate functions in a GROUP BY query. Basically, COUNT(), MIN(), MAX(), SUM()

  • Addition, subtraction, multiplication of numbers are generally safe, discounting data type range limits. Don't expect to use division or modulus, and most especially don't try to concatenate strings on the SQL server side. All of them could do it, of course, but in slightly different ways.

  • Don't attempt to use the LIKE operator.

  • Expect to ORDER BY only plain numbers, and keep ordering by strings on the application side. Support for collations vary greatly. If the column you order by can contain NULLs, expect that those could either order up top or the very bottom.

  • If you must store binary data (BLOB, VARBINARY etc.) in the database, expect having to painstakingly test and handle differences between all your supported database backends one by one, both for retrieval and storage.

If you stick to these, then the bulk of your work will be on the DDL side, creating your database, defining your tables, hand-customized for each database you chose to support. Generally everything supports VIEWs these days, so you might even be able to abstract away differences in functions, operators, and provide consistent views to your application, that look to your code as if it were the same "table," despite variations in how you had to define it for each database.

Pain points to look out for:

  • Stick to signed, 32-bit or 64-bit integers and bigints. You'll need a lot of extra care if you must have decimal numbers. Not impossible though by any means with a defined list of backends you support.

  • Textual values character set and length. These days, you want to be able to correctly store and handle everything, including emojis. Test with those, and figure out what's needed. Eg. MySQL historically, and MariaDB still, calls what you need utf8mb4, and base utf8 won't do. In Microsoft SQL Server you want an _SC collation and only use NCHAR/NVARCHAR fields, alternatively starting with version 15 (2019) _SC_UTF8. Ensure you have made them large enough to hold as many characters as you expect. A utf-8 char(4) can only hold a single emoji (with no modifiers), not four. Be wary of large sizes though if you must have any kind of INDEX on a text column, as max limits can be painfully low.

  • Textual values, collation. Even if you took it to heart where I said previously, and don't rely on ordering by the database server, the collation still comes into play when determining equality. That matters both for selecting by value equivalence and unique keys! Always be cognizant and test that you're getting what you need. Case sensitivity or not, accent sensitivity, and so on. Achieving the desired result will vary greatly between various DBs, but is generally possible with some caveats. Do expect to spend plenty of time on this.

  • Obviously forget more esoteric types. Sets, enums, XML, arrays, whatnot.

  • Having a UNIQUE key on a NULLable column might allow for any number of NULL values, or precisely one, depending on database system. But you can retrofit and handle this in the database definition part to function the way you desire.

Also don't think you can as readily lump MariaDB and MySQL together these days. They have diverged in significant ways by now. Handle and test for them as if they were separate. Tools like dbfiddle are wonderfully useful.

Whether all the effort is worth it, in light of easily ending up with a lowest common denominator solution that does not really take advantage of the strengths of any specific database backend, is a question you have to answer for yourself. Many blogging, CMS and similar systems have found it useful to, for example, support both MySQL and PostgreSQL at least.

  • 2
    Perhaps worth noting that whilst compliance with the standards is patchy, some do better than others. If you can restrict yourself to databases with reasonably good compliance, you can use a larger subset. I've found PostgreSQL and HSQLDB pair well together, for this reason. – James_pic Jan 15 at 12:32
  • 1
    Another “pain point” I've encountered in practice: MS SQL Server doesn't allow Boolean expressions in arbitrary contexts, so you can't write SELECT A = B FROM SomeTable, but must rewrite it as SELECT CASE WHEN A=B THEN 1 ELSE 0 END FROM SomeTable. – dan04 Jan 15 at 20:32
  • @James_pic - patchy or not, a standard is worth working toward. Sure enhancements like PL/SQL, but I know the lock-in I am getting if I start down that path. Other things are just pedantic and have little effect on functionality. Case sensitivity or DESC (MariaDB) vs \d (Postgres) is just silly. Who cares how you spell it or pronounce it, it's the same verb. Support an ANSI standard if you can, where you can. – James Shewey Jan 16 at 14:56
  • fds: Excellent points. Thanks for mentioning datatypes, collation,etc. A note: "In Microsoft SQL Server you want an _SC collation" While preferred when available, an _SC collation is not required for storing Supplementary Characters (which most emoji are). The _SC collations merely allow the built-in functions to properly work with Supplementary Characters. Please see my post for details. Also, please clarify that the new _UTF8 collations only affect [VAR]CHAR data. – Solomon Rutzky Jan 16 at 16:46
2

For sufficiently trivial statements - sure, yes.

SELECT field FROM table

should work everywhere, if you get your case right because some of those DBs are case-sensitive.

For anything that you are likely to need in an actual application - the other answers are spot-on.

  • I think "select" should work universally and less need to worry about case sensitivity. Bonus points for me b/c it is even more useless than your useless query. Portability issue: your query will fail if either 'table' or 'field' does not exist. My query makes no assumptions. – emory Jan 16 at 14:07
  • 2
    @emory: No bonus points for you: select; is not working in Oracle (OracleXE 18c 18.0.0.0.0) ;) – SebastianH Jan 16 at 16:29
  • 3
    @emory no, standard SQL requires the FROM clause. This is why some implementations supply a single row helper table. DUAL – Martin Smith Jan 16 at 21:07
  • 1
    Exactly. Thats why in the Oracle even for simple things like the execution of a function you need to use the system dummy-table dual e.g. SELECT sysdate FROM dual;. – SebastianH Jan 17 at 9:57
  • 1
    and even the implementation of a dummy single row table seems far from standardised en.wikipedia.org/wiki/DUAL_table#In_other_database_systems – Martin Smith Jan 17 at 10:15
2

Syntax is one thing (and the other answers have covered that already), but behaviour of seemingly identical statements is another thing.

I think that is something to beware of as well - might even be more important as problems with that might only show up very late.

Here are a few examples that may or may not be surprising to you:

Integer division

Postgres and SQL Server will return an integer if dividing two integers. Oracle and MySQL will return a decimal value in that case.

Take this sample table:

create table t (nr integer);
insert into t values (1), (1), (2), (2), (2), (3);

And a query that calculates the percentage of the occurrences for each number:

select nr, count(*) / (select count(*) from t) as pct
from t
group by nr;

Postgres and SQL Server will return 0 (zero) for every row, while MySQL and Oracle will return the expected percentages.

Behaviour of LIKE

SQL Server use some kind of "poor man's regex" as LIKE wildcards which might bite you if you are looking for e.g. a square bracket. Take this sample data:

create table foo (bar varchar(100));
insert into foo values ('2'), ('[42]');

And the following statement (which is 100% pure ANSI SQL):

select *
from foo
where bar like '%[42]%';

No DBMS will complain about the syntax. SQL Server however will return both rows, while all others will only return the one with [42]

(I deliberately took numbers in there, to not get into the case sensitive/case insensitive problem)

Unique indexes and NULL

Consider this table:

CREATE TABLE foo (col1 integer, col2 integer);
CREATE UNIQUE INDEX idx_foo ON foo (col1, col2);

The above will run on pretty much every DBMS without change.

Then consider two INSERT statements:

INSERT INTO foo (col1, col2) VALUES (1, null);
INSERT INTO foo (col1, col2) VALUES (1, null);

Postgres and MySQL will happily insert those two rows as NULL is never equal to anything, and thus they don't violate the unique index (constraint).

Oracle and SQL Server will refuse to insert the second row.

Foreign key evaluation

Take this self referencing table:

CREATE TABLE fk_test
(
  id          integer PRIMARY KEY,
  name        varchar(20),
  parent_id   integer,
  FOREIGN KEY (parent_id) REFERENCES fk_test (id)
);

The following insert is a single statement (100% ANSI SQL - but not supported by Oracle, but that's not the point here).

INSERT INTO fk_test 
  (id, name, parent_id) 
VALUES 
  (4, 'Four', 1),
  (3, 'Three', 2),
  (2, 'Two', 1),
  (1, 'OnNe', null);

As it's a single atomic statement, the foreign key references are all valid. The above runs in SQL Server and Postgres without problems as the treat the statement as a single atomic INSERT and checks the constraint on statement level. MySQL fails because it checks the constraint row by row, not when the statement ends.

The same is true when deleting multiple rows. Assuming we inserted all those four rows in the correct order and want to delete everything but the root:

DELETE FROM fk_test
WHERE id IN (2,3,4);

Again this fails in MySQL but works in Postgres, Oracle and SQL Server.

A similar thing can happen with unique constraints.

Locking

A big difference is also the (default) locking behaviour. While in Postgres and Oracle readers never block writer and writers never block readers (explicit locking using FOR UPDATE or LOCK TABLE aside) this might not be the case in SQL Server or MySQL. Oracle and Postgres don't have lock escalation either, so the locking behaviour is typically not influenced by the number of locks.


This is also the reason why I think that testing with a DBMS other than the one used in production makes the tests pretty meaningless (think: embedded/in-memory engines like H2 or HSQLDB vs. the "real" thing)

  • Regarding numbers and case sensitivity on SQL Server at least it depends on case sensitivity of collation whether N'⁴²' matches N'42' – Martin Smith Jan 17 at 8:15
  • @MartinSmith: I was more thinking about the difference between like '%[A]%'and like '%[a]%' when I wrote that – a_horse_with_no_name Jan 17 at 8:19
  • Yep, I was just pointing out that using numbers doesn't duck the case sensitivity issue entirely, though this too seems vendor specific. Those strings don't compare equal doing a case insensitive compare in Postgres anyway – Martin Smith Jan 17 at 8:29
  • @a_horse_with_no_name should the multi-row insert end with ; vs , – Jasen Jan 20 at 6:13
  • @Jasen: thanks, corrected – a_horse_with_no_name Jan 20 at 6:53
0

The more complex your SQL code is, the less portable it can be. For a particular app I need to support Oracle and MSSQL. Other, more complex differences aside, the string concatenation ( || vs + ) drives me mad.

Then, the drivers. You may be lucky to access these SQL servers in JAVA with its rather well-designed JDBC API. You may get a bit angry at C implementations or simply not find any driver for something else. YMMV.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.