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

81–90 of 260 posts

Re: A Critique of SQL, 40 Years Later

#81

Post by sivers »

SoftTalker wrote: post #56
webmobdev wrote: post #12

Earlier quoted context omitted.

Agreed - my experience with non-SQL tech (ORMs) with Django and ASP.net MVC made me really appreciate SQL so much. Most of the time I felt everything would be so much easier using raw SQL instead of dealing with model objects. It also felt like in their quest to replace SQL and make things "simpler" they were recreating some db features again.

Yep. That's why I write most of my logic in stored procedures. Working with tables and queries is so much easier in PL/pgsql than dealing with ORMs and their leaky abstractions. My application code just calls stored procedures. It's unaware of the tables and underlying data model.

Cool! I do this, but I haven't seen anyone else do it. Is any of your code public?

I wrote about it at https://sive.rs/pg

and posted my SQL shopping cart at https://github.com/sivers/store

Please contact me if you'd like to share tips: https://sive.rs/contact

Re: A Critique of SQL, 40 Years Later

#82

Post by cbm-vic-20 »

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

I wish the "truly awful" stuff I come up with was 0.1% as successful as SQL.

Re: A Critique of SQL, 40 Years Later

#83

Post by mordechai9000 »

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…

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

Re: A Critique of SQL, 40 Years Later

#84

Post by crazygringo »

dspillett wrote: post #46
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…

> but it's the twin meanings of NULL that really kills me NULL only has one meaning: NULL. This is roughly analogous to unknown. The one that his a lot of people is WHERE NOT IN ( ) where contains a NULL. Because NOT IN unrolls to “ AND AND … AND ” any NULL values in the set makes one predicate NULL which makes the whole expression NULL even if one or more of the other values match. > I have no way of knowing if that…

> NULL only has one meaning: NULL. This is roughly analogous to unknown.

Which is what I'm arguing against, because it's used in two unrelated ways in SQL -- as a data value, and to express no matching row found in a JOIN. So no matter how it's defined formally, in practice it has two meanings that have nothing whatsoever to do with each other. One is a value and can be stored, the other says 'not found' and results only from expressions and isn't for storage.

> but you can infer the difference

Yes, as I said there are lots of workarounds. But they're still workarounds.

> This is one of the reasons some purists argue against NULL existing in SQL at all

Sure, but the reality is that NULL is used in a practical sense to mean "no data entered". It's so ridiculously common that only some columns have no data for a particular row, and so e.g. you need the 'time_finished' column to either have a valid date, or it's NULL if the activity hasn't finished yet. The alternative is to have an additional boolean column 'is_finished' and for 'time_finished' to be arbitrarily '1970-01-01T00:00:00Z' whenever is_finished is false, which is clunky and redundant.

Purists can argue what they want, but NULL is so ridiculously useful as a stored data value it doesn't really matter.

Re: A Critique of SQL, 40 Years Later

#85

Post by crazygringo »

SoftTalker wrote: post #54
dspillett wrote: post #46

Earlier quoted context omitted.

> but it's the twin meanings of NULL that really kills me NULL only has one meaning: NULL. This is roughly analogous to unknown. The one that his a lot of people is WHERE NOT IN ( ) where contains a NULL. Because NOT IN unrolls to “ AND AND … AND ” any NULL values in the set makes one predicate NULL which makes the whole expression NULL even if one or more of the other values match. > I have no way of knowing if that…

If you have a business need to represent "empty" or "n/a" or "declined to answer" or something like that, use a specific value for that. NULL does not mean anything. Or, it means nothing. It's just NULL. Once I got that into my head, SQL became less frustrating.

You can't without defining yet another field, usually. It's really annoying to double the number of columns so that [time_started, time_finished] becomes [time_started_exists, time_started, time_finished_exists, time_finished].

NULL values makes business logic far more compact and intuitive. For enumerated values in fields it's easy enough to define another value in the same field to mean 'unknown' or 'not entered', but you can't do that for strings, numbers, datetimes, etc. -- you have to throw in a bunch of unwieldy additional boolean fields instead.

Re: A Critique of SQL, 40 Years Later

#86

Post by janci »

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…

This is why JS has both null and undefined.

Re: A Critique of SQL, 40 Years Later

#87

Post by adrian_b »

warrenm wrote: post #26

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

The title did not refer to years passed since the introduction of SQL, but to years passed since the publication of the paper with the title "A Critique of the SQL Database Language", i.e. from 1984-11.

So the title is correct.

Re: A Critique of SQL, 40 Years Later

#88

Post by spullara »

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…

It makes way more sense honestly. It also looks a lot like a functional pipeline if you set it up that way.

Re: A Critique of SQL, 40 Years Later

#90

Post by janci »

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…

Also you should select y.a to know wether the y-record exists.
Post reply on HN