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)
