3 ms·

So this looks like a nice client which can be used with different databases, but do I still have to write database specific SQL? The first example I can think

So this looks like a nice client which can be used with different databases, but do I still have to write database specific SQL? The first example I can think of is the way you set the size of the result set in for the different databases (TOP vs. LIMIT vs. ROWNUM)[1]. I mean having one client to rule them all is good, but in my experience the harder problem is to learn all the different dialects depending on what database you use. [1]: https://www.w3schools.com/sqL/sql_top.asp https://www.w3schools.com/sqL/sql_top.asp

MarkusWinand 8y ago
This is something we have to blame the vendors for. There is an international SQL standard from ISO, it's just not commonly followed. However, sometimes the standard isn't very useful in itself. For this example, FETCH FIRST x ROWS ONLY is the syntax mandated by the standard. Although some databases accept this in the meanwhile, LIMIT might have been a better choice as it is supported by more databases. https://www.slideshare.net/MarkusWinand/modern-sql/120 https://www.slideshare.net/MarkusWinand/modern-sql/120 Edit: ps.: Please don't use w3schools.com as a SQL reference. It's utterly outdated, prefers vendor syntax over standard and is sometimes just straight wrong.
- laumars 8y ago
  There's a few areas I think ANSI SQL falls down compared to some of the vendor syntax. eg I really hate the way table joins are done in ANSI SQL. I get the logic behind the syntax but the PL/SQL syntax for table joins gives me far less mental gymnastics. In fact it is probably the only thing about PL/SQL that I actually like.
  - da_chicken 8y ago
    Most DBAs I know consider any comma join syntax difficult to maintain and difficult to debug because it's often difficult to tell the difference between join conditions and filter conditions. It's also very easy to mistakenly create a CROSS JOIN with comma join syntax, too. The real pain comes when you try comparing the old vendor specific OUTER JOIN syntax for Oracle and SQL Server. I guarantee you'll love JOIN ... ON ... over comma joins. Oracle: SELECT * FROM T1, T2 WHERE T1.PK1 = T2.FK1(+) SQL Server: SELECT * FROM T1, T2 WHERE T1.PK1 *= T2.FK1 Note that the outer indicator goes on the opposite side. Oh, and, of course, if you flip the order of the fields around, you've got to remember to flip the (+) operator. Which of these three are identical: SELECT * FROM T1, T2 WHERE T1.PK1(+) = T2.FK1 SELECT * FROM T1, T2 WHERE T2.FK1(+) = T1.PK1 SELECT * FROM T1, T2 WHERE T2.FK1 = T1.PK1(+) Now imagine you're joining 5 tables and want to reuse the JOIN syntax. Yeah. Fuck that. I'll take this any day: SELECT * FROM T1 LEFT JOIN T2 ON T1.PK1 = T2.FK1
    - laumars 8y ago
      I have genuinly written a lot of complex SQL for both MySQL and Oracle and honestly I do prefer Oracles syntax for joins. But I did spend several years writing PL/SQL before learning ANSI SQL so I guess it might just be a question of what you're used to?
      - GFischer 8y ago
        Maybe on what you got started on. I started with T-SQL, then PL/SQL and now back to T-SQL, so you can guess which I prefer.
      - da_chicken 8y ago
        I would just be aware that the prevailing opinion is that the ANSI syntax is considered better because it's considered clearer, more maintainable, and more functional. I was perfectly fine with comma joins until I had to interact with both SQL Server and Oracle. Then I found all kinds of hidden benefits. Like if I want to query the same set of tables, I can just copy the whole FROM clause and reuse it whole-hog. The logical separation is just nice, too. My tables know how they relate to each other. They know what type of join is being done. I don't have to tell the fields how they relate to each other. Even if I get my join conditions wrong, the relationships are correct. Even Oracle's own documentation[0] tells you to avoid the (+) syntax: > Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to [several restrictions], which do not apply to the FROM clause OUTER JOIN syntax[.] The doc itself lists all the issues, which is about 10 of them. None of these are opinions, either. They're actually technical limitations with (+), though they may not be ones that you encounter. The only advantage I know of for the (+) syntax is some uncommon issues with materialized view optimizations. [0]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm https://docs.oracle.com/cd/B19306_01/server.102/b14200/queri...
    - stareatgoats 8y ago
      Me too. Joins FTW.
