mongoDB and mysql query comparison

Connect with

This post is about MongoDB and MySQL query comparison. MongoDB query cheat sheet, where you compare sql query vs MongoDB query or SQL query to MongoDB query or vice-versa.

1. Background of MongoDB and MySQL Query comparison

If you are novice for MongoDB, then this post is essential for you. I assume every software developer must have written any SQL (mySQL or SQL server or Oracle SQL …) in his/her life, either in college/school or in initial day. If you know one database either SQL or MongoDB, you can easily grasp the query pattern in another.

2. Overview of SQL query vs MongoDB query

This article is about mySQL query and MongDB query comparison, i.e. SQL query vs MongoDB query. In this post, if you have used MySQL and not used MongoDB, then you can correlate one from another. This post made it simple for you to correlate any query in MongoDB and its counterpart in MySQL or you can correlate any query in MySQL and its similar query in MongoDB.
Based on the demand, I have written this post, many of our readers requested to write one post for the novice MongoDB user, who already know the basic of MySQL or any another SQL. it’s very easy for you to find, what is the query in MongoDB for the counterpart query in SQL. You can run these queries to the MongoDB shell terminal or any client like Robomongo, MongoChef. we will learn MogoDB and mySQL query comparison.

3. Creation of table MySQL Vs MongoDB

In mysql you have to create the table but in mongoDB if you have permission on user then collection can be build by mongodb when you insert the firt document in MongoDB. In mongoDB, no need to create collection before insert of document while SQL you have to create table before inserting the records.

MySQL	mongoDB	description
CREATE TABLE user (name VARCHAR(50), age NUMBER)	db.createCollection("user")	to create table/collection.

4. Select query: mySQL Query to MongoDB Query

In this following tabular grid, you can compare SQL query to MongoDB query or vice-versa. Or SQL query vs mongoDB query or MongoDB query vs SQL query.

MySQL	mongoDB	description
SELECT * FROM user	db.user.find({})	to select all
SELECT name, age FROM user	db.user.find({}, {name: 1, age: 1, _id:0})	to project column/fields
SELECT name, age FROM user WHERE age = 30	db.user.find({age: 30}, {name: 1, age: 1, _id:0})	select with project
SELECT * FROM user WHERE age > 30,	db.user.find({age: {$gt: 30}})	range based selection
SELECT * FROM user WHERE age <= 30,	db.user.find({age: {$lte: 30}})	range based selection on numeric value
SELECT * FROM user WHERE age > 30 AND age < 40,	db.user.find({age: {$gt: 30, $lt: 40}}),	range based selection on numeric value
SELECT * FROM user WHERE age = 32 AND name = 'ranjeet',	db.user.find({age: 32, name: 'ranjeet'});	equality and range both
SELECT * FROM user WHERE age = 30 OR name = 'ranjeet',	db.user.find({$or:[{age:30}, {name: 'ranjeet'}]});	equality and range both
SELECT * FROM user WHERE age = 30 ORDER BY name ASC;	db.user.find({age: 30}).sort({name: 1});	equality and sorting on name with ascending
SELECT * FROM user ORDER BY name DESC ;	db.user.find().sort({name: -1});	equality and sorting on name with descending
SELECT * FROM user WHERE name LIKE '%ran%' ;	db.user.find({name: /ran/});	contains selection
SELECT * FROM user WHERE name LIKE 'ran%' ;	db.user.find({name: /^ran/});	regex start with provided word
SELECT * FROM user LIMIT 10 SKIP 15;	db.user.find().skip(15).limit(10);	for pagination with offset and length
SELECT * FROM user LIMIT 1;	db.user.findOne();	select first record.
SELECT DISTINCT name FROM user;	db.user.distinct("name");	with distinct
SELECT COUNT(1) FROM user;	db.user.count();	count records/document
SELECT COUNT(*) FROM user WHERE AGE > 20	db.user.find({age: {$gt: 20}}).count()	provided condition and then count records/document
SELECT COUNT(AGE) FROM user;	db.user.find({age: {$exists: true}}).count();	existence check

5. Insert query: SQL query vs MmongoDB query

MySQL	mongoDB	description
INSERT INTO user(name\,age) VALUES ('ranjeet'\, 30);	db.user.insert({name: "ranjeet"\, age: 30});	to insert record/document

6. update query: MongoDB Query Vs SQL Query

This is MongoDB query cheat sheet for update query. The comparision of update query between MongoDB query vs SQL query.

MySQL	mongoDB	description
UPDATE user SET age = 30 WHERE name = 'anushka';	db.user.update({name: "anushka"}\, {$set: {age: 30}}\, {multi: true});	update multiple matching
UPDATE user SET age = age + 2 WHERE name = 'ranjeet' ;	db.user.update({name: "ranjeet"}\, {$inc: {age: 2}}\, {multi: true});	update multiple matching
DELETE FROM user WHERE name = 'ranjeet' , db.user.remove({name: "ranjeet"});		to delete matching condition

7. Index creation in mongodb and mysql

In this section, we do mongoDB and MySQL query comparison for index creation query.

MySQL	mongoDB	description
CREATE INDEX ON user (name ASC);	db.user.createIndex({name: 1});	to create index with asc order
CREATE INDEX ON user (name ASC, age DESC);	db.user.ensureIndex({name: 1, age: -1});	to create multi key index with asc and esc

8. explain query plan: for sql query Vs MongoDB query

In this section, I explain about how you view explain and its stat for tuning your query or for improving peformance of your query ( mongoDB query or SQL query)

MySQL	mongoDB	description
EXPLAIN SELECT * FROM user WHERE age = 32;	db.user.find({age: 32}).explain();	for explain query

9. query with matching criteria in mongodb

In this section of MongoDB query cheat sheet, you can find matching criteria of SQL query vs MongoDB query.

mongoDB	description
{key1: {$nin: [5, 'ranjeet']}};	returns doc with anything but not contains 5 or 'ranjeet'.
{key1: {$size: 3}};	return docs where array size of key1 exactly 3 elements.
{key1: {$exists: true}}	return docs containing a key1 field.
{key1: {$not: {$type: 2}}};	returns where key is not a string. $not negates any of the other query operators.

I hope, you enjoyed this post by learning MongoDB and MySQL query comparison. You can also visit MongoDB Tutorial Listing page for more articles on MongoDB document-oriented database.

10. Reference

You can visit docs.mongodb.com for more details.

Happy Learning 🙂 for SQL query vs MongoDB query.
Your suggestions or comment are welcome to improve this post.