Relational databases are brilliant, but SQL is terrible and I don't understand how it hasn't gone the way of COBOL yet. For a start, the bizarrely English-oriented structure forces a completely different ordering of the code than what comes out, and the syntax appears to completely ignore the underlying tabular concepts. Not to mention that IMO joining on foreign keys shouldn't be a thing--the database should be able to figure out what you want when you write `order.customer.address.taxRegion.rate` without specifying any joins at all. Even if you use an ORM you still have to understand the code it will emit to avoid building bad queries. There's just so much awkwardness in trying to write a SQL query that I can definitely see why people would avoid it.
Re: Verbose/obtuse. There are different ways to use SQL. For example IMHO if you use the unnecessary "<LEFT/RIGHT> JOIN" syntax then statements often come across as pretty unreadable, whereas you can generally achieve the same with alternate and more readable syntax.
As opposed to SELECT * FROM a, b WHERE a.id = b.id? Looks ok on paper, but in exchange for brevity you end up demolishing your readability on complex queries, since you can’t easily identify filters (as opposed to join conditions). Most style guides, including my team’s, consider explicit JOINs mandatory, and there’s a good reason for doing so.
Admittedly my work is very heavy on data manipulation; maybe complex queries are less of a concern for other use cases.
I'm not often SQL wrangling these days, so you are probably more aware than I am on intricacies, but often you can wrap subqueries like:
SELECT stuff FROM tables WHERE conditions AND table.field IN (
SELECT othertable.otherfield FROM othertables WHERE more_conditions...)
);
IMHO this reads far more easily and both depth and order of execution are clear. I guess at this point in my career (20 years in) my bias is toward code I can walk away from for years and come back to easily.
Clarity is better than cleverness. - Eric S. Raymond, The Art of Unix Programming (2003)
> Relational databases are brilliant, but SQL is terrible and I don't understand how it hasn't gone the way of COBOL yet.
Having written extensive amounts of SQL and greater than zero lines of COBOL (f. you, Lawson), this really couldn't be further from the truth.
> For a start, the bizarrely English-oriented structure forces a completely different ordering of the code than what comes out, and the syntax appears to completely ignore the underlying tabular concepts.
Every language has keywords; I'm not sure how you expect to write code that doesn't involve English in some form. Is the concept of `SELECT this_data FROM this_source WHERE true_conditions ORDER BY columns` really that convoluted? Sure, it definitely gets more complicated, but is there really a significantly better way to do it that doesn't involve taking away low-level control from the programmer, in exchange for something sexier and easier to pick up?
> Not to mention that IMO joining on foreign keys shouldn't be a thing--the database should be able to figure out what you want when you write `order.customer.address.taxRegion.rate`
Why can't you have a key-value storage engine that doesn't involve using keys to look up values?! (Is that really your question?) Okay, just as an example, what if you have more than one address for a customer? You need a way to tell the engine which address(es) you want.
> There's just so much awkwardness in trying to write a SQL query that I can definitely see why people would avoid it.
While I don't claim that SQL is easy (or always intuitive), saying that it is "terrible" for the above reasons is like saying that C is terrible because it's not Pythonic.
reply