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

21–30 of 260 posts

Re: A Critique of SQL, 40 Years Later

#21

Post by Hinrik »

toto444 wrote: post #14
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…

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.

Like the commenter alluded to, it allows accurately constrainted type-ahead. If a query starts with "SELECT FROM my_table " and expects one or more column names at that point, your IDE can already suggest the column names from my_table (and not from any other table).

Re: A Critique of SQL, 40 Years Later

#22

Post by dominotw »

ericHosick wrote: post #16
dominotw wrote: post #11

Earlier quoted context omitted.

right. I get lots of pushback for using sql at my clients. They just defeat me with one single point. "where are your unit tests" :D

> where are your unit tests I do unit testing in SQL, and something I'm working on and use extensively myself ( https://www.npmjs.com/package/sql-watch ) indirectly supports unit tests. There are also SQL testing frameworks available.

Yea I do that via dbt by setting up a mock data tables in database and using a macro to use those as sources/refs when run in test mode.

However what we are doing here isn't 'unit testing' its a black box integration testing. When I write equivalent code in scala, i just test the logic via unit tests,

eg: logic to filter out some orders that don't qualify, I extract method in scala code and just test that logic as part of development lifecycle. There is no dependency on a database.

Analog here is using selenium or some ui testing framework to test if button turns blue if order exceeds limit. Thats not unit testing.

Re: A Critique of SQL, 40 Years Later

#23

Post by mmcdermott »

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…

That's a fair point. This comment made me realize my own tendency to write "select * from..." so that I could supply the "from" before going back and replacing "*" with specific columns.

Re: A Critique of SQL, 40 Years Later

#24

Post by cogman10 »

toto444 wrote: post #14
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…

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.

Imagine a table `Foo` with the schema

`FooId, name, date, favorite_color, active`

Now, you want to pull the ID for the latest `foo` for a specific date, but you don't know any of the column names.

The modern workflow looks like this

You write

`SELECT * FROM Foo`

then you say, "Ok, now I can get autocomplete"

`SELECT FooId FROM Foo`

"Ok, now I can write the where clause"

`Select FooId From Foo WHERE date=?`

It becomes an exercise in moving the cursor around just to get the autocomplete going.

If you are really familiar with the schema, not a problem. But if you just remember a few details about it, then you are stuck in this weird back and forth cursor moving thing.

That's why it'd be more ergonomic to have something like

`From Foo Select FooId where date=?`

Because you never need to move your cursor and you could get all the autocomplete you need at the right times.

This becomes especially true when writing joining statements

    FROM Foo f
    JOIN Bar b ON f.FooId = b.FooId
    SELECT f.FooId
    WHERE b.active = 1

Re: A Critique of SQL, 40 Years Later

#25

Post by chrsig »

taeric wrote: post #18
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…

You could improve type ahead by just pouring effort into the IDE. Especially with the amount of resources we have nowadays. Easy enough to have basic typeahead on basically all possible columns when writing the select, and then you could use the columns as a filter on the tables during auto complete. In general, this isn't done. But I don't see any technical reason it can't be done.

This strikes me as solving the wrong problem. It's not as useful to be able to derive a table from selected columns -- the desire is to complete columns from a table. It's both more intuitive and helps the user more than once.

Re: A Critique of SQL, 40 Years Later

#26

Post by warrenm »

the title doesn't match the first statement, which states a math unfact:

>A Critique of SQL, 40 Years Later 08.11.2022

>The SQL language made its first appearance in 1974, as part of IBM’s System R database. It is now over 50 years later, and SQL is the de facto language for

1974 isn't 50 years ago yet :)

Re: A Critique of SQL, 40 Years Later

#27

Post by spprashant »

toto444 wrote: post #14
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…

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.

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 through the IDE treeview.

Honestly, I don't think it helps a whole lot beyond this functionality, but I can see why folks who are accustomed to thinking in functional pipelines (from -> select -> map -> filter -> collect) can prefer this way of querying.

I think PRQL is one attempt at building something this way.[1]

[1]. https://github.com/prql/prql

Re: A Critique of SQL, 40 Years Later

#28

Post by baq »

toto444 wrote: post #14
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…

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.

you seem to be too used to it to notice...

you start to type SELECT some, columns and can't get autocomplete until you add FROM afterwards, so you either type the query inside out (SELECT FROM table and go back to after SELECT) or just give up.

Re: A Critique of SQL, 40 Years Later

#29

Post by fipar »

cmrdporcupine wrote: post #2

"Despite the improvements listed above, in a 2014 interview, CJ Date said “we didn’t realize how truly awful SQL was or would turn out to be (note that it’s much worse now than it was then, though it was pretty bad right from the outset).” This quote leaves me wondering – if Date himself were to write an updated critique, what would it look like? My best guess is most of his criticism would revolve around further dep…

I agree with everything you're saying, though I'm assuming here:

> Date is definitely concerned about the departures from the relational model in SQL. But it's just about purism.

Perhaps you meant to say "But it's not just about purism"?

Re: A Critique of SQL, 40 Years Later

#30

Post by warrenm »

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…

I work extensively with Splunk which is dominantly based on noSQL underneath (MongoDB, among other, proprietary technologies)

I've also recently been [re]introduced to graph databases (which are highly similar to the pre-relational network database paradigm)

You can simulate graph relationships with an RDBMS or noSQL - but you shouldn't

You can simulate an RDBMS with a graph db or noSQL - but you shouldn't

You can simulate noSQL with graph and RDBMS tools - but, again, you shouldn't

They all have their place - and ca even work quite well together, if you use them as they're intended

Post reply on HN