Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

A Critique of SQL, 40 Years Later

carlineng.com

Post reply on HN
Print view View original

131–140 of 260 posts

Re: A Critique of SQL, 40 Years Later

#131

Post by cryptonector »

asah wrote: post #120
tabtab wrote: post #43

Many of those complaints seem theoretical. I like to focus on practical concerns. The biggest problem I see is that the SQL language has grown too complex. It's related to the "Lack of Orthogonality" problem mentioned in the article, but I see different solutions. SQL is not based on combinations of simpler concepts, but hard-coded keywords. But how to orthogonize (factor) it gets into philosophical differences. My f…

I agree!!! this is my pet peeve as well, and I sometimes fantasize about ripping into PostgreSQL and adding column-minus. One wrinkle: computed columns would interfere with query optimization. That said (and here I speak heresy) there are times when syntactic convenience trumps performance.

The syntax could be "* (minus, columns, here)".

Re: A Critique of SQL, 40 Years Later

#133

Post by tremon »

emaginniss wrote: post #35
toto444 wrote: post #14

Earlier quoted context omitted.

Every time SQL is mentioned on HN someone comes to complain about FROM coming after SELECT. I use SQL every day and not a single time have I found reason to complain about it. Can you give a bit more detail about what's wrong with it being like it is ? EDIT : thanks all for your reply. I now understand that it is an IDE related thing not something fundamental to the language.

Everyone else is mentioning it from an IDE perspective, but let's also think about logically from a language perspective. When you start a FROM clause and add some JOINs, a few WHERE conditions and maybe GROUP BY, you are building a virtual view of a series of tables, columns, and aggregations. You could even define this data set as an ephemeral table. What you do with that data set afterwards might vary depending on…

You could even define this data set as an ephemeral table

Exactly! This is where SQL hurts me the most: not being able to store (partial) query expressions in variables for later reuse. The only way to do this is by creating explicit views (requires DDL permissions) or executing the partial query into a temporary table (which is woefully inefficient for obvious reasons).

Re: A Critique of SQL, 40 Years Later

#134

Post by jjav »

noirscape wrote: post #53
spprashant wrote: post #27

Earlier quoted context omitted.

If you exploring a set of tables you have never touched before, it really neat if you could just type in: FROM tablename t SELECT t. and some form of autocomplete mechanism, either prefills all the column names from table "t" or suggests the list of columns and/or types associated with it. This is much better than having to: 1. Run a SELECT with LIMIT statement just to get an idea of the layout. 2. Point and click th…

DESCRIBE TABLE is a command that pretty much does exactly this (explain what a table contains) and it's a part of MySQL. If you use PostgreSQL then you can use \d instead. I'm sure the other RDBMSes have their own equivalent (except for maybe SQLite but if you're using SQLite outside of toy environments or hobby projects, you're doing it wrong).

> except for maybe SQLite

.schema

> if you're using SQLite outside of toy environments or hobby projects, you're doing it wrong

That's an uninformed statement. SQLite is extremely solid production quality code. Of course it's not the universally applicable database solution, nothing is. Sometimes you need Cassandra or its kind, often MySQL|Postgres, other times SQLite is the correct answer.

Re: A Critique of SQL, 40 Years Later

#135

Post by crazygringo »

rrrrrrrrrrrryan wrote: post #116
crazygringo wrote: post #37

I've written a bajillion queries and have tons of nitpicks, but it's the twin meanings of NULL that really kills me. NULL can be the value of a field in a record, but it is also used to indicate the lack of a record in a JOIN. If I run: SELECT x.a, y.b FROM x LEFT JOIN y on x.a = y.a and I get back [5, NULL] I have no way of knowing if that means there's a record [5, NULL] in table y, or if there's no record in table…

Why are you joining on a nullable column in the first place? If your database is well designed, joining on a nullable column should be a relatively exotic use case, and can be handled by writing a tiny bit more code to check for NULLs before performing your join.

It's not.

Assume y.a is the non-nullable primary key, while y.b is the column that may be null.

But in this example there might not be a row where y.a = 5. Or there might be. But you can't tell.

Re: A Critique of SQL, 40 Years Later

#136

Post by mamcx »

tadfisher wrote: post #79
jolmg wrote: post #75

