Detailed Comparison of SQL (MySQL) vs. NoSQL (MongoDB) vs. Graph Query (Neo4j) | Data-structure, Queries, Data types, Functions

In this article, we will compare RDBMS, NoSQL DB & Graph DB. This is a development & implementation level comparison which will highlight differences on below criteria.

  • Data structures & relationships
  • Queries
  • Data types like Text, Integer, Date etc.
  • Special operations like sort, limit
  • Aggregate functions like max

This article might not help in choosing among databases. Choosing among these databases is based on your requirements about data model, data usage, ACID properties etc.

Example Data for comparison

We will take example of storing candidates information for job interviews. We will use MySQL (RDBMS), MongoDB (NoSQL or Document database) & Neo4J (Graph database) & see how this data can be stored & retrieved from these databases.

  • Candidate – Name (String), Years Of Experience (Number)
  • Contact Details (One to One) – Mobile Number (String), Email id (String)
  • Job History (One to Many) – List of all present & past jobs with company name (String), start(Date) & end dates (Date).



Create new database

MySQL

MongoDB

Neo4j

Create Data Structure

MySQL

  • Data Structure: Table
  • Each Data Entry: Row
  • Schema: Need to provide table structure upfront including columns names, data types, relationships etc.

Below is a logical way to represent our example using 3 tables.

MongoDB

  • Data Structure: Collection
  • Each Data Entry: Document (format like JSON)
  • Schema: Only need name of collections upfront. No need to provide structure of document within collection upfront. MongoDB does provide way for schema validation.

We have 2 ways to represent our example data in MongoDB as given below.

1) Embedded: All attributes in single collection

2) Normalized/Referential: Three logical collections with references to each other.

Neo4j

  • Data Structure: Graph
  • Each Data Entry: Nodes & Relationships
  • Schema: No need to provide anything upfront.

If everything is node, then how would you know which node represents what data? The answer is “Label“. All nodes with same label can be considered as same type of data. Like in our case CANDIDATE, CONTACT_DETAILS, JOB_HISTORY can be labels to tag the nodes of respective data. So Label can be approximately (but not exactly) analogous to table in RDBMS or collection in NoSQL.

Node with labels can be directly created whenever you get first entry of data.



Insert data

MySQL

MongoDB

1) Embedded document with all data into single JSON document.

2) Referential form of storage where data is stored in 3 documents which refer to each other using ids.

Neo4j



Query data, Search/select/find

MySQL

MongoDB

1) Embedded: No need of joins as all data is available in single document. Single query & single collection scan to get all data. pretty()  function displays output JSON in pretty printed & readable format.

2) Referential: Need to use lookup to aggregate (similar to join in SQL) multiple documents & return as a single document.

Neo4j

Below query is executed in Neo4J Browser.

This is a graphical representation of results which is rendered by Neo4J Browser.



Modify/Alter Data Structure

MySQL

MongoDB

MongoDB doesn’t have structure as it is schemaless. So no need of any alter. We can directly insert new row with any additional field.

Neo4j

Neo4j data are nodes so all nods are free to have any attributes. So no need of any alter & we can directly add new node with new fields.



Sorting & limiting results (Top results)

In earlier section, we inserted new data with YEARS_OF_EXP as 25 which was top max YEARS_OF_EXP.

MySQL

MongoDB

Reference for projection.

Neo4j



Aggregate function (Max)

MySQL

MongoDB

Neo4j



References

  • MongoDB Community Server Install
    • While installation on Windows 10, it gives error “Invalid domain/user/password”. Use domain as “.” & your Windows machine’s user name & password & then it works. Then keep continuing with default options on all installation screens.
    • CLI – Run C:\Program Files\MongoDB\Server\4.2\bin\mongo.exe
  • Neo4j Desktop Installation 
    • Download Neo4j desktop which is for local development setup. It asks for few details like name, email etc. & assigns a free key which can be used while installation.
    • Neo4j query language is called Cypher.



One Reply to “Detailed Comparison of SQL (MySQL) vs. NoSQL (MongoDB) vs. Graph Query (Neo4j) | Data-structure, Queries, Data types, Functions”

  1. nice article. expecting more articles like this. when to use what type db with more depth about volume of data, TPS etc.

Leave a Reply

Your email address will not be published.