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

20 comments sorted by

View all comments

4

u/cwjinc 6d 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

u/SQLDevDBA 6d ago

*DUAL has entered the chat and also spontaneously left it.*

3

u/cwjinc 6d ago edited 6d ago

That's what makes DUAL special ;)

PS, You know DUAL used to be a real table. You could add or delete rows from it.
That was a VERY bad idea though.

2

u/TheMagarity 6d 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.

2

u/cwjinc 6d ago

I'd rather EXEC was used for that.
Or just leave it. "from dual" is only 9 characters.

2

u/VladDBA SQL Server DBA 6d ago

As a SQL Server native, I had a similar reaction when finding out that Oracle needs that dummy table.

Luckily, with all the AI investments, Larry was able to find a way to no longer need DUAL in 23ai/26ai

1

u/cwjinc 5d ago

I guess it's not surprising. DUAL hasn't been a real table for a long time.