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

101–110 of 260 posts

Re: A Critique of SQL, 40 Years Later

#101

Post by sedatk »

Koshkin wrote: post #70
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…

FWIW in LINQ, Select comes after From and Where.

Yeah, Select comes last, and it makes the most sense.

Re: A Critique of SQL, 40 Years Later

#102

Post by ianmcgowan »

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…

I'd check to see if y.a IS NULL in that situation. I'm sure there are cases where it matters, but most of the time for me the difference between "there's a row in y, but the value is NULL" and "there's no row in y, the value is NULL" is irrelevant. I can't think of a time when that distinction has been important, and I'm working on a project converting thousands of complex SQL queries.

The thing that really bugs me about NULL is the default assumptions - 99.9% of the time I want NULL in a string context to be the empty string and NULL in a numeric context to be 0, but I have to use ISNULL or COALESCE to get that. I wish it were the other way round where NULL is treated as '' or 0 by default, but I can do something special if I really want to test for NULL'ness.

Re: A Critique of SQL, 40 Years Later

#103

Post by gfody »

gibsonf1 wrote: post #17

I think the issue isn't SQL, but the table paradigm for storing data. Humans do not store data in separate tables that need joining, they store data in a fully connected graph (hyper-graph). Its about relationships and hierarchies - the graph allows incredibly fast hierarchical reasoning, as most things involve hierarchical reasoning. The relational table, and Sql by correlation, are terrible at the human approach to…

sql doesn't specify how data is stored, it's only a description of the data you want. how your data is connected or not at rest is up to the engine and how you leverage its engine-specific features.

Re: A Critique of SQL, 40 Years Later

#104

Post by toto444 »

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

It's actually useful to the person reading the code. It clearly defines where a statement starts, what it does and makes reading a query close to reading English. Show a SELECT FROM WHERE query to someone who does not know SQL and the person will understand it. It might be a bit harder if you remove the SELECT.

Re: A Critique of SQL, 40 Years Later

#105

Post by alphanumeric0 »

vendiddy wrote: post #5
dominotw wrote: post #3

SQL is having somewhat of a moment in the bigdata world, thanks in part to 'modern datastack' and new age datawarehouses like snowflake,bigquery. However there are a lot of pushback from 'traditional' dataengineers who were trained on spark/scala. Its bit of hardsell to go from a highly typed language to a free for all text based logic. I think the following is needed for sql to be finally accepted as 'serious' conte…

I think these are great suggestions. It seems like you're suggesting that someone could design a functional-style programming language that compiles to SQL. 2 & 3 are my biggest pain points. I can't just extract functions like I can with a regular programming language. Instead, SQL queries get increasingly complex with no great tools to manage that. For 3, products like https://materialize.com/ look interesting for b…

I'm not familiar with Ecto, dplyr, or DBT, but I would love an ML-like language to replace SQL. I'm imagining being able to pass a table (or any table-oriented data, like a sub-query) to functions that would type-check columns and would return table-oriented or scalar data. I'm not sure if this is actually possible in practice, but one can dream.

For instance, a "top 10" function that could be re-used on any table (apologies for my pseudo types and code):

  selectTop10 : Column -> Table -> Table
  selectTop10 orderByColumn table =
    SQL.selectAllFrom table
    |> orderDescBy orderByColumn
    |> limit 10

  limit : Int -> Table -> Table
  limit n rows =
    SQL.limitBy n rows

  orderDescBy : Column -> Table -> Table
  orderDescBy orderByColumn rows =
    SQL.orderBy [orderByColumn] SQL.Ordering.Desc rows

Re: A Critique of SQL, 40 Years Later

#106

Post by mordechai9000 »

ilkhan4 wrote: post #100
mordechai9000 wrote: post #83

Earlier quoted context omitted.

Maybe we should treat SQL like JavaScript, and use it as a compiler target instead of coding in it directly. /s

I mean, that's pretty much what ORMs do, right? Hasura et al too.

Yes, this is true. Although my experience with ORMs is that there is always a reason to use some kind of escape hatch to run raw SQL directly.

Re: A Critique of SQL, 40 Years Later

#107

Post by The_Colonel »

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).

It's kind of funny to see a claim that the most widely deployed database in the world is useful only for toy projects.

https://www.sqlite.org/mostdeployed.html

Re: A Critique of SQL, 40 Years Later

#108

Post by crazygringo »

ianmcgowan wrote: post #102
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…

I'd check to see if y.a IS NULL in that situation. I'm sure there are cases where it matters, but most of the time for me the difference between "there's a row in y, but the value is NULL" and "there's no row in y, the value is NULL" is irrelevant. I can't think of a time when that distinction has been important, and I'm working on a project converting thousands of complex SQL queries. The thing that really bugs me a…

Interesting, we must work with really different data/queries.

I've come up against the distinction lots of times (and yes have to retrieve additional fields in order to address it), while I don't ever want to confuse 0 with NULL. Tons of things are legitimately zero but crucially non-null, like an inventory count. (Empty strings, on the other hand, do seem much more interchangeable with NULL in probably the vast majority of contexts.)

Re: A Critique of SQL, 40 Years Later

#109

Post by jolmg »

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.

You mean the keyword; I meant the clause. "FROM foo" is optional to the syntax.

Re: A Critique of SQL, 40 Years Later

#110

Post by __float »

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).

This may be true, but you've missed the point that this is about aiding autocompletion while you're writing the query the first time.
Post reply on HN