5 ms·

Awesome SQL blog

155 points by niklas 17y ago

russell 17y ago
It's an excellent cookbook of useful SQL queries with well written explanations. I've used SQL for decades, but after reading a few articles, it was obvious that I could learn some new tricks. The articles are bite sized an tasty.
- quassnoi 17y ago
  Thanks!
sivers 17y ago
Fans of this will probably also like Joe Celko's SQL for Smarties. ISBN: 0123693799 It's unfortunate that a lot of new programmers are relying on their framework's ORM to abstract the SQL away from them, weakening their understanding of it.
- kingkilr 17y ago
  I think that's a little unfair to people using ORMs. Obviously I'd hope people don't want to avoid knowledge, but I'll be damned if I want to write SQL, it's not fun for me. That being said I contribute to the ORM of my framework of choice, if I write the solution to my problem once I'll never have to do it again. I think it's a bit like complaining that C programs are trying to abstract away assembly. Yes they are, but as long as they have some level understanding of how machines work they'll be ok, and a hell of a lot more productive.
  - JoachimSchipper 17y ago
    Yes, but C maps pretty cleanly to the underlying assembly, whereas there is a very large gap between (proper, nontrivial) SQL and the typical ORM. Perhaps "Haskell is trying to abstract away assembly" is a better comparison.
  - samuel 17y ago
    Personally I don't get ORM's. May be it's because the only I have had to deal with was Hibernate, and seemed to me as an innecesary layer of complexity and bloat, and objects and relations sometimes doesn't map nicely. If you want to persist objects, use an object datastore in the first place, not a relational one. And, what's wrong with SQL? It's simple, powerful, widely known and deployed, and cool(declarative programming, dude!). I've read a some introductory examples about distinct ORM's and just don't get it. Where's the gain? Apart from database independence(which I don't think it's even desirable in most cases, may be Tom Kyte's books have brainwashed me irremediably), can't see one. How an ORM makes you more productive?
    - ilyak 17y ago
      Hibernate is horrible. But, object datastores aren't an option most often. There are a lot of things wrong with SQL. For example, it's hard to do non-trivial queries because you can't say A = SELECT something FROM foo; SELECT another FROM bar WHERE field IN A. Of course, you can make a view, but views are database structure, and you really really really don't want to change database structure in runtime. So SQL sucks in some places. In object-oriented programs they have no use of your "tuples". They just can't do anything useful of them, nor give them away (because that would violate code separation). So they have to turn your tuples into some objects (or other objects). Therefore, ORMs.
    - gaius 17y ago
      The people who hate SQL don't know about any of the cool stuff it can do as they've only ever used MySQL's very basic implementation.
- notauser 17y ago
  I'm good enough at SQL to know about normal forms and how to write recursive queries. I also know that there is very little value in me writing a new set of CRUD queries for a low-load site when someone else has written a well-debugged interface for me.
  - artsrc 17y ago
    Are both a full featured object model, and an SQL database really essential for that kind of site? I essentially share the same SQL, OO, ORM hammer, as you, so I would likely use the same solution. It may be that all 3 complex technologies, the database, the object model, and the ORM are unwarranted by the low load/crud problem.
- pradocchia 17y ago
  I like this style better than SQL for Smarties. Celko tries to stick with the ANSI standard, which is fine, but I much to prefer to know how to get the most out of each platform. Like here, http://explainextended.com/2009/09/ http://explainextended.com/2009/09/, he covers adjacency list vs. nested sets on MySQL, Postgres, Oracle and SQL Server. Different platforms, different optimal solutions. The man knows his database internals.
- quassnoi 17y ago
  Hi, I'm the author of the blog. Actually, most askers (both on Stack Overflow and on the site) are either the ORM developers or the people who need to deal with huge volumes of data (financial reports etc). As with every other abstracting tool, it's perfectly OK to rely on an ORM — as long as you know which load can it handle and under which carpet the service hatch is.
- ilyak 17y ago
  That's because native SQL apis in programming languages are horrible (JDBC, you are horrible). So you want something to wrap your SQL with. And then, you only have objects in your program, that's why it's called object oriented. So you often need to make objects from the result of your query, and manual bean fill-up is tedious. Therefore, ORMs. On other hand, DBI is okay, so most Perl project don't use ORMs.
- artsrc 17y ago
  Maintaining a model feature in three places, the SQL, the class and a manual mapping, violates the basic principles of database modelling (store facts once), at the meta level (rather than the data level). ORM is a flawed attempt to remedy this. It is flawed because client/server sql databases create a distributed system, and distribution is hard to abstract away. Other solutions solution are to ditch the SQL model (something like bigtable, db4o or Gemstone?), or to ditch the object model (something like PHP or Access?).
leandrod 17y ago
Sorry, it is wrong. SQL is not baſed on ſets, but bags, which are quite different and more complicated. And, by the way, CTEs are a feature of ISO SQL, not only of MS SQL Server. Finally, entity‐relationſhip is not terribly practical as a model, but as diagrammiŋ.
- jacobolus 17y ago
  (It’s off-topic, but why the long esses and engs? The latter are not letters in English, and the former haven’t really been used in at least a century.)
- pradocchia 17y ago
  This is true, SQL does not require everything to be a set, strictly speaking, but internally many platforms append a unique row identifier to bags, so that they can be processed as sets. I often wish platforms would expose their internal calculus in a separate syntax, so we wouldn't have to work through the SQL translation layer. Algebraic operators would be nice too.
  - leandrod 17y ago
    Ðat may be true, but what platforms do internally ſhould not affect ðeir flavours of SQL. In oðer words, internal implementation as ſets do not make SQL ſet‐baſed.
    - pradocchia 17y ago
      No argument there (ðere?), SQL is an unfortunate language.
      - cturner 17y ago
        Do you have any thoughts about why there has been no challenge to the SQL syntax? We can easily imagine notations based on relational algebra and relational calculus emerging that could be interpreted at the same levels of an RDBMS as SQL, yet I've not seen anyone attempt it. A good PhD topic for someone?

155 points by niklas 17y ago

It's an excellent cookbook of useful SQL queries with well written explanations. I've used SQL for decades, but after reading a few articles, it was obvious that I could learn some new tricks. The articles are bite sized an tasty.

quassnoi 17y ago
Thanks!

sivers 17y ago

Fans of this will probably also like Joe Celko's SQL for Smarties. ISBN: 0123693799 It's unfortunate that a lot of new programmers are relying on their framework's ORM to abstract the SQL away from them, weakening their understanding of it.

kingkilr 17y ago
I think that's a little unfair to people using ORMs. Obviously I'd hope people don't want to avoid knowledge, but I'll be damned if I want to write SQL, it's not fun for me. That being said I contribute to the ORM of my framework of choice, if I write the solution to my problem once I'll never have to do it again. I think it's a bit like complaining that C programs are trying to abstract away assembly. Yes they are, but as long as they have some level understanding of how machines work they'll be ok, and a hell of a lot more productive.
- JoachimSchipper 17y ago
  Yes, but C maps pretty cleanly to the underlying assembly, whereas there is a very large gap between (proper, nontrivial) SQL and the typical ORM. Perhaps "Haskell is trying to abstract away assembly" is a better comparison.
- samuel 17y ago
  Personally I don't get ORM's. May be it's because the only I have had to deal with was Hibernate, and seemed to me as an innecesary layer of complexity and bloat, and objects and relations sometimes doesn't map nicely. If you want to persist objects, use an object datastore in the first place, not a relational one. And, what's wrong with SQL? It's simple, powerful, widely known and deployed, and cool(declarative programming, dude!). I've read a some introductory examples about distinct ORM's and just don't get it. Where's the gain? Apart from database independence(which I don't think it's even desirable in most cases, may be Tom Kyte's books have brainwashed me irremediably), can't see one. How an ORM makes you more productive?
  - ilyak 17y ago
    Hibernate is horrible. But, object datastores aren't an option most often. There are a lot of things wrong with SQL. For example, it's hard to do non-trivial queries because you can't say A = SELECT something FROM foo; SELECT another FROM bar WHERE field IN A. Of course, you can make a view, but views are database structure, and you really really really don't want to change database structure in runtime. So SQL sucks in some places. In object-oriented programs they have no use of your "tuples". They just can't do anything useful of them, nor give them away (because that would violate code separation). So they have to turn your tuples into some objects (or other objects). Therefore, ORMs.
  - gaius 17y ago
    The people who hate SQL don't know about any of the cool stuff it can do as they've only ever used MySQL's very basic implementation.
notauser 17y ago
I'm good enough at SQL to know about normal forms and how to write recursive queries. I also know that there is very little value in me writing a new set of CRUD queries for a low-load site when someone else has written a well-debugged interface for me.
- artsrc 17y ago
  Are both a full featured object model, and an SQL database really essential for that kind of site? I essentially share the same SQL, OO, ORM hammer, as you, so I would likely use the same solution. It may be that all 3 complex technologies, the database, the object model, and the ORM are unwarranted by the low load/crud problem.
pradocchia 17y ago
I like this style better than SQL for Smarties. Celko tries to stick with the ANSI standard, which is fine, but I much to prefer to know how to get the most out of each platform. Like here, http://explainextended.com/2009/09/ http://explainextended.com/2009/09/, he covers adjacency list vs. nested sets on MySQL, Postgres, Oracle and SQL Server. Different platforms, different optimal solutions. The man knows his database internals.
quassnoi 17y ago
Hi, I'm the author of the blog. Actually, most askers (both on Stack Overflow and on the site) are either the ORM developers or the people who need to deal with huge volumes of data (financial reports etc). As with every other abstracting tool, it's perfectly OK to rely on an ORM — as long as you know which load can it handle and under which carpet the service hatch is.
ilyak 17y ago
That's because native SQL apis in programming languages are horrible (JDBC, you are horrible). So you want something to wrap your SQL with. And then, you only have objects in your program, that's why it's called object oriented. So you often need to make objects from the result of your query, and manual bean fill-up is tedious. Therefore, ORMs. On other hand, DBI is okay, so most Perl project don't use ORMs.
artsrc 17y ago
Maintaining a model feature in three places, the SQL, the class and a manual mapping, violates the basic principles of database modelling (store facts once), at the meta level (rather than the data level). ORM is a flawed attempt to remedy this. It is flawed because client/server sql databases create a distributed system, and distribution is hard to abstract away. Other solutions solution are to ditch the SQL model (something like bigtable, db4o or Gemstone?), or to ditch the object model (something like PHP or Access?).

leandrod 17y ago

Sorry, it is wrong. SQL is not baſed on ſets, but bags, which are quite different and more complicated. And, by the way, CTEs are a feature of ISO SQL, not only of MS SQL Server. Finally, entity‐relationſhip is not terribly practical as a model, but as diagrammiŋ.

jacobolus 17y ago
(It’s off-topic, but why the long esses and engs? The latter are not letters in English, and the former haven’t really been used in at least a century.)
pradocchia 17y ago
This is true, SQL does not require everything to be a set, strictly speaking, but internally many platforms append a unique row identifier to bags, so that they can be processed as sets. I often wish platforms would expose their internal calculus in a separate syntax, so we wouldn't have to work through the SQL translation layer. Algebraic operators would be nice too.
- leandrod 17y ago
  Ðat may be true, but what platforms do internally ſhould not affect ðeir flavours of SQL. In oðer words, internal implementation as ſets do not make SQL ſet‐baſed.
  - pradocchia 17y ago
    No argument there (ðere?), SQL is an unfortunate language.
    - cturner 17y ago
      Do you have any thoughts about why there has been no challenge to the SQL syntax? We can easily imagine notations based on relational algebra and relational calculus emerging that could be interpreted at the same levels of an RDBMS as SQL, yet I've not seen anyone attempt it. A good PhD topic for someone?