In this post, we will understand the difference between NoSQL vs SQL, MySQL vs MongoDB Database. Which is better SQL or NoSQL?
When it comes to choosing a database, one of the biggest decisions is picking a relational (SQL) or non-relational (NoSQL) data structure. While both are viable options, there are certain key differences between the two that users must keep in mind when making a decision.
Here, we break down the most important distinctions and discuss two of the key players in the relational vs non-relational debate: MySQL and MongoDB.
SQL databases are primarily called as Relational Databases (RDBMS); whereas NoSQL database are primarily called as non-relational or distributed database.
SQL databases are table based databases whereas NoSQL databases are document based, key-value pairs, graph databases or wide-column stores. This means that SQL databases represent data in form of tables which consists of n number of rows of data whereas NoSQL databases are the collection of key-value pair, documents, graph databases or wide-column stores which do not have standard schema definitions which it needs to adhered to.
SQL databases have predefined schema whereas NoSQL databases have dynamic schema for unstructured data.
SQL databases are vertically scalable whereas the NoSQL databases are horizontally scalable. SQL databases are scaled by increasing the horse-power of the hardware. NoSQL databases are scaled by increasing the databases servers in the pool of resources to reduce the load.
SQL databases uses SQL ( structured query language ) for defining and manipulating the data, which is very powerful. In NoSQL database, queries are focused on collection of documents. Sometimes it is also called as UnQL (Unstructured Query Language). The syntax of using UnQL varies from database to database.
SQL database examples: MySql, Oracle, Sqlite, Postgres and MS-SQL. NoSQL database examples: MongoDB, BigTable, Redis, RavenDb, Cassandra, Hbase, Neo4j and CouchDb
For complex queries: SQL databases are good fit for the complex query intensive environment whereas NoSQL databases are not good fit for complex queries. On a high-level, NoSQL don’t have standard interfaces to perform complex queries, and the queries themselves in NoSQL are not as powerful as SQL query language.
For the type of data to be stored: SQL databases are not best fit for hierarchical data storage. But, NoSQL database fits better for the hierarchical data storage as it follows the key-value pair way of storing data similar to JSON data. NoSQL database are highly preferred for large data set (i.e for big data). Hbase is an example for this purpose.
For scalability: In most typical situations, SQL databases are vertically scalable. You can manage increasing load by increasing the CPU, RAM, SSD, etc, on a single server. On the other hand, NoSQL databases are horizontally scalable. You can just add few more servers easily in your NoSQL database infrastructure to handle the large traffic.
For high transactional based application: SQL databases are best fit for heavy duty transactional type applications, as it is more stable and promises the atomicity as well as integrity of the data. While you can use NoSQL for transactions purpose, it is still not comparable and sable enough in high load and for complex transactional applications.
For support: Excellent support are available for all SQL database from their vendors. There are also lot of independent consultations who can help you with SQL database for a very large scale deployments. For some NoSQL database you still have to rely on community support, and only limited outside experts are available for you to setup and deploy your large scale NoSQL deployments.
For properties: SQL databases emphasizes on ACID properties ( Atomicity, Consistency, Isolation and Durability) whereas the NoSQL database follows the Brewers CAP theorem ( Consistency, Availability and Partition tolerance )
For DB types: On a high-level, we can classify SQL databases as either open-source or close-sourced from commercial vendors. NoSQL databases can be classified on the basis of way of storing data as graph databases, key-value store databases, document store databases, column store database and XML databases.
The following are some of the benefits and strengths of MongoDB:
Free to use: Since October 2018, MongoDB's updates have been published under the Server Side Public License (SSPL) v1, and the database is free to use.
Dynamic schema: As mentioned, this gives you the flexibility to change your data schema without modifying any of your existing data.
Scalability: MongoDB is horizontally scalable, which helps reduce the workload and scale your business with ease.
Manageability: The database doesn’t require a database administrator. Since it is fairly user-friendly in this way, it can be used by both developers and administrators.
Speed: It’s high-performing for simple queries.
Flexibility: You can add new columns or fields on MongoDB without affecting existing rows or application performance.
ACID Transactions: MongoDB v.4 is finally getting support for multi-document ACID (atomicity, consistency, isolation, durability) transactions. That’s something the MongoDB community has been asking for for years and MongoDB Inc, the company behind the project, is now about to make this a reality.
MongoDB Atlas (a new feature): MongoDB recently added MongoDB Atlas global cloud database technology to its offerings. This feature allows you to deploy fully-managed MongoDB via AWS, Azure, or GCP. MongoDB Atlas lets you use drivers, integrations, and tools to reduce the time required to manage your database. Here's the pricing information from Atlas.
Who Should Use It? MongoDB is a good choice for businesses that have rapid growth or databases with no clear schema definitions (i.e., you have a lot of unstructured data). If you cannot define a schema for your database, if you find yourself denormalizing data schemas, or if your data requirements and schemas are constantly evolving - as is often the case with mobile apps, real-time analytics, content management systems, etc. - MongoDB can be a strong choice for you.
Here are some MySQL benefits and strengths:
Owned by Oracle: Although MySQL is free and open-source, the database system is owned and managed by Oracle.
Maturity: MySQL is an extremely established database, meaning that there’s a huge community, extensive testing and quite a bit of stability.
Compatibility: MySQL is available for all major platforms, including Linux, Windows, Mac, BSD, and Solaris. It also has connectors to languages like Node.js, Ruby, C#, C++, Java, Perl, Python, and PHP, meaning that it’s not limited to SQL query language.
Cost-effective: The database is open-source and free.
Replicable: The MySQL database can be replicated across multiple nodes, meaning that the workload can be reduced and the scalability and availability of the application can be increased.
Sharding: While sharding cannot be done on most SQL databases, it can be done on MySQL servers. This is both cost-effective and good for business.
Who Should Use It? MySQL is a strong choice for any business that will benefit from its pre-defined structure and set schemas. For example, applications that require multi-row transactions - like accounting systems or systems that monitor inventory - or that run on legacy systems will thrive with the MySQL structure.
In fact, every database has its unique advantages. No database offers the best solution, only the most suitable option for each project.
SQL vs NoSQL or MySQL vs MongoDB: SQL or NoSQL? MySQL vs MongoDB? Which database is better? Which one should you use?
SQL or NoSQL? MySQL vs MongoDB? Which database is better? Which one should you use?
In this article, we'll explain syntax differences between standard SQL and the Transact-SQL language dedicated to interacting with the SQL#1 Names of Database Objects
In relational database systems, we name tables, views, and columns, but sometimes we need to use the same name as a keyword or use special characters. In standard SQL, you can place this kind of name in quotation marks (""), but in T-SQL, you can also place it in brackets (). Look at these examples for the name of a table in T-SQL:
CREATE TABLE dbo.test.“first name” ( Id INT, Name VARCHAR(100)); CREATE TABLE dbo.test.[first name] ( Id INT, Name VARCHAR(100));
Only the first delimiter (the quotation marks) for the special name is also part of the SQL standard.What Is Different in a SELECT Statement?#2 Returning Values
The SQL standard does not have a syntax for a query returning values or values coming from expressions without referring to any columns of a table, but MS SQL Server does allow for this type of expression. How? You can use a SELECT statement alone with an expression or with other values not coming from columns of the table. In T-SQL, it looks like the example below:
SELECT 12/6 ;
In this expression, we don’t need a table to evaluate 12 divided by 6, therefore, the FROM statement and the name of the table can be omitted.#3 Limiting Records in a Result Set
In the SQL standard, you can limit the number of records in the results by using the syntax illustrated below:
SELECT * FROM tab FETCH FIRST 10 ROWS ONLY
T-SQL implements this syntax in a different way. The example below shows the MS SQL Server syntax:
SELECT * FROM tab ORDER BY col1 DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;
As you notice, this uses an ORDER BY clause. Another way to select rows, but without ORDER BY, is by using the TOP clause in T-SQL:
SELECT TOP 10 * FROM tab;#4 Automatically Generating Values
The SQL standard enables you to create columns with automatically generated values. The syntax to do this is shown below:
CREATE TABLE tab (id DECIMAL GENERATED ALWAYS AS IDENTITY);
In T-SQL we can also automatically generate values, but in this way:
CREATE TABLE tab (id INTEGER IDENTITY);#5 Math Functions
Several common mathematical functions are part of the SQL standard. One of these math functions is CEIL(x), which we don’t find in T-SQL. Instead, T-SQL provides the following non-standard functions: SIGN(x), ROUND(x,[,d]) to round decimal value x to the number of decimal positions, TRUNC(x) for truncating to given number of decimal places, LOG(x) to return the natural logarithm for a value x, and RANDOM() to generate random numbers. The highest or lowest number in a list in the SQL standard is returned by MAX(list) and MIN(list) functions, but in Transact-SQL, you use the GREATEST(list) and LEAST(list) functions.
T-SQL function ROUND:#6 Aggregate Functions
SELECT ROUND(col) FROM tab;
We find another syntax difference with the aggregate functions. The functions COUNT, SUM, and AVG all take an argument related to a count. T-SQL allows the use of DISTINCT before these argument values so that rows are counted only if the values are different from other rows. The SQL standard doesn't allow for the use of DISTINCT in these functions.
SELECT COUNT(col) FROM tab;
SELECT COUNT(col) FROM tab;
SELECT COUNT(DISTINCT col) FROM tab;
But in T-SQL we don’t find a population covariance function: COVAR_POP(x,y), which is defined in the SQL standard.#7 Retrieving Parts of Dates and Times
Most relational database systems deliver many functions to operate on dates and times.
In standard SQL, the EXTRACT(YEAR FROM x) function and similar functions to select parts of dates are different from the T-SQL functions like YEAR(x) or DATEPART(year, x).
There is also a difference in getting the current date and time. Standard SQL allows you to get the current date with the CURRENT_DATE function, but in MS SQL Server, there is not a similar function, so we have to use the GETDATE function as an argument in the CAST function to convert to a DATE data type.#8 Operating on Strings
Using functions to operate on strings is also different between the SQL standard and T-SQL. The main difference is found in removing trailing and leading spaces from a string. In standard SQL, there is the TRIM function, but in T-SQL, there are several related functions: TRIM (removing trailing and leading spaces), LTRIM (removing leading spaces), and RTRIM (removing trailing spaces).
Another very-often-used string function is SUBSTRING.
The standard SQL syntax for the SUBSTRING function looks like:
SUBSTRING(str FROM start [FOR len])
but in T-SQL, the syntax of this function looks like:
SUBSTRING(str, start, length)
There are reasons sometimes to add values coming from other columns and/or additional strings. Standard SQL enables the following syntax to do this:
As you can see, this syntax makes use of the || operator to add one string to another.
But the equivalent operator in T-SQL is the plus sign character. Look at this example:
SELECT col1 + col2 FROM tab;
In SQL Server, we also have the possibility to use the CONCAT function concatenates a list of strings:
SELECT CONCAT(col1, str1, col2, ...) FROM tab;
We can also repeat one character several times. Standard SQL defines the function REPEAT(str, n) to do this. Transact-SQL provides the REPLICATE function. For example:
SELECT REPLICATE(str, x);
where x indicates how many times to repeat the string or character.#9 Inequality Operator
During filtering records in a SELECT statement, sometimes we have to use an inequality operator. Standard SQL defines <> as this operator, while T-SQL allows for both the standard operator and the != operator:
SELECT col3 FROM tab WHERE col1 != col2;#10 ISNULL Function
In T-SQL, we have the ability to replace NULL values coming from a column using the ISNULL function. This is a function that is specific to T-SQL and is not in the SQL standard.
SELECT ISNULL(col1) FROM tab;Which Parts of DML Syntax Are Different?
In T-SQL, the basic syntax of DELETE, UPDATE, and INSERT queries is the same as the SQL standard, but differences appear in more advanced queries. Let’s look at them.#11 OUTPUT Keyword
The OUTPUT keyword occurs in DELETE, UPDATE, and INSERT statements. It is not defined in standard SQL.
Using T-SQL, we can see extra information returned by a query. It returns both old and new values in UPDATE or the values added using INSERT or deleted using DELETE. To see this information, we have to use prefixes in INSERT, UPDATE, and DELETE.
UPDATE tab SET col='new value'
OUTPUT Deleted.col, Inserted.col;
We see the result of changing records with the previous and new values in an updated column. The SQL standard does not support this feature.#12 Syntax for INSERT INTO ... SELECT
Another structure of an INSERT query is INSERT INTO … SELECT. T-SQL allows you to insert data from another table into a destination table. Look at this query:
INSERT INTO tab SELECT col1,col2,... FROM tab_source;
It is not a standard feature but a feature characteristic of SQL Server.#13 FROM Clause in DELETE and UPDATE
SQL Server provides extended syntax of the UPDATE and DELETE with FROM clauses. You can use DELETE with FROM to use the rows from one table to remove corresponding rows in another table by referring to a primary key and a foreign key. Similarly, you can use UPDATE with FROM update rows from one table by referring to the rows of another table using common values (primary key in one table and foreign key in second, e.g. the same city name). Here is an example:
DELETE FROM Book
WHERE Author.Id=Book.AuthorId AND Author.Name IS NULL;
WHERE Book.AuthorId=Author.Id AND Author.Id=12;
The SQL standard doesn’t provide this syntax.#14 INSERT, UPDATE, and DELETE With JOIN
You can also use INSERT, UPDATE, and DELETE using JOIN to connect to another table. An example of this is:
DELETE ItemOrder FROM ItemOrder
JOIN Item ON ItemOrder.ItemId=Item.Id
WHERE YEAR(Item.DeliveredDate) <= 2017;
This feature is not in the SQL standard.Summary
This article does not cover all the issues about syntax differences between the SQL standard and T-SQL using the MS SQL Server system. However, this guide helps point out some basic features characteristic only of Transact-SQL and what SQL standard syntax isn’t implemented by MS SQL Server.
Thanks for reading. If you liked this post, share it with all of your programming buddies!
Originally published on https://dzone.com
This SQL Full Course video will cover all the topics of SQL starting from scratch. This video is great for beginners who want to learn SQL and for advanced people to brush up their skills.
Thanks for watching ❤
If you liked this post, share it with all of your programming buddies!