🔷 Part 15: Advanced Query Techniques – Master Complex Queries in SQL and NoSQL
📍 Introduction
Once you’re comfortable with basic queries, it’s time to learn advanced techniques that let you handle complex data retrieval and analysis efficiently.
This part covers:
-
SQL joins and subqueries
-
NoSQL aggregation pipelines
-
Combining data from multiple collections or tables
-
Performance considerations
📚 If you missed Part 14: Data Modeling Best Practices, read it here.
🔸 1. SQL Advanced Queries: Joins & Subqueries
Joins:
-
INNER JOIN: Returns rows with matching values in both tables.
-
LEFT JOIN: Returns all rows from left table, with matching rows from right table or NULL.
-
RIGHT JOIN: Opposite of LEFT JOIN.
-
FULL OUTER JOIN: Returns rows when there is a match in one of the tables.
Example: Get all books and their authors
This query retrieves book titles along with their author names using an INNER JOIN.
SELECT books.title, authors.name
FROM books
INNER JOIN authors ON books.author_id = authors.id;
Subqueries:
-
A query nested inside another query.
-
Useful for filtering, aggregating, or transforming data.
SELECT title FROM books
WHERE author_id IN (SELECT id FROM authors WHERE country = 'USA');
🔹 2. NoSQL Aggregation Pipelines (MongoDB)
-
Aggregation pipelines process data through multiple stages.
-
Common stages:
$match(filter),$group(aggregate),$sort,$project(reshape).
Example: Count books by genre
db.books.aggregate([
{ $group: { _id: "$genre", totalBooks: { $sum: 1 } } },
{ $sort: { totalBooks: -1 } }
]);
🔸 3. Combining Data from Multiple Collections
-
MongoDB uses $lookup for joining collections (like SQL JOIN).
Example: Books with author details
db.books.aggregate([
{
$lookup: {
from: "authors",
localField: "author_id",
foreignField: "_id",
as: "authorDetails"
}
}
]);
🔹 4. Performance Tips
-
Use indexes on join/filter fields.
-
Avoid unnecessary fields in SELECT or
$project. -
Limit result set sizes.
-
Analyze query plans (
EXPLAINin SQL,.explain()in MongoDB).
📝 Summary
| Feature | SQL | NoSQL (MongoDB) |
|---|---|---|
| Joins | INNER, LEFT, RIGHT, FULL OUTER | $lookup in aggregation pipeline |
| Aggregations | GROUP BY, HAVING | $group, $match, $sort |
| Subqueries | Nested SELECT | Sub-pipelines or $facet |
| Performance Optimization | Indexes, query plans | Indexes, .explain(), pipelines |
❓ Frequently Asked Questions
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only matching rows from both tables, while LEFT JOIN returns all rows from the left table and matching rows from the right (or NULL).
Can MongoDB use JOINs like SQL?
MongoDB doesn’t use traditional JOINs, but the $lookup stage in aggregation can simulate joins between collections.
✅ Next Steps
In Part 16, we will explore Database Scaling Techniques — vertical and horizontal scaling approaches for growing databases.