Earlier quoted context omitted.

Having SELECT come first makes sense to me because it's the only part of the statement that's required. FROM and everything else is optional. Also when reading a statement, you're mostly interested in what the returned fields are rather details like where they came from or how they're ordered. It kind of makes sense to put it at the start. Maybe other syntax forms have their benefits, specially when writing, but I do…

SELECT itself should be optional. Languages with expressions are fairly intuitive, e.g. "int x = foo.bar;" where "foo.bar" is equivalent to the "SELECT bar FROM foo;" SQL statement. I don't breathe SQL every day, so I'm struggling to come up with a case where removing SELECT results in parsing ambiguity.

> I'm struggling to come up with a case where removing SELECT results in parsing ambiguity.

Oh! Is super-ambiguous! Make the parser and enjoy it!

Lets make this more concrete:

     city SELECT id
     city ORDER id
     city id
You could then "favor" projection as the most important than the others. Ok, so:

     city city city
Which is the table, or the field?

Re: A Critique of SQL, 40 Years Later

#137

Post by acdha »

naranha wrote: post #93

My problem with SQL is that it's 99% Excel and 1% A database query language. And the boundaries between are often not well defined. Why even have a SUM function, when it is not accelerated by an index, that makes using it automatically non-scalable. I think there should be a core language, perhaps similar to SQL perhaps not, as an interface to pure DB functionality, all the other 99% could be done in Excel and while…

> Why even have a SUM function, when it is not accelerated by an index, that makes using it automatically non-scalable.

Because it’s useful and that last part isn’t true? Databases do more than indexes and you have trade offs regarding the impact of adding too many indexes.

It's also not only not a problem for scalability but in reality an important way to _improve_ scalability. Consider a simple example where we do "SELECT SUM(price) FROM orders GROUP BY customer_id". Removing SUM() from the language would massively increase the amount of data which needs to be processed into the response — which makes anything else you're doing like sorting harder – and it prevents various optimizations the database engine might make. For example, on AWS RDS Aurora the database pushes all of that down to the storage nodes so each storage node will return the sums for the records which are on that node and the main node can sum those intermediate values without needing to transfer a single source row over the network. Since SUM() is part of the language and well-defined, that's safe for one database team to implement without the risk of their results not matching a competing database.

Other things to think about are computed views and stored procedures: in both cases, there are situations where these are dramatic performance improvements or otherwise desirable and having a richer language means that those constructs can solve more problems.

Re: A Critique of SQL, 40 Years Later

#138

Post by tehlike »

exabrial wrote: post #10

The only thing that affects my daily life with SQL is that FROM should be before the SELECT keyword. This would _greatly_ improve type-ahead support in SQL IDEs. Nothing is perfect, but that is really the main beef. Another commentor already nailed having a LIMIT WITH ERROR clause to be specified on UPDATE,DELETE statements and explicitly throw an error otherwise. SQL is on of my favorite tools to use and I don't see…

Check this out https://github.com/prql/prql

Re: A Critique of SQL, 40 Years Later

#139

Post by tremon »

vbezhenar wrote: post #130
andy81 wrote: post #117

Earlier quoted context omitted.

Of course. You've got a list of countries and are pulling each country's national flower, national bird, largest port city etc. Without outer joins, Liechtenstein with no ports doesn't show in the list at all. Sad news for people who want to know all countries, or Liechtenstein's national bird (eagle).

You’re not obliged to pull everything in one request. You can issue several requests.

Sure. You're also not obligated to include a WHERE-clause in the query you send to your database. You can do the filtering in the application.

Re: A Critique of SQL, 40 Years Later

#140

Post by tremon »

crazygringo wrote: post #135
rrrrrrrrrrrryan wrote: post #116

Earlier quoted context omitted.

Why are you joining on a nullable column in the first place? If your database is well designed, joining on a nullable column should be a relatively exotic use case, and can be handled by writing a tiny bit more code to check for NULLs before performing your join.

It's not. Assume y.a is the non-nullable primary key, while y.b is the column that may be null. But in this example there might not be a row where y.a = 5. Or there might be. But you can't tell.

But in those cases, you can always tell by including the non-nullable key in your result set. It's already being evaluated, so it's virtually free (only adds to the network transport size).
Post reply on HN