Posso escrever instruções SQL portáteis?

Não, não para qualquer quantidade significativa e prática de código de qualquer maneira. Você pode tentar seguir os padrões (por exemplo, use COALESCEem vez de ISNULL), mas há muitas diferenças, grandes e pequenas. Em cima da minha cabeça:

• O SQL Server oferece suporte a aspas duplas e colchetes para identificadores; MySQL usa backticks .
• SQL Server suporta TOP, a maioria dos outros bancos de dados usam LIMIT.
• O PostgreSQL implementou procedimentos armazenados apenas recentemente, embora na prática você possa usar funções em vez disso.
• O MySQL diferencia maiúsculas de minúsculas para nomes de tabelas (mas não nomes de campos) quando executado no UNIX, mas não quando executado no Windows. O SQL Server não diferencia maiúsculas de minúsculas de qualquer maneira (exceto quando é).
• CTEs e funções em janela não estão disponíveis em todos os sistemas e nem sempre são implementadas da mesma forma.
• O SQL Server não requer um delimitador de comando (exceto quando o faz), mas o MySQL e o Oracle sim. O MySQL também requer o uso de delimitadores alternativos ao definir procedimentos armazenados; MS SQL não suporta tal coisa.
• A segurança é quase sempre diferente para diferentes fornecedores.
• O tratamento de erros é sempre diferente.
• Todos os itens acima podem mudar, e podem ter mudado desde a última vez que trabalhei com esses sistemas.

Muitas pessoas escreveram software com o objetivo de permitir que alguém escreva consultas agnósticas de RDBMS . A maioria desses experimentos falhou, e alguns saíram do laboratório, cambaleando pela paisagem espalhando destruição em seu rastro . Mas mesmo o melhor não terá o desempenho do código escrito com o sistema de destino em mente.

Existem padrões ANSI SQL, veja por exemplo a parte sobre Interoperabilidade e padronização no artigo da Wikipedia. O problema é que poucos realmente seguem esses padrões, que muitas vezes são escritos e criados após o fato, quando anos de história já amarraram as mãos de vários produtos de banco de dados para fazer as coisas de maneira diferente.

Nem tudo está perdido, no entanto. Com objetivos modestos, como um aplicativo da Web com pouca necessidade de consultas e relatórios complexos, é um objetivo alcançável ter uma lista de back-ends de banco de dados aos quais você dá suporte. Por exemplo, sua lista acima. Adicione apenas números de versão mínimos para que você saiba o que realmente pretende oferecer suporte e teste. Teste, eu temo, você deve.

No código do seu aplicativo, espere limitar-se a SELECTs, UPDATEs e INSERTs muito básicos.

• Encontre uma camada de abstração de banco de dados que permita que você tenha consultas parametrizadas e preparadas. As strings de escape podem variar muito, mesmo com base em quais configurações estão atualmente habilitadas em um determinado produto de banco de dados. Se você precisar incluir um literal de string fixo, certifique-se de que ele esteja com aspas simples e não possa conter caracteres de controle, nulos, barras invertidas, aspas e outros.

• Certifique-se de que todos os seus identificadores - tabela, nomes de coluna, aliases - não possam ser palavras-chave reservadas (from, select, left, count e assim por diante), evitando essencialmente todas as palavras simples em inglês. Caso contrário, você precisaria citá-los, e isso é uma lata de vermes. Melhor manter todos eles em letras minúsculas, mas não espere que você os devolva com essa caixa.

• Não conte com nenhuma função SQL além das funções agregadas comuns em uma consulta GROUP BY. Basicamente, COUNT(), MIN(), MAX(), SUM()

• Adição, subtração, multiplicação de números são geralmente seguras, descontando os limites de intervalo do tipo de dados. Não espere usar divisão ou módulo e, principalmente, não tente concatenar strings no lado do servidor SQL. Todos eles poderiam fazê-lo, é claro, mas de maneiras ligeiramente diferentes.

• Não tente usar o operador LIKE.

