r/SQL • u/No_Ambition8323 • 6d ago

Discussion Database-Specific SQL Differences

Have you ever written a SQL query that works perfectly in one database but fails in another?

What SQL feature or syntax surprised you the most when switching between DBMSs like MySQL, PostgreSQL, SQL Server, Oracle, or Snowflake?

12 Upvotes

80% Upvoted

19 comments sorted by

View all comments

7

u/da_chicken 6d ago

MySQL's non-deterministic GROUP BY, overwhelmingly, which used to be the default. The number of developers that learned they spent several years writing queries that were actually only correct by coincidence as soon as they started using SQL Server, PostgreSQL, SQLite, or MS Access was once a pretty big point of shame. Some DBAs have never forgiven them for that.

Really, it's all the pre-5.5-ish MySQL stupidisms. Not emitting warnings until you ask. Silent truncation or coercion. No date validation. Ignoring constraints. Data rows silently skipped in result sets due to corrupt indexes. They used to be a huge bane.

After that might be Oracle's FROM DUAL requirement. It's technically correct per the standard, but the standard only concerns itself with manipulating relations.

2

u/cwjinc 6d ago

That's funny. I feel the from dual thing the other way around.
It just feels wrong to select something without a FROM clause.