Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why you should learn SQL

executeprogram.com

Post reply on HN
Print view View original

101–110 of 137 posts

Re: Why you should learn SQL

#101

Post by sa46 »

BulgarianIdiot wrote: post #64
cratermoon wrote: post #23

I've spent most of my career hoping that something - anything that's better than SQL will come along and replace it. It's like FORTRAN, except FORTRAN has had the decency to stay in use where it's really the best choice. But SQL is out there, like Clippy. "Hey, I see you're collecting some data. SELECT TRUE FROM HELP WHERE COLLECTING_DATA IS TRUE

> I've spent most of my career hoping that something - anything that's better than SQL will come along and replace it. So, if we put aside Clippy jokes and so on, what's your problem with SQL exactly and what does "better than SQL" mean to you? Because what I saw in the last couple of decades is the NoSQL movement lose air, and half the products representing it adding some sort of SQL dialect support, which if you th…

There's a lot wrong with SQL. Chris Date [1] wrote several books comparing SQL to a more pure implementation relational algebra, Tutorial D. In Date's words:

> SQL is incapable of providing the kind of firm foundation we need for future growth and development. Instead, it’s the relational model that has to provide that foundation. [...] We see SQL as a kind of database COBOL, and we would like to see some other language become available as a better alternative to it.

Summarizing Date's points:

- Real relations don't contain duplicate tuples but SQL tables allow duplicate rows.

- Tuples and therefore relations don't ever contain nulls. Personally, I have trouble understanding how to support this limitation given LEFT JOIN.

- Domain types only provide type aliases not a new type. For example, you can compare or join on different domain types if the base type is the same. Date uses the example of `p.weight = sp.qty` to show a comparison on domain types that shouldn't be allowed.

- SQL has many ways to express the same query. His example shows at least 12 ways to answer "Get part numbers for parts that either are screws or are supplied by supplier S1, or both."

- SELECT DISTINCT should have been the default instead of SELECT ALL.

My personal gripes:

- SQL is quite chatty, so much so that you can omit words in some incantations.

- It's hard to dynamically build queries. It composes poorly for anything dynamic, even simple things like ordering by a column name.

[1]: https://en.wikipedia.org/wiki/Christopher_J._Date

Re: Why you should learn SQL

#102

Post by smitty1e »

frogcoder wrote: post #54

I used to love ORM, I used it everywhere. Writing another language in the language I am coding is wrong. ORM simplifies my programs. No, ORM does not simplify coding! It's a big complex adapter which does not fit many cases. RDBM itself is complex enough, let's put another complex abstraction above it so we can forget about the tables and columns and joins and foreign keys. Complexity added upon another complexity do…

One nifty use-case for ORMs is decoupling the logic from the data.

I can prototype in Python/SQLAlchemy/SQLite and deploy to Python/SQLalchemy/PostGreSQL via a pipeline with confidence that I'm actually managing the complexity.

Cracking open some legacy code with vast swaths of embedded SQL is a source of much weeping and gnashing of teeth when the time comes for maintenance.

Re: Why you should learn SQL

#103

Post by polynox »

SilverRed wrote: post #44
nesarkvechnep wrote: post #40

Earlier quoted context omitted.

Maybe create foreign keys and indexes in your Rails migrations

I had a crazy case where after adding indexes to things, queries suddenly become much much slower. I had to look over the plan and realized that removing these indexes made queries fast again. Turns out you can't just take for granted that an index will speed things up and its something you actually have to test and record before/after results. I'm still slightly at a loss as to what the issue was but the best I can…

Indices work best when the number of matching entries is low. There is a metric called index selectivity which is the number of distinct values of the indexed columns divided by the total number of records. For a boolean value, this would be 2/N records, effectively the worst possible index. For a perfect index, it would be 1 (every row has a unique entry in the index). It could be possible for the query planner to get the answer wrong for which index to use if it happens to be wrong about the selectivity of your particular query, because the selectivity must be approximated.

See for example PostgreSQL's (a fantastic database) documentation on these approximations and imagine a number of ways it could fail [1].

> Assuming a linear distribution of values inside each bucket, [...]

> This amounts to assuming that the fraction of the column that is not any of the MCVs is evenly distributed among all the other distinct values.

> Using some rather cheesy assumptions about the frequency of different characters