• Espere ORDER BY apenas números simples e mantenha a ordenação por strings no lado do aplicativo. O suporte para agrupamentos varia muito. Se a coluna pela qual você ordena puder conter NULLs, espere que eles possam ser ordenados na parte superior ou na parte inferior.

• Se você precisar armazenar dados binários (BLOB, VARBINARY etc.) no banco de dados, espere ter que testar meticulosamente e lidar com as diferenças entre todos os back-ends de banco de dados suportados um por um, tanto para recuperação quanto para armazenamento.

Se você se ater a isso, a maior parte do seu trabalho estará no lado DDL, criando seu banco de dados, definindo suas tabelas, personalizadas à mão para cada banco de dados que você escolheu para suportar. Geralmente tudo suporta VIEWs hoje em dia, então você pode até ser capaz de abstrair diferenças em funções, operadores e fornecer visões consistentes para seu aplicativo, que parecem seu código como se fosse a mesma "tabela", apesar das variações em como você teve que defini-lo para cada banco de dados.

Pontos de dor a serem observados:

• Atenha-se aos inteiros e bigints assinados, de 32 ou 64 bits. Você precisará de muito cuidado extra se precisar ter números decimais. Não é impossível, de forma alguma, com uma lista definida de back-ends que você suporta.

• Valores textuais conjunto de caracteres e comprimento. Atualmente, você deseja armazenar e manipular tudo corretamente, incluindo emojis. Teste com eles e descubra o que é necessário. Por exemplo. O MySQL historicamente, e o MariaDB ainda, chama o que você precisa de utf8mb4, e a base utf8 não fará. No Microsoft SQL Server, você deseja um agrupamento _SC e usa apenas os campos NCHAR/NVARCHAR, alternativamente, começando com a versão 15 (2019) _SC_UTF8. Certifique-se de torná-los grandes o suficiente para conter quantos caracteres você espera. Um caractere utf-8(4) só pode conter um único emoji (sem modificadores), não quatro. Porém, tenha cuidado com tamanhos grandes se você precisar ter qualquer tipo de ÍNDICE em uma coluna de texto, pois os limites máximos podem ser dolorosamente baixos.

• Valores textuais, agrupamento. Mesmo que você leve a sério onde eu disse anteriormente e não confie na ordenação pelo servidor de banco de dados, a ordenação ainda entra em jogo ao determinar a igualdade. Isso importa tanto para selecionar por equivalência de valor quanto por chaves exclusivas! Esteja sempre ciente e teste se você está obtendo o que precisa. Sensibilidade a maiúsculas ou não, sensibilidade a acentos e assim por diante. Alcançar o resultado desejado varia muito entre vários bancos de dados, mas geralmente é possível com algumas ressalvas. Espere gastar bastante tempo com isso.

• Obviamente, esqueça os tipos mais esotéricos. Conjuntos, enumerações, XML, matrizes, outros enfeites.

• Ter uma chave UNIQUE em uma coluna NULLable pode permitir qualquer número de valores NULL, ou precisamente um, dependendo do sistema de banco de dados. Mas você pode adaptar e lidar com isso na parte de definição do banco de dados para funcionar da maneira que desejar.

Também não pense que você pode facilmente juntar MariaDB e MySQL hoje em dia. Eles divergiram de maneiras significativas até agora. Manuseie e teste-os como se fossem separados. Ferramentas como dbfiddle são maravilhosamente úteis.

Se todo o esforço vale a pena, à luz de acabar facilmente com uma solução de menor denominador comum que realmente não aproveita os pontos fortes de qualquer back-end de banco de dados específico, é uma pergunta que você precisa responder por si mesmo. Muitos blogs, CMS e sistemas similares acharam útil, por exemplo, suportar pelo menos MySQL e PostgreSQL.

A sintaxe é uma coisa (e as outras respostas já cobriram isso), mas o comportamento de declarações aparentemente idênticas é outra.

Eu acho que isso também é algo para se ter cuidado - pode até ser mais importante, pois os problemas com isso podem aparecer apenas muito tarde.

