Advanced Query Techniques for SQL and NoSQL Databases: Aggregations, Joins & More

🔷 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 (EXPLAIN in 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.

← Previous Next →