- da_chicken 8y ago
  I'll disagree with you that we have vendors to blame. Vendors are trying to give users new features and support existing applications. The way new features get added to ANSI SQL is primarily by those features being adopted by multiple vendors and then one vendor's implementation (usually Oracle's) is selected as the standard. You also have to keep in mind that ANSI SQL is a pure relational language. It's artificial in many senses. There are essentially no details of implementation in ANSI SQL. These implementation details include everything from a modular database engine like MySQL, multiple index algorithms or clustering options like PostgreSQL, deep features like external procedural languages extensions, and so on. None of these type of features are considered by the standards body. It's entirely left up to the vendors to create them. Well, a lot of features like these end up becoming standardized precisely because they become popular ways to solve problems. Statements like BACKUP DATABASE and RESTORE DATABASE are not a consideration of standard SQL because that is considered an implementation detail. Point in time recovery and log file handling are all different. Other features like string aggregation, JSON and XML support, etc. all began as vendor extensions before they were standardized. Yes, sometimes the standard will create a new extension that no vendor supports or only minimally supports like the new MATCH_RECOGNIZE() expression. But for the most part it's the standard that lags behind and takes cues from the vendors. Even then, the results of standard SQL sometimes are not adopted by vendors because nobody needs the features the standards body invents. Indeed, the standards body has sometimes standardized what would later be identified as bad practices. Elements like NATURAL JOIN or KEY JOIN (I think that's in the standard) are considered bad design since they result in ambiguous column joining when you start adding multiple tables or modifying the schema. This is basically the exact same issue as XHTML vs HTML5 and browser vendors. It's not quite as bad since there's no W3C vs WHATWG political garbage (I can't imagine how bad it'd be if ANSI and ISO didn't cooperate) but it's the same kind of thing. Vendors are in competition and want to provide features that developers need. They're not going to wait for the ISO joint technical committee to decide what to do.
nicoburns 8y ago
Depends which databases you use. This is super useful for Postgres/Mysql/SQLite, which aren't completely compatible, but have a large amount of crossover (they all use LIMIT for example).
sixdimensional 8y ago
I have a story to tell about this. I worked on a project for a company once, that mapped a single custom SQL dialect to nearly every other dialect. We wrote a translator / mapper that converted the AST of that single dialect of SQL to every platform specific as close as we could. That included as many function mappings, data type mappings, SQL query expressions, as we could. Then we had custom drivers that could be used which parsed that SQL. One of the most difficult projects I've done, but it worked reasonably well, for most basic functions. We struggled with custom platform specific features of course, but even figured out workarounds for them. Of course, if a database didn't support a base level of the SQL standard (say SQL-92), for example, Cassandra - it was impossible for the translator to take a complex expression and handle it on its own - there was no target syntax to translate to. Anyways, point being - been there tried this, and it is possible. An open source implementation of what I did would have been awesome, alas, it was for a proprietary project. And ultimately, if you want one specific syntax, unless you find creative solutions around missing features between platforms (possible but much different problem that goes beyond syntax) - it is never quite as powerful for each platform. I still think every day about starting some kind of open source SQL gateway client project to normalize it and sit in front of databases. It's a big project though, and like I said, I question the value sometimes. Normalizing the syntax for databases that support SQL >=SQL99 (or even 2003 standard) would not be too bad though.

MarkusWinand 8y ago

This is something we have to blame the vendors for. There is an international SQL standard from ISO, it's just not commonly followed. However, sometimes the standard isn't very useful in itself. For this example, FETCH FIRST x ROWS ONLY is the syntax mandated by the standard. Although some databases accept this in the meanwhile, LIMIT might have been a better choice as it is supported by more databases. https://www.slideshare.net/MarkusWinand/modern-sql/120 https://www.slideshare.net/MarkusWinand/modern-sql/120 Edit: ps.: Please don't use w3schools.com as a SQL reference. It's utterly outdated, prefers vendor syntax over standard and is sometimes just straight wrong.

laumars 8y ago
There's a few areas I think ANSI SQL falls down compared to some of the vendor syntax. eg I really hate the way table joins are done in ANSI SQL. I get the logic behind the syntax but the PL/SQL syntax for table joins gives me far less mental gymnastics. In fact it is probably the only thing about PL/SQL that I actually like.
- da_chicken 8y ago
  Most DBAs I know consider any comma join syntax difficult to maintain and difficult to debug because it's often difficult to tell the difference between join conditions and filter conditions. It's also very easy to mistakenly create a CROSS JOIN with comma join syntax, too. The real pain comes when you try comparing the old vendor specific OUTER JOIN syntax for Oracle and SQL Server. I guarantee you'll love JOIN ... ON ... over comma joins. Oracle: SELECT * FROM T1, T2 WHERE T1.PK1 = T2.FK1(+) SQL Server: SELECT * FROM T1, T2 WHERE T1.PK1 *= T2.FK1 Note that the outer indicator goes on the opposite side. Oh, and, of course, if you flip the order of the fields around, you've got to remember to flip the (+) operator. Which of these three are identical: SELECT * FROM T1, T2 WHERE T1.PK1(+) = T2.FK1 SELECT * FROM T1, T2 WHERE T2.FK1(+) = T1.PK1 SELECT * FROM T1, T2 WHERE T2.FK1 = T1.PK1(+) Now imagine you're joining 5 tables and want to reuse the JOIN syntax. Yeah. Fuck that. I'll take this any day: SELECT * FROM T1 LEFT JOIN T2 ON T1.PK1 = T2.FK1
  - laumars 8y ago
    I have genuinly written a lot of complex SQL for both MySQL and Oracle and honestly I do prefer Oracles syntax for joins. But I did spend several years writing PL/SQL before learning ANSI SQL so I guess it might just be a question of what you're used to?
    - GFischer 8y ago
      Maybe on what you got started on. I started with T-SQL, then PL/SQL and now back to T-SQL, so you can guess which I prefer.
    - da_chicken 8y ago
      I would just be aware that the prevailing opinion is that the ANSI syntax is considered better because it's considered clearer, more maintainable, and more functional. I was perfectly fine with comma joins until I had to interact with both SQL Server and Oracle. Then I found all kinds of hidden benefits. Like if I want to query the same set of tables, I can just copy the whole FROM clause and reuse it whole-hog. The logical separation is just nice, too. My tables know how they relate to each other. They know what type of join is being done. I don't have to tell the fields how they relate to each other. Even if I get my join conditions wrong, the relationships are correct. Even Oracle's own documentation[0] tells you to avoid the (+) syntax: > Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to [several restrictions], which do not apply to the FROM clause OUTER JOIN syntax[.] The doc itself lists all the issues, which is about 10 of them. None of these are opinions, either. They're actually technical limitations with (+), though they may not be ones that you encounter. The only advantage I know of for the (+) syntax is some uncommon issues with materialized view optimizations. [0]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm https://docs.oracle.com/cd/B19306_01/server.102/b14200/queri...
  - stareatgoats 8y ago
    Me too. Joins FTW.
da_chicken 8y ago
I'll disagree with you that we have vendors to blame. Vendors are trying to give users new features and support existing applications. The way new features get added to ANSI SQL is primarily by those features being adopted by multiple vendors and then one vendor's implementation (usually Oracle's) is selected as the standard. You also have to keep in mind that ANSI SQL is a pure relational language. It's artificial in many senses. There are essentially no details of implementation in ANSI SQL. These implementation details include everything from a modular database engine like MySQL, multiple index algorithms or clustering options like PostgreSQL, deep features like external procedural languages extensions, and so on. None of these type of features are considered by the standards body. It's entirely left up to the vendors to create them. Well, a lot of features like these end up becoming standardized precisely because they become popular ways to solve problems. Statements like BACKUP DATABASE and RESTORE DATABASE are not a consideration of standard SQL because that is considered an implementation detail. Point in time recovery and log file handling are all different. Other features like string aggregation, JSON and XML support, etc. all began as vendor extensions before they were standardized. Yes, sometimes the standard will create a new extension that no vendor supports or only minimally supports like the new MATCH_RECOGNIZE() expression. But for the most part it's the standard that lags behind and takes cues from the vendors. Even then, the results of standard SQL sometimes are not adopted by vendors because nobody needs the features the standards body invents. Indeed, the standards body has sometimes standardized what would later be identified as bad practices. Elements like NATURAL JOIN or KEY JOIN (I think that's in the standard) are considered bad design since they result in ambiguous column joining when you start adding multiple tables or modifying the schema. This is basically the exact same issue as XHTML vs HTML5 and browser vendors. It's not quite as bad since there's no W3C vs WHATWG political garbage (I can't imagine how bad it'd be if ANSI and ISO didn't cooperate) but it's the same kind of thing. Vendors are in competition and want to provide features that developers need. They're not going to wait for the ISO joint technical committee to decide what to do.

nicoburns 8y ago

Depends which databases you use. This is super useful for Postgres/Mysql/SQLite, which aren't completely compatible, but have a large amount of crossover (they all use LIMIT for example).

sixdimensional 8y ago

I have a story to tell about this. I worked on a project for a company once, that mapped a single custom SQL dialect to nearly every other dialect. We wrote a translator / mapper that converted the AST of that single dialect of SQL to every platform specific as close as we could. That included as many function mappings, data type mappings, SQL query expressions, as we could. Then we had custom drivers that could be used which parsed that SQL. One of the most difficult projects I've done, but it worked reasonably well, for most basic functions. We struggled with custom platform specific features of course, but even figured out workarounds for them. Of course, if a database didn't support a base level of the SQL standard (say SQL-92), for example, Cassandra - it was impossible for the translator to take a complex expression and handle it on its own - there was no target syntax to translate to. Anyways, point being - been there tried this, and it is possible. An open source implementation of what I did would have been awesome, alas, it was for a proprietary project. And ultimately, if you want one specific syntax, unless you find creative solutions around missing features between platforms (possible but much different problem that goes beyond syntax) - it is never quite as powerful for each platform. I still think every day about starting some kind of open source SQL gateway client project to normalize it and sit in front of databases. It's a big project though, and like I said, I question the value sometimes. Normalizing the syntax for databases that support SQL >=SQL99 (or even 2003 standard) would not be too bad though.