6 ms·
The problem with SQL is it seems everyone hates its guts. It is a weird obtuse kind of "non-language" that most programmers can't stand. I'm baffled. I'm a pro
by twic 12y ago
The problem with SQL is it seems everyone hates its guts. It is a weird obtuse kind of "non-language" that most programmers can't stand.
I'm baffled. I'm a programmer and i rather like SQL. Most programmers i know use SQL quite happily. Indeed, i often hear them wishing they could just write SQL to solve some problem rather than use some API that purports to save you from writing SQL.
I certainly prefer SQL to MongoDB's query language, which is verbose and unexpressive by comparison.
Am i in a tiny minority? Or is this SQL hatred a popular myth?
- dickinurass 12y agoLOL makes me want to dip my penis into your coffee
- nknighthb 12y agoI'm probably one of those programmers you think "use SQL quite happily". You're wrong. SQL is an abstraction with myriad problems in both its core design and its disjointed implementations. ORMs and similar interfaces are crappy abstractions on top of a crappy abstraction. You get the problems of SQL combined with the problems of the ORM. Preferring to remove a set of problems and be left with only the problems of SQL is not the same thing as liking SQL, it is merely preferring the lesser of two evils.
- mamcx 12y agoIn what sense SQL is bad?
- nknighthb 12y agoI see no reason to reproduce Wikipedia's laundry list: http://en.wikipedia.org/wiki/SQL#Criticism http://en.wikipedia.org/wiki/SQL#Criticism
- jeltz 12y agoWikipedia's laundry list contains of two items: * SQL not being relational algebra * SQL implementations being incompatible Both are real but I have not experienced either of these being a problem in real usage. Despite the problems caused by NULLs and that SQL allows duplicates I do not think the alternative would be better for real world programming. And the incompatibilities are avoided by picking one database per project and sticking with it.
- nknighthb 12y agoEven if you reduce it to its most basic issues, you missed a third: Incompleteness of implementations. If you have not experienced problems as a result of these core issues, it honestly makes me feel you must not have a lot of experience -- at least not in diverse projects and environments. That you think picking one database is some sort of viable solution in the general case amplifies that feeling. That is so often simply not an option, and even where supporting only one database at a time is an option, you absolutely cannot guarantee that you will not have to migrate later. I've been through that pain many times, it is a real-world problem.
- deleted 12y ago[deleted]
- deleted 12y ago[deleted]
- jeltz 12y agoI honestly think you should look at the database just like any other piece of your code, you cannot just replace GTK with WX Widgets and except there to be no migration work. And most of these differences between databases have nothing to do with SQL, but rather databases not implementing it correctly for legacy reasons. I would not expect a new database query language or NoSQL databases to do this better.
- Joeri 12y agoWell, I like SQL, but let me play devil's advocate and guess at some of the ways people may consider it "bad": - SQL queries flatten structured and hierarchical relationships into unstructured rows, often requiring very creative join operations to get at the exact set of data you want, after which you then have to convert everything back to the structured data you wanted in the first place. ORM's are a leaky abstraction on top of that, which means that when you use them you end up dealing with two problems instead of one. - SQL is superficially simple, but at scale the simplicity is a lie. Thhe performance trade-offs you make require you to understand the internals of the database you're running on and how it interprets the query, and often you feel like you're dealing with a too high level API. Often you're resigned to "tricks" to force the engine to do the right thing, like optimizer hints, special index types or denormalized data. - SQL has no ability to express relexation of the ACID principles. If you're streaming weather data into a SQL database, it could be ok to drop inserts here and there to gain throughput for the data analysis operations. SQL has no abilities to express such a thing (except in db-specific hacks).
- Pacabel 12y agoEven those reasons are really stretching for it these days. There are numerous ways of dealing with hierarchical or otherwise structured data using relational DBs. Support for recursive CTEs is becoming more common, which makes such querying quite clean and simple. And with many relational systems offering support for storing and manipulating XML and JSON data, storing more structured data becomes a non-issue. I think that SQL managing to hide a lot of the underlying implementation details is one of its strongest points. In many situations the defaults are more than sufficient, saving developers and users a significant time. Yes, there are situations where more control over the exact handling is needed, but it gives ways of managing this. At least a simplified, high-level abstraction is offered; many NoSQL systems immediately force you to take a very low-level approach for even the simplest of queries. And I don't see the ability to intentionally lose data as something that's good, either. The database should be concerned with doing everything in its power to protect and ensure the integrity of the data it has been given. If it's fine to lose some of that data, then whatever or whoever is providing that data should just not provide it to the DB in the first place. So while some people may present those as possible disadvantages of SQL, I just don't think the arguments really hold up upon further scrutiny.
- kitsune_ 12y agoParaphrasing Rich Hickey: It's primarily a user oriented language and there is no underlying abstraction for external programs or systems. In the end programmatic access boils down to using StringBuilders.
- jsemrau 12y agoPopular myth. Definitely. There is hardly anything as beautiful and simple as SQL.
- Amezarak 12y agoAgreed. Years ago, when I took a database class in college, I hated it. Several years later, I began working at a consulting firm on the support team. My primarily role was defect/enhancement tracking and resolution in the product we had developed for them as well as their satellite apps. The first time I really got my hands dirty with SQL was pulling out a bunch of VBA and Access "SQL" they were using to generate reports and to rewrite it into real SQL so it could be run on the backend, which cut times down from hours to milliseconds. It was okay (I'd hate those queries now) but it's where I really picked it up. Then the same client started calling me to write (or edit) queries to pull out some information from their database I don't think anyone had anticipated they'd ever need. That's when I started to really appreciate how beautiful and expressive SQL can be. Stop thinking imperatively - SQL will let you do it but don't. No cursors unless you absolutely have to do. You probably don't. Dynamic SQL has its place, but try to stay away from that too. There are a few queries I still remember with some fondness. One was to determine the number of days particular classes of employees worked on particular classes of projects. Unfortunately, the way their data was modeled made this not-at-all obvious - and the date information was stored in the "start" and "end" date type fields. There was a lot more to it, but after pulling my hair out - the customer generally needed these in under half an hour - I suddenly realized I could use a recursive CTE to generate a dataset of all days that I could use to join with, filter out any duplicates, and then use aggregation functions to get what I needed. There were a lot of other details because of the complexity of their model, but it worked and it just seemed so elegant at the end. I've moved on to another job now and honestly, writing SQL is what I miss most. I would have never pictured myself saying that before I worked there.
- joesmo 12y agoI think the sad proliferation of horrific ORM systems and the thousands of hours put into them by many people is proof enough that this is not just a popular myth. While I wouldn't say that most programmers hate it, I'd say there is a fair share of those who do. Then again, considering the horrors that are built to avoid SQL, one wonders if they should be counted as programmers anyway.
- jimktrains2 12y agoJust because I hate extracting the results of a query into a structure the program can use, doesn't mean I don't like SQL. It's a very nice language to write relational algebra in.
- twic 12y agoIndeed. The current crop of lightweight semi-ORMs (MyBatis, Ebean, ORMLite, etc) are built around that use case: let programmers write SQL, but automate the conversion of result sets to objects. You can also do this in standard JPA, to some extent, using constructor expressions, although those are pretty verbose.
- Pxtl 12y agoI like relational algebra. I like the idea of a data-store language whose core features are the CRUD operations. But I'll pipe up and say I don't like SQL. Verbose string management, monstrous 3-value boolean logic, the lack of any linguistic shortcuts for joining on foreign-key constraints, etc.. The problem is that a good replacement for SQL would be a modernized relational language, not a NOSQL language that throws the baby out with the bath-water.
- jimktrains2 12y agoYou mean shorter than "JOIN fktable USING (key_name)"? (Granted, that implies both columns have the same name iirc)
- twic 12y agoActually, yes. JPQL, the generally SQL-like query language in the Java Persistence API, lets you write joins through foreign key references (as most joins are, although of course not all) as if they were object property references. So instead of: select u.user_id from User u join Company c using (company_id) join Country k using (country_id) where k.code = 'br' You would write something like: select u from User u where u.company.country.code = 'br' That's pretty neat. I wish i could do that in SQL. If you want to fetch data from joined entities, then you still need to name them in an explicit join, but it's still easier than in SQL: select u, k.code from User u join u.company.country k where u.creation_date = current_date
- Pxtl 12y agoMicrosoft's Entity Framework is similar, except that they put the Select at the end so that the editor is aware of the availability of relevant variables when you're writing the "select" part of the statement. Such simple changes that will never be part of the SQL standard.
- sanderjd 12y agoYeah the SQL vs. NoSQL thing conflates so many different concerns. Sometimes it's about not wanting to do things relationally, sometimes it's about trading off ACID for other concerns, and sometimes (most rarely, it seems to me) it is actually about the SQL language itself. I don't think the language is really so bad (inconsistencies across databases aside), but I'd be interested in taking a look at a redesigned language that does all the same stuff but cleans a few things up.
- pessimizer 12y agoIt's not a popular myth, it's just become a trend. People want to use javascript or some dynamically-typed interpreted algol-like language for everything, and they're freaked out by SQL because it's both different and mathy. It's also very, very easy, and almost completely fat-free as a mathematical expression of what you might want to do with data. I learned almost everything I needed to know about it within an afternoon ~10 years ago, and the dividends paid since on that investment have been extraordinary. The use of "non-language" here just reflects the extremely narrow view of how languages must look amongst programmers of late.
- randallsquared 12y agoWhen I see people complain about SQL, "mathy" never seems to come up. Mostly it's "tries too hard to be English".
- marcosdumay 12y agoThose "hard to learn" complains are about SQL being mathy. But maybe if it didn't try that hard to be english-like, people would resort to learning the math and get it faster, or maybe it would mean that nobody would use it, and some other english-like language would take its place.
- eddieroger 12y agoI'm with you. I love SQL. I find it surprisingly expressive, despite having a rigid syntax. Some of my favorite projects that I've worked on thus far into life have been heavily SQL-based, and no successful code run has ever matched the thrill of seeing a SQL statement return what I expected. I'd venture to say NoSQL has gained it's popularity because it's Magic(TM), and it works well with equally magical frameworks. For frameworks that don't have generators like ActiveRecord, NoSQL replaces the need to boringly write your models and database logic. It's like every Getting Started document on the Internet is having a race to the bottom, and people are falsely equating a quick start with a good framework. I have never picked a technology to work with because I could have a blog written in 10 minutes.
- vertex-four 12y ago> I'd venture to say NoSQL has gained it's popularity because it's Magic No, it's because to a first approximation, they aren't magic. Writing your queries in a more imperative style means that most developers can more easily reason about them, doubly so once they get more complicated. You don't have to rely on generators or magic frameworks to generate SQL for you with a good document database. All your queries can quite reasonably be right there, and things like "get the document with the ID of foo" are already just one method call away. Of course, most NoSQL databases have tradeoffs that are not obvious, while I believe all SQL databases stick to ACID. That's probably more of the problem than what query language is used.
- woah 12y agoDo you know of any good ACID NoSQL?
- eddieroger 12y ago> You don't have to rely on generators or magic frameworks to generate SQL for you with a good document database. All your queries can quite reasonably be right there, and things like "get the document with the ID of foo" are already just one method call away. I guess the scenario I was thinking of was if somewhere were to sit down and learn Rails with no knowledge of programming, they don't also have to learn SQL in addition to Ruby and Rails and ERB and everything else bundled in because ActiveRecord takes care of the database for you. If they were learning Sinatra, as a comparison, they'd have to also learn some ORM-layer tool, which potentially abstracts the SQL, but may not. But if they do Mongoid or the like, then the database returns to Magic status. Change you schema? No problem.
- tjr 12y agoAfter studying SQL at MIT with Greenspun, I came to see it not only as a nice language, but beautiful and expressive in a way similar to Lisp. I personally would rather write database applications mostly in SQL than pull as much as possible up into the scripting language through ORMs.
- bitL 12y agoI don't like SQL either. Disclaimer: I wrote my own SQL server some time ago, then went on to help to prepare a NoSQL architecture at one of the largest tech companies. SQL is a weird hack of relational algebra with a huge variation of syntax between different providers. Also, it's pretty inflexible if you need to change schema, you need to solve crazy transactional problems if you write your own server, not mentioning the problems arising from clustering. Try modeling trees in SQL - if your data is mostly static, you will be saved by nested sets or their fractal successor, however most SQL servers mislead you in thinking their tree-functions are anywhere as performing. If you think you don't need trees, imagine modeling categories or an org chart in SQL, and making sure decisions at one level propagate correctly to all levels underneath and can be overriden only with correct permissions, and this must be a part of each query.
- noblethrasher 12y agoDon't CTEs solve the problem of representing trees in a DB (modulo depth limit)? I have used them to construct schemas that represented org charts and other kind of trees. Right now I'm working on a project that will probably use an RDBMS to store the parse tree of a compilation.
- bitL 12y agoTry to benchmark your CTE query against a nested-sets implementation as described e.g. here: http://www.evanpetersen.com/item/nested-sets.html http://www.evanpetersen.com/item/nested-sets.html
- mason55 12y agoI can't speak for any other server but Postgres recurvise CTEs should be reasonably performant
- bitL 12y agoI am not sure what is the situation today; in 2005 when I wrote nested-sets implementation for TSQL and benchmarked it against Oracle's START-CONNECT extension it was a few milliseconds vs seconds for large hierarchies. Of course, nested sets need an occasional rebuild of the whole hierarchy so they are only useful for mostly static hierarchical data. If you have only a couple of levels, you are probably fine with what your SQL server offers; for huge amount of hierarchical data you need to use something smarter.
- tbrownaw 12y agoI mostly like SQL. The biggest annoyance for what I do, is that I don't have a clean way to edit a select list; that is "select foo., ... from (...) foo" works to add* columns, but I can't remove or rename or replace existing columns without listing all of them. The second biggest annoyance, is the hoops I have to jump thru to join a parent to two (or more) sets of child rows without either multiplying everything or dropping data.
- crazygringo 12y agoI know SQL inside and out, and I hate it for anything put trivial queries that involve a single table and a single WHERE clause. Every time I write a query of reasonable complexity, I have to figure out what I want the database to do (in an imperative sense -- read from this table first, then look up this in that table, then do this other thing with this other table for each row), and then figure out how to translate this into a declarative SQL statement, just so the database's query interpreter can turn that back into an imperative program it can run. The whole thing is just a huge unnecessary round-trip of thinking, and SQL is limited in that there are many simple things it can't do, and you never really have any guarantee that the database's query analyzer will execute your query imperatively in the manner you hope for (which can lead to terrible performance problems, and hacks like index "hints" still don't always solve it). I'd MUCH rather be able to program against the database imperatively, directly. So despite using SQL day-in, day-out, I totally agree that it is weird, obtuse, and a kind of "non-language" (in the sense that any query is limited to a single declarative statement, with a host of limitations, instead of an imperative programming language that allows you to write whole programs of execution).
- nwenzel 12y agoGood points all the way around. Do you think SQL's obtuseness comes from it's ability to do more than (perhaps) it should? Maybe it really only should be used for simple queries with simple where clauses. But because it can be used for more, we choose to do so. It's based on relational algebra making it fairly predictable (query optimizer aside which is completely unpredictable). It operates on relations and returns relations. "Select * from MyRelation;" returns a relation. So we could also write "select * from (select * from MyRelation);" and get the same result. You can continue that nested select as many times as you like and with as much complexity as you like and it would still work. Though undoubtedly it gets difficult to follow. Adding to the obtuseness, SQL is not typed. So for "select * from MyRelation where SomeField = 1;" you could replace the 1 with another select statement, which would return a relation. But as long as that relation had a single column and a single row, it would be interpreted as a single value and successfully replace the 1. Also adding to the obtuseness as you get more complex is that it variable scope for table aliases can be difficult to follow. Table alias names can be used across various select, where, from and other clauses to link them together through a common filter but it happens in the single SQL statement. I think it's that common issue of reducing a "program" into a so gel statement that causes the issues I run into. At times, I feel that I am writing what basically amounts to a stored procedure in a single statement. That's when I start getting mad at SQL. Though, it's really my own choices that made it a problem.