Aqui estão alguns exemplos que podem ou não ser surpreendentes para você:

Divisão inteira

Postgres e SQL Server retornarão um inteiro se dividirem dois inteiros. Oracle e MySQL retornarão um valor decimal nesse caso.

Pegue esta tabela de exemplo:

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

E uma consulta que calcula a porcentagem das ocorrências para cada número:

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

Postgres e SQL Server retornarão 0 (zero) para cada linha, enquanto MySQL e Oracle retornarão as porcentagens esperadas.

Comportamento de LIKE

O SQL Server usa algum tipo de "regex do pobre homem" como curingas LIKE que podem mordê-lo se você estiver procurando, por exemplo, um colchete. Pegue estes dados de exemplo:

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

E a seguinte declaração (que é 100% puro ANSI SQL):

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

Nenhum SGBD reclamará da sintaxe. O SQL Server, no entanto, retornará as duas linhas, enquanto todas as outras retornarão apenas aquela com[42]

^{(Eu deliberadamente peguei números lá, para não entrar no problema que diferencia maiúsculas de minúsculas/não diferencia maiúsculas de minúsculas)}

Índices exclusivos e NULL

Considere esta tabela:

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

O acima será executado em praticamente todos os DBMS sem alterações.

Em seguida, considere duas instruções INSERT:

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

Postgres e MySQL irão inserir alegremente essas duas linhas, pois NULL nunca é igual a nada e, portanto, eles não violam o índice exclusivo (restrição).

Oracle e SQL Server se recusarão a inserir a segunda linha.

Avaliação de chave estrangeira

Pegue esta tabela de auto-referência:

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

A inserção a seguir é uma única instrução (100% ANSI SQL - mas não suportada pela Oracle, mas esse não é o ponto aqui).

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

Como é uma única declaração atômica, as referências de chave estrangeira são todas válidas. O acima é executado no SQL Server e no Postgres sem problemas, pois trata a instrução como um único INSERT atômico e verifica a restrição no nível da instrução. O MySQL falha porque verifica a restrição linha por linha, não quando a instrução termina.

O mesmo acontece ao excluir várias linhas. Supondo que inserimos todas essas quatro linhas na ordem correta e queremos excluir tudo, menos a raiz:

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

Novamente, isso falha no MySQL, mas funciona no Postgres, Oracle e SQL Server.

Algo semelhante pode acontecer com restrições exclusivas.

Bloqueio

Uma grande diferença também é o comportamento de bloqueio (padrão). Enquanto no Postgres e no Oracle os leitores nunca bloqueiam o escritor e os escritores nunca bloqueiam os leitores (bloqueio explícito usando FOR UPDATEou de LOCK TABLElado), isso pode não ser o caso no SQL Server ou MySQL. Oracle e Postgres também não têm escalonamento de bloqueio, portanto, o comportamento de bloqueio normalmente não é influenciado pelo número de bloqueios.

Esta é também a razão pela qual eu acho que testar com um DBMS diferente do usado na produção torna os testes bastante sem sentido (pense: mecanismos incorporados/na memória como H2 ou HSQLDB versus a coisa "real")

Para declarações suficientemente triviais - claro, sim.

SELECT field FROM table

deve funcionar em qualquer lugar, se você acertar o seu caso, porque alguns desses bancos de dados diferenciam maiúsculas de minúsculas.

Para qualquer coisa que você provavelmente precise em um aplicativo real - as outras respostas são pontuais.

Quanto mais complexo for o seu código SQL, menos portátil ele pode ser. Para um aplicativo específico, preciso oferecer suporte a Oracle e MSSQL. Outras diferenças mais complexas à parte, a concatenação de strings ( || vs + ) me deixa louco.

Em seguida, os motoristas. Você pode ter sorte de acessar esses servidores SQL em JAVA com sua API JDBC bastante bem projetada. Você pode ficar um pouco irritado com as implementações C ou simplesmente não encontrar nenhum driver para outra coisa. YMMV.