What is Subquery?
A subquery is query inside an INSERT,UPDATE,SELECT,DELETE statements.
It can also be nested inside another subquery. It can be used anywhere an expression is allowed.
Example:
- SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500) ;
- INSERT INTO CUSTOMERS_BKP SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS) ;
What is Join?
Join is a clause used to combine data from multiple tables based on related common values.
Types:
Inner join
Self join
Outer join (left and right)
Examples:
- SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
- SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders LEFT OUTER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
What is MongoDB?
MongoDB is a NoSQL database which is used to store data in document form.
It is an OpenSource Document oriented Database. It uses JSON (JavaScript Object Notation) for displaying documents. And BSON (Binary Object Notation) for storage and retrieval of data.
Features:
- Flexible schema
- Heavy read
- Allows documents inside a document
- Array functionalities
- Support for CRUD operations with aggregation
How to perform Subquery and Join in MongoDB?
For subquery operation MongoDB provides aggregation and $lookup function.
We can easily use $lookup for subqueries and optimization.
Syntax:
{
$lookup:
{
from: ”name of collection to join”
localField: ”field from input document”
foreignField: “field from form <collection>”
as: “output field”
}
}
Examples:
SQL:
SELECT *,orders_det
FROM orders
where orders_det IN
(SELECT * FROM customers where custid=order.customerid)
MongoDB:
db.orders.aggregate([
{
$lookup:{
From:”customers”,
localField:”customerid”
foriengnField:”custid”
as:”orders_det”}
}
])