[1] https://www.postgresql.org/docs/current/row-estimation-examp...

Re: Why you should learn SQL

#104

Post by goto11 »

BiteCode_dev wrote: post #80
BulgarianIdiot wrote: post #64

Earlier quoted context omitted.

> I've spent most of my career hoping that something - anything that's better than SQL will come along and replace it. So, if we put aside Clippy jokes and so on, what's your problem with SQL exactly and what does "better than SQL" mean to you? Because what I saw in the last couple of decades is the NoSQL movement lose air, and half the products representing it adding some sort of SQL dialect support, which if you th…

It's super verbose, doesn't compose well with the host language primitives, is hard to introspect, full of gotchas and very low level.

In what way is SQL "low level"? I would consider it a very high level DSL.

Agree about the verbosity. The syntax is quite clunky.

Re: Why you should learn SQL

#105

Post by goto11 »

urthor wrote: post #26
cratermoon wrote: post #23

I've spent most of my career hoping that something - anything that's better than SQL will come along and replace it. It's like FORTRAN, except FORTRAN has had the decency to stay in use where it's really the best choice. But SQL is out there, like Clippy. "Hey, I see you're collecting some data. SELECT TRUE FROM HELP WHERE COLLECTING_DATA IS TRUE

Nobody else has invented any other declarative language it seems. Come on aspiring post-docs, do your thing and launch a new programming language. If MIT can launch Julia someone can launch a language that's not SQL.

There are multiple alternatives. Problem is SQL is "good enough" and ubiquitous and deeply entrenched. It will be extremely difficult for any alternative language to unseat SQL.

Re: Why you should learn SQL

#106

Post by BulgarianIdiot »

sa46 wrote: post #101
BulgarianIdiot wrote: post #64

Earlier quoted context omitted.

> I've spent most of my career hoping that something - anything that's better than SQL will come along and replace it. So, if we put aside Clippy jokes and so on, what's your problem with SQL exactly and what does "better than SQL" mean to you? Because what I saw in the last couple of decades is the NoSQL movement lose air, and half the products representing it adding some sort of SQL dialect support, which if you th…

There's a lot wrong with SQL. Chris Date [1] wrote several books comparing SQL to a more pure implementation relational algebra, Tutorial D. In Date's words: > SQL is incapable of providing the kind of firm foundation we need for future growth and development. Instead, it’s the relational model that has to provide that foundation. [...] We see SQL as a kind of database COBOL, and we would like to see some other langu…

It seems a lot of those things you list as "wrong" come from lack of understanding of the reasons behind these choices, and born out of pure idealism in vacuum.

For example, allowing duplicate rows is irrelevant, because if you have a primary key, there are no duplicate rows. SQL doesn't require primary keys because relational algebra has no such concept as a "primary key". There are just keys. However without defining a primary key, enforcing unique rows would mean the database silently indexing the ENTIRE ROW'S CONTENTS, including potentially blobs and large text fields. This would obviously be nonsense.

Likewise, having SELECT DISTINCT be a default would mean a very expensive processing step in your query processing being a default. An expensive step that in fact doesn't matter, because the vast majority of queries don't produce duplicate results in practice. DISTINCT is optional because the need for it is exceptional, and its cost is high.

Even you don't buy the "have no NULL" argument. So I don't have to defend this. Real-world data is not perfectly "rectangular". Optional attributes are a thing. So having a primitive for it makes sense. Once again, SQL allows you to define a field as non-nullable, so complaining about it being there if you EXPLICITLY WANT IT is silly.

Regarding having many ways to express the same query: that's true for all languages. This is one of the biggest issues in optimizing compilers, canonicalizing expressions so patterns can be recognized. There's no way to do it at the source, so complaining SQL also does it, is like shouting at clouds.

Regarding the type system, type systems can always be better, but let's not forget type systems (typically) exist to eliminate mistakes, not to enable new features. The kind of mistake where you compare "weight and quantity" is not likely.

More subtle errors are possible, like comparing metric and imperial measures, but since SQL is often used in tandem with a system's language (Java, C++) or a script, all this domain logic is offloaded to them and their type system. Even if SQL had a detailed type system, therefore, most people wouldn't bother duplicating their detailed type definitions from Java to SQL or back.

