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

111–120 of 260 posts

Re: A Critique of SQL, 40 Years Later

#111

Post by andy81 »

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

Every RDMBS has this, including SQLite.

Re: A Critique of SQL, 40 Years Later

#112

Post by andy81 »

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.

A better option in many cases is to check the primary key.

e.g.

select questions.Id , questions.Text , answers.Id , answers.Text

from questions left join answers on answers.QuestionId = Questions.Id

answers.Id is non-nullable as a primary key, so if answers.Text is null but answers.Id is not null they've declined to answer.

Re: A Critique of SQL, 40 Years Later

#113

Post by jakespoon »

adrian_b wrote: post #87
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.

Title may be correct. First sentence is totally wrong. Why would I read further?

Re: A Critique of SQL, 40 Years Later

#114

Post by BeefWellington »

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…

I think this stems from a misunderstanding of the entire point of SQL. It isn't about looking at data in an individual table, it's about retrieving a Result Set for complex queries.

All the FROM-first examples I've ever seen are almost universally the simplest query in the world where autocomplete is not a large hurdle anyways because you aren't even bothering with table aliasing. As soon as you do anything even moderately complex (multiple joins, subqueries, calculations, etc.) the advantage of putting FROM first vanishes, and if you're adding a table alias or hard reference to the table you can already see what is in the SELECT list AND in many cases get autocomplete.

Re: A Critique of SQL, 40 Years Later

#115

Post by nhumrich »

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…

Perhaps excel is 99% SQL and not the other way around, especially since SQL is older.

Re: A Critique of SQL, 40 Years Later

#116

Post by rrrrrrrrrrrryan »

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.

Re: A Critique of SQL, 40 Years Later

#117

Post by andy81 »

Koshkin wrote: post #71
vbezhenar wrote: post #57

Earlier quoted context omitted.

You don’t do outer joins.

Don’t these exist for a reason?

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

Re: A Critique of SQL, 40 Years Later

#118

Post by cryptonector »

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…

You need one more output column. The specifics will vary by RDBMS because SQL isn't that standard. The most portable thing would be to do this:

  SELECT x.a, y.a IS NOT NULL, y.b
  FROM x
  LEFT JOIN y ON x.a = y.a;
assuming y.a is a NOT NULL column, or a PRIMARY KEY column, since PKs are supposed to be non-nullable.

In PG you could:

  SELECT x.a, y IS DISTINCT FROM NULL, y.b
  FROM x
  LEFT JOIN y ON x.a = y.a;

Re: A Critique of SQL, 40 Years Later

#119

Post by cryptonector »

cmrdporcupine wrote: post #7
Cockbrand wrote: post #4

While all the criticism is probably correct, and SQL definitely shows its age, it's still very much good enough for pretty much all its current applications. Also, SQL's basics are very easy to learn. These aspects make it very hard to imagine a language that might gain enough traction to actually replace SQL in a foreseeable future.

Many have tried. Most are hobby projects. None have had the full force of a real production ready DBMS behind them. And really, projects like this are fighting the general ignorance the industry as a whole has about what a relational database actually is. It's better since the NoSQL wave crested and we stopped hearing stupid shit like "my data isn't tabular and doesn't fit in a schema", but there's still a prepondera…

Any alternative to SQL has to transpile to SQL in order to gain traction.

Re: A Critique of SQL, 40 Years Later

#120

Post by asah »

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.

Post reply on HN