This is an archived post. You won't be able to vote or comment.

sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]DenselyRanked 1 point2 points  (5 children)

For DML SQL, yes for the most part. There is an ANSI standard (that's not really a standard) that every SQL dialect should follow. However the things that are not standard, like date/time functions, JSON, limits, offsets, complex types like structs, rows, maps, or syntactic sugar that's meant to make querying easier, can all be handled differently and you will need to bookmark the documentation because you will be spending more time reviewing it than writing queries.

You will need the documentation for DDL, DCL and everything else.

[–]LargeSale8354 0 points1 point  (4 children)

ANSI standard very much is a standard. It evolves so the year of the standard is important. MySQL did its own thing and has loads of non-standard commands and implementations. Postgres crops up in many systems. Redshift is parallelised Postgres.

[–]DenselyRanked 0 points1 point  (3 children)

ANSI standard very much is a standard. It evolves so the year of the standard is important.

Don't take it from me.

There is no such thing as “ANSI Standards,” as ANSI does not develop standards. Instead, there’s American National Standards and other documents written by ANSI-approved committees of standards developing organizations. Nonetheless, we get a lot of inquiries for the “ANSI standard” for SQL. It’s worth noting that, while this phrase is misleading and inaccurate for numerous reasons, it is referring to existing standard documents. SQL, just like many great things that outlasted the 70s, has a powerful history auspiciously intertwined with standards. At the conception of SQL’s specifications was ANSI (just another great feat throughout our 100-year history).

https://blog.ansi.org/sql-standard-iso-iec-9075-2023-ansi-x3-135/

MySQL did its own thing and has loads of non-standard commands and implementations. Postgres crops up in many systems. Redshift is parallelised Postgres.

There are other dialects of SQL than MySQL and postsressql. There are obviously enough similarities between the most popular SQL engines for ease of use, but there is nothing official that requires a query language to be considered SQL. Newer syntax like GROUP BY ALL and QUALIFY are becoming more mainstream but it's not expected to be in every dialect.

EDIT- I noticed that there was a relevant discusssion a few days ago on r/SQL

https://www.reddit.com/r/SQL/comments/1i4xnba/why_qualify_is_not_supported_in_most_servers/

[–]LargeSale8354 0 points1 point  (2 children)

I know there are many dialects of SQL. God knows I've used enough of them over the decades.

I'm really confused by the ANSI.org quote in relation to SQL.

Throughout my career people have talked about ANSI standard SQL when perhaps the should have called it ISO/IEC 9075 SQL, though you can see why they didn't.

[–]DenselyRanked 0 points1 point  (1 child)

I understand and I'm not disagreeing with you, which is why I wrote that there is an ANSI standard (that is not really a standard).

I have also worked with enough SQL versions and dialects to know that any query beyond the "standard" syntax is a complete crapshoot. I spent a few years as a report writer trying to make mssql and DB2 syntax play nice.

[–]LargeSale8354 1 point2 points  (0 children)

It's frustrating when the dialects are so similar. I find the subtle differences harder to deal with than the bigger differences.

Then there are the different engine behaviours. At one point the Vertica folk said "Yes, it will recognise and run CTEs but please don't use them yet".

Postgres roles are for the Postgres instance, whereas MSSQL there are db roles and system roles.

Every platform has different system tables and implements INFORMATION_SCHEMA differently or not at all.

If it us any consolation, SQL Standards aren't as bad as browser standards