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

51–60 of 260 posts

Re: A Critique of SQL, 40 Years Later

#51

Post by strbean »

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.

COBOL also fits this description, down to the awkward attempts at matching natural language.

I think the staying power of SQL comes from its broader audience - programmers, analysts, and executives all use it. It's much easier to motivate programmers to learn a new, superior language than it is to motivate executives to learn a new technology that only gives ergonomic improvements.

Re: A Critique of SQL, 40 Years Later

#52

Post by gfody »

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 should not need to distinguish between empty and null because they mean the same thing. if you want to select records from x that aren't in y you should use an anti-join (where not exists, not in, etc.)

Re: A Critique of SQL, 40 Years Later

#53

Post by noirscape »

spprashant wrote: post #27
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.

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

Re: A Critique of SQL, 40 Years Later

#54

Post by SoftTalker »

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…

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.

Re: A Critique of SQL, 40 Years Later

#55

Post by dspillett »

bearjaws wrote: post #8

The only thing I would really blame solely on SQL is that UPDATE and DELETE statements don't require you to specify a limit. I have seen many times in my career where a rogue delete just truncates a table, a simple statement of intent (e.g. LIMIT 1) would tell the query planner that if it is about update/delete more than 1 row, it should error. In fact MySQL actually returns a warning if you do this. TRUNCATE clearly…

> TRUNCATE clearly states your intention to delete everything, but DELETE by default also deletes everything.

I think the other way around.

“DELETE Something” to me means get rid of the whole of Something. “TRUNCATE Something” (in general English, not SQL) to me means to make something shorter, which probably doesn't mean removing it completely.

Perhaps “EMPTY Table” would have been a better choice than “TRUNCATE Table”, but by the time it reached the standard¹ TRUNCATE had long since been picked and until then it was not the responsibility of the SQL standard: you only had DELETE and without a filter it makes sence that this deletes all.

----

[1] note that TRUNCATE was not in the SQL standards until SQL2008, long after it was common in various DMBSs as a minimally logged alternative to DELETE-without-filtering-clauses.

Re: A Critique of SQL, 40 Years Later

#56

Post by SoftTalker »

webmobdev wrote: post #12
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.

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.

Re: A Critique of SQL, 40 Years Later

#57

Post by vbezhenar »

onlyrealcuzzo wrote: post #49
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…

> This is one of the reasons some purists argue against NULL existing in SQL at all, rather than needing a family of NULL-a-likes. So then are there no optional values? What happens when OUTER JOINs don't match?

You don’t do outer joins.

Re: A Critique of SQL, 40 Years Later

#58

Post by dspillett »

onlyrealcuzzo wrote: post #49
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…

> This is one of the reasons some purists argue against NULL existing in SQL at all, rather than needing a family of NULL-a-likes. So then are there no optional values? What happens when OUTER JOINs don't match?

I assume NULL would still exist there, but there would be no explicit NULL values permitted in tuples (rows) that actually exist.

Or perhaps the purists would remove outer joins too, it isn't since University that I've read around the discussion, but given alternate syntax to do the same thing can sometimes be convoluted that might be a bad idea itself.

Re: A Critique of SQL, 40 Years Later

#59

Post by gibsonf1 »

warrenm wrote: post #30
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 si…

But thats just it, having many silos for connected information is just not the way we humans do it, nor necessary. A single conceptually indexed space/time hypergraph (as we humans do it) for any part of the world is all you need, scalable up to many billions of edges for that one part of the world.

Re: A Critique of SQL, 40 Years Later

#60

Post by thedataslinger »

gfody wrote: post #52
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 should not need to distinguish between empty and null because they mean the same thing. if you want to select records from x that aren't in y you should use an anti-join (where not exists, not in, etc.)

This is not true--especially when considering different SQL implementations (e.g. Oracle SQL versus Microsoft SQL). NULL and EMPTY handle the intersection of ontic versus epistemic claims.

EMPTY implies a known, 0-byte value whereas NULL can imply either an unknown value or the "unknowability" of a value (i.e. the in-existence of a value).

In practical terms, this would be like equating the statements "I don't know whether that dog has a name" (i.e. a NULL name) and "I do not know the name of that dog" (an EMPTY name). The former does not assert the existence of a proper noun to represent "that dog", whereas the latter implicitly asserts that there exists a proper noun which nominates "that dog".

Post reply on HN