SQL vs NoSQL – What is the difference?

Posted on by

SQL and NoSQL have been in the market for a while now, but what are the real differences between each other? Why should we use one or the other? What is the convenience of using either one or the other in your project?

Let’s find out!

First of all, what does NoSQL stands for?

Actually, NoSQL stands for Not Only SQL, which means that you will use SQL in NoSQL, is not like the meme that says ‘When they ask you if you know NoSQL in an interview and you don’t know SQL’ . Let’s take a deep breath and, taking this into account, dive into each one of them.

The main differences

Relational Databases

SQL uses relational databases, which means that the data is stored and accessed in a relational way.

For instance, let’s take an example from a MySQL table creation:

CREATE TABLE Customers (
    CustomerID int NOT NULL,
    CustomerName varchar(100) NOT NULL,
    CustomerSurname Varchar(100),
    PRIMARY KEY (CustomerID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

What we are saying here is that there will be two keys that could be accessed in this table, one is the CustomerID, which is created and depends on the table content, and the other is PersonID, which is a FOREIGN KEY, it comes and depends of another table.

As you may think, this makes a relationship between the two tables, which will mean that you could access the two of them at the same time and make reports with the data from both.

NoSQL does not use relational databases.

Advantages of using relational databases

So, what are the advantages of using relational databases? Here are some of them:

  • Integration: This means that if a column of your table uses a certain data type, all the data in the same column will use the same data type.
  • Atomicity: This means that the transaction cannot be partial, you either will have your query execute successfully with all the data you are asking for or the query won’t execute at all.
  • Accuracy: Primary keys can’t be repeated, this means that when you are consulting for a certain primary key, you are accessing all the data that correlates with that primary key, and there would be no duplicates.
  • High Security: You can make a table that could be accessed by all users, another one that could be accessed by certain users, another one that could be accessed by you alone… Think you get the idea.
  • Documentation: These databases have been in the market for a long time now, so the documentation is everywhere and easy to access.

Disadvantages of using relational databases

Now, let’s talk a bit about the disadvantages:

  • Expensive: We need software and people to maintain the database, so the cost of doing it is almost always high.
  • Scalability: We can have big data in relational databases with the right servers, but in NoSQL databases, we can store more data with fewer resources and problems.
  • Loss of information: If we have to migrate our database there could be problems in accessing the information and we could lose information in the process.

Non-relational Databases

Now let’s take a look at how a MongoDB create collection query looks like (Which would be the equivalent of a create table in MySQL)

db.createCollection("post")

Ok, that’s all, we didn’t have to put a primary key, we didn’t have to put columns, we could have passed parameters to delimit the size of the collection but in this case we didn’t and the only thing that MongoDB needs is the name of the collection… Even more, what if I tell you that you DOESN’T need a collection at all?

This would be an insert in MongoDB:

db.post_2.insert([
	{
		title: "This is a new Document in the collection post_2 (That could be created by this same document)",
		description: "This is NoSQL",
		tags: ["this", "is", "an", "array"],
		GINT: 100
	}

We have just created a new collection by only inserting a document.

As you may think, documents don’t need to have the same size, don’t need to have the same fields, don’t need to have anything in common (But it would be a good practice that you put each document in a collection that correlates with the same documents) so how we do a Select? Well, it’s something like this:

db.post_2.find({"title":/.*This is a new Document.*/)

The second part of our query would be a Regex that would be the equivalent in SQL “LIKE ‘%This is a new Document.%'”.

So now you have an idea of how a non relational database works, now let’s see the advantages and disadvantages of using this model.

Advantages of using non-relational databases

  • Flexibility: As we saw in our example, we can put whatever we want in a document, and would still be written in our database.
  • Scalability: non-relational databases perform better than relational databases in very large datasets.
  • They can run on devices with limited resources.
  • They can run on multiple devices.

Sounds good, right? But it also has it’s downsize.

Disadvantages of using non-relational databases

  • The integrity of data: As we saw, data doesn’t need to have integrity, so we could find with some problems here.
  • They do not have standardization.
  • Most of these databases do not have a management tool.
  • Compatibility: Most of these databases are not yet compatible with SQL commands.
  • Cross-Platform Support: Most of these databases actually don’t work in a non-linux environment.
  • Poor documentation: As they are not as used as standard SQL databases, they haven’t got so much documentation.

Which is which?

Now that we know what we are talking about, let’s take a look at which databases use SQL and which ones use NoSQL:

SQL databases:

  • Microsoft SQL Server
  • OracleDB
  • MySQL
  • PostgreSQL
  • SQLite
  • MariaDB
  • Teradata

NoSQL databases:

  • MongoDB
  • Cassandra
  • Redis
  • Neo4

Conclusion

You now know how both types of databases work, and also, you know what are the differences between each other. NoSQL databases are widely used by very big companies like Alphabet or Meta that cannot store the quantity of data they have in a conventional way, so they require this kind of databases to optimize their data implementation.

Also, we know that the most used databases today are SQL databases, as they comply better with most companies requirements. We have seen some examples of how we use each one, and at last, we saw which is which.

Here you have an useful link to start over with mongoDB if you want to learn more about it.

And some Google documentation about relational and non-relational databases.

Also, here you have some information about Redis (A NoSQL database) to get you started.

Hope you find this article useful! See you at the next one :).

facebookShare on Facebook
TwitterTweet
FollowFollow us

Leave a Reply