Why do NoSQL databases not support joins? | Hacker News Reader

Why do NoSQL databases not support joins?

5 pointsadroitboss9 comments

NoSQL doesn't mean non-relational, it just means the absence of the Structured Query Language. So why isn't there a NoSQL relational database?

View on HN

>NoSQL doesn't mean non-relational

https://en.wikipedia.org/wiki/NoSQL

A NoSQL (originally referring to "non-SQL" or "non-relational")[1] database provides a mechanism for storage and retrieval of data that is modeled in means other than the tabular relations used in relational databases.

Thank you for pointing that out, but then the name NoSQL would be a misnomer. There must be multiple ways of storing relational data without including SQL. Someone above mentioned graph databases as a good example.

Poorly named things are bread and butter of software engineering.

> There must be multiple ways of storing relational data without including SQL

SQL makes no assumptions how relational data is stored exactly. It is just a language to talk to a RDBMS.

Usrbinbash makes a good point regarding the meaning of “relational”. Many people have come to think that “relational” refers to the relationships between entities in the database, when it really means that the database manages data in tabular form. I think this misunderstanding has become more ingrained because people typically normalize their data in RDBMSs, which necessitates greater use of joins. So, when folks talk about a “relational” workload, they often mean executing ACID-compliant joins over a normalized dataset. Because early NoSQL databases had traded transactional consistency for performance and scalability, there’s been an expectation that NoSQL databases can’t support what some think of as a relational workload.

To be fair, in the early days of NoSQL, it was necessary to use denormalization in one’s schema to achieve transactional integrity in the absence of joins. However, distributed databases have since overcome the major challenges of achieving consistency at scale. For example, http://Fauna.com is a serverless, document database which supports ACID transactions via the Calvin protocol. This means that you can execute transactionally consistent joins across collections (“tables”), and either normalize or denormalize your data model for all the same design reasons you might in a traditional RDBMS. The principle difference is that records in Fauna are JSON documents rather than rows in a table.

This is not to say that denormalization in modern schema design isn’t beneficial. However, support for ACID joins in a modern document database gives you more latitude in your schema design choices.

Full disclosure, I am a Product Manager at Fauna.

(1) Joins are a challenge to implement, particularly if CPU, network, and storage use matter. To get excellent results the database needs a query optimizer that can make a good guess of what query plan will be effective.

(2) There are many databases with other query languages that support joins, just a few are

https://www.arangodb.com/ https://www.w3.org/TR/sparql11-query/ https://docs.couchbase.com/dotnet-sdk/current/howtos/n1ql-qu... https://neo4j.com/developer/cypher/

all of the above support some combination of document and graph models. All of them can be used in a way very similar to a SQL database. For instance you can stuff arangodb with 'flat' JSON documents that work like tables and also create RDF graphs structured like a relational database and write SPARQL queries.

In fact there is a close mathematical relation between SQL and SPARQL and those other query languages. For instance it is possible to write a schema that maps a set of relational databases to an RDF graph, then compile a SPARQL query into a process that runs SQL queries on multiple databases. Better than that the system can rewrite the SQL queries to implement powerful and efficient inference

https://www.obitko.com/tutorials/ontologies-semantic-web/owl...

The Department of Defense tried this technology to answer the question of "Where does all the money go?" It didn't fail because the math didn't work or the tools sucked but more because of the intrinsic complexity of the problem and politics.

Thank you for the detailed explanation! I am not that much of a fan of SQL and much prefer writing things in a JSON like way. I wasn't aware of all of the above options and query languages that exist. I also wondered why document databases such as Firestore didn't support a join option. I'll explore some of these solutions.

Personally I am a big arangodb fan. It has never gotten as fashionable as some databases but I use it for many routine applications that I'd consider using a relational db for. For instance:

  * store the temperature and humidity readings from sensors,
  * pull out all the pictures from 'The Met' art gallery that have a near square aspect ratio and swipe right or left to select them to become part of something like

https://gen5.info/$/XQ*42RXF-TLY:$B.8/

The original Carlo Strozzi "NoSQL" database did exactly what you said.

It was an RDBMS.

It supported joins.

It just didn't use SQL syntax; it used a stream-operator paradigm.

Instead of "OUTER JOIN" you used "jointable -al"

https://en.wikipedia.org/wiki/Strozzi_NoSQL

Reply on news.ycombinator.com