6 critical differences you need to know!

TL;DR:
Major differences between SQL vs NoSQL
- SQL databases have a strict schema while NoSQL databases don’t.
- SQL databases have relations while NoSQL databases don’t have any relations.
- SQL databases consist of tables whereas NoSQL databases have collections and documents.
- SQL databases have multiple tables while in NoSql databases, the data is shrunk into just a few collections.
- Sql databases are vertically scalable but NoSQL databases are both vertically and horizontally scalable.
- SQL databases can have limitations on no. of queries/sec whereas NoSQL databases don’t.
Now that we’ve seen the major differences between the SQL and NoSQL databases, let’s dive into what they actually are and how they work. We will also compare the different advantages and disadvantages of both databases.
Let’s dive in!

SQL: Structured Query Language
SQL stands for Structured Query Language which allows you to write database queries. So at the end of the day, SQL is just a language and not a database in itself. But the databases can be queried by the use of SQL. A typical SQL query might look something like this:
SELECT id, name, email FROM users;
In this sample query, the keywords that are capitalized are called SQL keywords, and the other parts of the query consist of custom columns that are needed and the name of the table in the database.
SQL has many more keywords and commands, of course. SQL can be used to perform any CRUD operations on the database (Create, Read, Update, and Delete).
When we usually talk about SQL vs NoSQL, we actually talk about the databases behind them.
For SQL, the database that we typically use is a relational one. It means that we have a database that works with certain assumptions and it supports the structured query language.
Such a database works with tables. Think of a table as a container for a specific type of data. Something like a users table and a products table. If we want to store the information regarding our application users, we store that in the users table and we store the products’ information in the products table.
The tables in relational databases are related to each other through common columns called keys. There are primary keys and foreign keys in a relational database. Primary keys help in uniquely identifying the records in a table whereas foreign keys help in relating to the related information in other tables.
However, in relational databases, where we query using SQL, there would be some strict requirements on how we can store the data. This is because there is a fixed schema which the database has to adhere to. Each column has a specified data type and can’t be changed. Even adding a new column to the table is not as flexible you want it to be. Let’s say you need to store an extra piece of information, maybe a customer special instructions for an order. Ideally, you would want to have the special instructions information for just that order. But with SQL, it’s not possible. You’d have to add a new column called special instructions to the entire table, even if that means all other records will have NULL values in them. While this is not exactly a bad idea, it’s not as flexible either.
However, SQL is quite powerful. Even with all the complexities that it brings in due to the multiple joins that need to be processed every time a request comes in, SQL is still the favorite choice for many developers. If you are planning to get into data science or already into it, SQL is gonna be very helpful. SQL is a great data wrangling tool and can pretty much do everything that python does for data wrangling. Read more about the importance of SQL for data science here.
Now that we spoke about SQL, let’s have a look at what NoSQL is and how it works.

NoSQL: Not Only SQL
NoSQL stands for "Not Only SQL" and that’s because NoSQL supports the storage of unstructured data due to its very nature. NoSQL databases do not have a fixed schema like the way SQL databases do.
In fact, NoSQL databases don’t even have tables and indexes like SQL databases. So how do we store data then? In NoSQL, we store data in a hierarchical form. Information is stored not in the form of tables but as collections and documents. Think of NoSQL database collections as the tables of a SQL database, and the NoSQL documents as the SQL records.
But unlike SQL tables, which have a fixed schema, there is no such restriction for NoSQL collections. The documents can have as many values as one may need. With this incredible level of flexibility, it is now possible to have different information in each document. Going back to the example of the special instruction column we discussed in the SQL section, it is now possible to add the special instruction column just for that specific order without changing the entire collection.
Also, there is no such thing as relations in NoSQL. "Oh, then how on earth would you combine data from different collections then, Praneeth?" you may ask.
Well, even though technically there is no such thing as "relational" in the NoSQL databases, it is still possible to bring all the relevant together into one place. And that is by literally putting all the data that you need into a single collection. Yup. This might sound very inefficient, but this drastically reduces the query complexity and allows us to very quickly display the data without having the need to join multiple tables, as we would do in a SQL database.
The downside to this, however, is that we’d end up with a lot of duplicate data. For example, you would have a users collection and a products collection. When you need to store the order-related information in another collection, you will have to include both the user information and the product information again in the orders collection.

Scaling
With SQL databases, it is quite difficult/impossible to scale horizontally. But it’s not the case with NoSQL databases. But hey, what the hell is horizontal/vertical scaling?
Horizontal scaling is when you add additional servers to your database as your data grows. Whereas vertical scaling is just adding more power to the existing server. That’s one big downside of SQL databases because at some point it would be really difficult to scale further. But with NoSQL, you can just add additional servers as you scale and live a tension-free life!
Pros and Cons of Both Types
Both the databases come with their individual pros and cons.
- While having a fixed schema is good for a predictive layout and reliable fields in the database, it’s not quite flexible. In this regard, SQL has a predictive layout but loses out on flexibility, whereas NoSQL has all the flexibility but lacks the reliability of fields.
- Due to the relation between tables, SQL databases can easily handle the update operations. NoSQL on the other hand is good at handling read requests and not at update operations due to the lack of a fixed schema.
- When building an application for the long term, scaling could be a concern. And NoSQL is quite feasible due to its horizontal scaling capability.
Final Thoughts
Practically, it is possible to build any kind of application with either of these databases. It all boils down to your particular need and the nature of the business. The problem arises only when the data grows big.
Do you need a lot of relations among the data, which you would change rather frequently? Do you need a clear schema defined? Go with SQL.
Does your application make a lot of reading requests and not a lot of write requests? Want to display data quickly without any need for complex queries? Is scaling is a concern? Go with NoSQL.
There is no clear winner among these two and it only depends on the specific need of the application.
Originally published on my blog at praneethvasarla.com