The only remaining issue is SQL is chatty. Which is quite ironic given the procedural code for what SQL does would be several times the size of the SQL query. SQL is a high-level language, and it being explicit is fine. And few extra letters here and there for a keyword don't make or break a language. I prefer chatty over cryptic.

Re: Why you should learn SQL

#107

Post by goto11 »

urthor wrote: post #49
Akronymus wrote: post #12

Earlier quoted context omitted.

And even if you don't want to normalize, at least don't make the DB basically incomprehensible. Speaking from experience, a "abnormalized" db sucks. https://news.ycombinator.com/item?id=27842820

There's reasons for one wide table. The traditional SQL database uses row based data structures. When you shift to columnar data formats for the table, parquet or any columnar DB, the normalization rules which were developed for row based become extremely different. The brave new world now is in-memory DBs. All those 1970s rules, which make sense for row based tables stored on disk, don't apply to data in RAM. At all…

Shouldn't the normalization rules be exactly the same whether the data is stored row-oriented or column-oriented and on disk or memory?

Re: Why you should learn SQL

#108

Post by goto11 »

sa46 wrote: post #101
BulgarianIdiot wrote: post #64

Earlier quoted context omitted.

> I've spent most of my career hoping that something - anything that's better than SQL will come along and replace it. So, if we put aside Clippy jokes and so on, what's your problem with SQL exactly and what does "better than SQL" mean to you? Because what I saw in the last couple of decades is the NoSQL movement lose air, and half the products representing it adding some sort of SQL dialect support, which if you th…

There's a lot wrong with SQL. Chris Date [1] wrote several books comparing SQL to a more pure implementation relational algebra, Tutorial D. In Date's words: > SQL is incapable of providing the kind of firm foundation we need for future growth and development. Instead, it’s the relational model that has to provide that foundation. [...] We see SQL as a kind of database COBOL, and we would like to see some other langu…

Some of Dates gripes are valid, but some are quite questionable.

In SQL you enforce uniqueness of rows by defining a primary key. Dates concern seem to be that you can have duplicate rows if no primary key is defined, but why would you do that in the first place? A table should always have a primary key.

His gripe against nulls are controversial - E.F.Codd suggested nulls himself, so when Date claims null have no place in the relational model he is just stating a personal opinion. And while nulls are kind of weird, his suggested alternative is far worse.

His complain about comparing different types is valid IMHO. SQL is weakly typed and types are silently converted. I think it would be be much better if it was strongly typed and values has to be explicitly converted when e.g. comparing a string to a number. The issue about custom types is a consequence of SQL being weakly typed.

Re: Why you should learn SQL

#109

Post by pjmlp »

elmo2you wrote: post #93
pjmlp wrote: post #47

Earlier quoted context omitted.

Anyone that does work on the client that should stay on the server, sending wasted data across the wire has already lost it. To this day I keep writing stored procedures, no need to multiline strings. And for the rest just use either myBatis or jOOP, run away from Hibernate.

You are right, regarding the wastefulness. Additionally, there are extra risks involved when data needs to be transferred between a storage and compute facility. To a degree I get why architects these days like to separate everything into individual narrow and easier to manage/tuned services. But separating business logic from the data it runs on may equally be a fundamental mistake, when it comes to guarding integri…

There is also a security aspect to it, with stored procedures, even with stolen creditials there is very granular acess, that would have to be otherwise provided with tons of views.

I think it is a consequence of six month bootcamps and then be allowed to call themselves "engineers" (in countries that allow such things).

Re: Why you should learn SQL

#110

Post by BulgarianIdiot »

goto11 wrote: post #108
sa46 wrote: post #101

Earlier quoted context omitted.

There's a lot wrong with SQL. Chris Date [1] wrote several books comparing SQL to a more pure implementation relational algebra, Tutorial D. In Date's words: > SQL is incapable of providing the kind of firm foundation we need for future growth and development. Instead, it’s the relational model that has to provide that foundation. [...] We see SQL as a kind of database COBOL, and we would like to see some other langu…

Some of Dates gripes are valid, but some are quite questionable. In SQL you enforce uniqueness of rows by defining a primary key. Dates concern seem to be that you can have duplicate rows if no primary key is defined, but why would you do that in the first place? A table should always have a primary key. His gripe against nulls are controversial - E.F.Codd suggested nulls himself, so when Date claims null have no pla…

What's his alternative to null in a nutshell?
Post reply on HN