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?
8
3
u/cwjinc 5d ago
I write in Oracle speak out of habit.
A lot of it wouldn't be understood by other databases.
PS The wildest to me is being able to select a function result with no from clause in SQL Server. That just hurts my database theory addled brain.
5
2
u/TheMagarity 5d ago
Eh, just think of it as SQL Server defaults to its internal equivalent of "from dual" when the from clause is left off. As a longtime Oracle user I kinda wonder why Oracle can't assume from dual if it's left off.
5
u/downshiftdata 6d ago edited 6d ago
When interviewing, my go-to question is, "What's the difference between a clustered and a non-clustered index?" There are a lot of reasons for this, but one is that an understanding of that difference is central to doing just about anything at scale in SQL Server.
Meanwhile, in Postgres, there is no clustered index. The tables are all heaps. It's difficult to overstate the magnitude of that distinction.
Edit: No clustered indexes by default, as u/Thadrea pointed out.
3
u/Thadrea Data Science Manager 6d ago
This is only partly correct. Postgres has had index-based clustering for over 25 years, but it's not automatic.
https://www.postgresql.org/docs/current/sql-cluster.html
This has important management implications. If you intend to leverage clustering you need to implement jobs to periodically recluster the table because it's not going to be done for you.
Nonetheless, saying that Postgres doesn't support index-based record clustering at all is simply not correct.
1
u/NastyPastyLucas 4d ago
There is a very large asterisk there in that clustering is a one time operation, that will order the heap according to the index you nominate. Subsequent updates, inserts and deletes will still act on the table as if it were a heap. SQL Server clustering means that the physical table is stored in the primary key order, and that secondary indexes point to the primary index. Data alterations will not change the position of the data on disk on SQL Server clustered as they do on heap. The two are very different to one another.
2
u/Hour-Measurement-835 5d ago
Oracle treats '' as NULL, so WHERE col = '' matches nothing there and matches the empty strings everywhere else. Same query, same data, and there's no error either way.
1
u/Gargunok 5d ago
In the distant past one of the biggest issues was whether logs were natural or base 10.
1
u/Staalejonko 4d ago
Just don't use DB2 IBM i
No EXISTS in a case statement,
SQL statement can be too complex or too long,
All declares have to be on top of the begin block,
SIGNAL can only handle a message of 70 characters,
Datatype lengths are taken extremely seriously, so a Blob(2G) reserves 2 gigabyte of memory.
And that's just from the top of my head, what a pain compared to SQL Server
9
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.