SQL Joins in SQL Server – INNER, LEFT, RIGHT, FULL Explained with Examples

Part 8: SQL Joins – INNER, LEFT, RIGHT, FULL Explained

Microsoft SQL Server Tutorial Series: Beginner to Expert

---

Welcome to the Intermediate section of our SQL Server Tutorial Series! In this part, we’ll dive into one of the most important topics in SQL — Joins.

Joins are essential when working with relational databases, as they allow you to combine data from multiple tables based on related columns.

---

🔍 What You'll Learn

• What SQL Joins are and why they matter
• The difference between INNER, LEFT, RIGHT, and FULL Joins
• Syntax and visual examples of each type
• Best practices for using Joins in SQL Server

---

📘 What is a SQL Join?

A SQL Join is used to combine rows from two or more tables, based on a related column between them — often a foreign key and primary key relationship.

Let’s say you have two tables:

Students
---------
StudentID | Name

Enrollments
--------------
EnrollmentID | StudentID | CourseName

To get a list of student names with their courses, you'll use a join on the StudentID column.

---

🔗 Types of Joins in SQL Server

Here are the four main types of joins in SQL Server:

Join Type	Description
INNER JOIN	Returns only the matching rows between both tables.
LEFT JOIN	Returns all rows from the left table, and matched rows from the right table.
RIGHT JOIN	Returns all rows from the right table, and matched rows from the left table.
FULL JOIN	Returns all rows when there is a match in either table.

---

🧠 Example Tables

Let’s assume the following data:

Students
+-----------+---------+
| StudentID | Name    |
+-----------+---------+
| 1         | Alice   |
| 2         | Bob     |
| 3         | Charlie |
+-----------+---------+

Enrollments
+--------------+-----------+---------------+
| EnrollmentID | StudentID | CourseName    |
+--------------+-----------+---------------+
| 1            | 1         | Math          |
| 2            | 2         | Science       |
| 3            | 4         | History       |
+--------------+-----------+---------------+

---

🔸 INNER JOIN

SELECT Students.Name, Enrollments.CourseName
FROM Students
INNER JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID;

✅ Returns: Only rows where StudentID matches in both tables.

---

🔸 LEFT JOIN

SELECT Students.Name, Enrollments.CourseName
FROM Students
LEFT JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID;

✅ Returns: All students, even if they are not enrolled in any course.

---

🔸 RIGHT JOIN

SELECT Students.Name, Enrollments.CourseName
FROM Students
RIGHT JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID;

✅ Returns: All enrollments, even if the student is not found in the Students table.

---

🔸 FULL OUTER JOIN

SELECT Students.Name, Enrollments.CourseName
FROM Students
FULL OUTER JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID;

✅ Returns: All students and all enrollments, matching where possible.

---

📊 Visual Summary of Join Behavior

Join Type	Includes Unmatched Rows From
INNER JOIN	None
LEFT JOIN	Left Table (Students)
RIGHT JOIN	Right Table (Enrollments)
FULL OUTER JOIN	Both Tables

---

🛠️ Best Practices for Using Joins

• Always use ON clause correctly to prevent Cartesian products.
• Use INNER JOIN when you only want matched records.
• Use LEFT JOIN when you want all data from the first (left) table.
• Make sure joined columns have proper indexes for performance.
• Use table aliases (S, E) in complex queries to make them readable.

---

🧾 Quick Join Syntax Cheat Sheet

-- INNER JOIN
SELECT * FROM A
INNER JOIN B ON A.ID = B.A_ID;

-- LEFT JOIN
SELECT * FROM A
LEFT JOIN B ON A.ID = B.A_ID;

-- RIGHT JOIN
SELECT * FROM A
RIGHT JOIN B ON A.ID = B.A_ID;

-- FULL JOIN
SELECT * FROM A
FULL OUTER JOIN B ON A.ID = B.A_ID;

---

✅ Summary

In this tutorial, you learned:

• How to use SQL Joins in SQL Server
• INNER, LEFT, RIGHT, and FULL OUTER joins with syntax and examples
• How Joins combine data from multiple tables
• Best practices for writing clean and efficient joins

---

🔗 What’s Next?

Now that you’ve mastered SQL Joins, the next part will teach you how to perform grouping and aggregation using GROUP BY, HAVING, and aggregate functions like COUNT(), SUM(), and AVG().

---

Have any questions or insights? Leave a comment below — let’s grow together! 🚀

---

← Previous Next →

Master SQL Joins & Table Relationships with Fun Python & SQLite Examples

Joining Tables and Relationships with SQL: A Simple and Fun Tutorial for Everyone

Learn how to join tables and model relationships in Python using SQLite. This beginner-friendly tutorial shows how to connect data from multiple tables in a toy store database.

Welcome back to our exciting SQL adventure! In our last tutorial, Filtering, Sorting, and Aggregating Data, we learned how to pick out specific data, arrange it neatly, and summarize it using SQL commands in our toy store database. Now, let’s explore something even cooler: joining tables and understanding relationships in a database. This is like connecting different toy boxes to tell a bigger story, such as linking toys to their owners. This tutorial covers key aspects of joining tables and relationships. We’ll continue using SQLite and Python with our toystore.db database, keeping it fun and simple like organizing a treasure hunt!

---

What are Joining Tables and Relationships?

Imagine your toy store has two notebooks: one lists all your toys, and another lists customers who buy them. Sometimes, you want to combine these notebooks to answer questions like, “Which customer bought which toy?” This is where joining tables comes in—it lets you connect information from different tables in a database.

A relationship is how the tables are connected. For example, a customer might be linked to a toy they bought through a special number (like a toy’s ID). SQL helps you join tables to see this combined information, making your data more powerful and fun to explore!

In our toy store, we’ll:

• Create two tables: Toys and Customers.
• Link them with a third table, Purchases, to show which customer bought which toy.
• Use SQL JOIN commands to combine the data.

---

Why Learn Joining Tables and Relationships?

Joining tables and understanding relationships are awesome because:

• They’re Simple: The SQL commands are like connecting puzzle pieces.
• They’re Powerful: You can answer complex questions by combining data from multiple tables.
• They’re Useful: Joins are used in apps, websites, games, and school projects to connect information.
• They’re Fun: It’s like being a detective, linking clues to solve a mystery!
• They Build on SQL: If you know SELECT and WHERE from our last tutorials, you’re ready for joins.

• Powerful for Apps & Reporting: Joins let you create customer dashboards or sales reports.
• Data Analysis Essential: Anywhere you need to correlate data—like users and their actions—you’ll use joins.
• Career Skill: Understanding joins is foundational for data analysts, backend developers, and QA engineers.

Let’s dive into our toy store and learn how to join tables!

---

Getting Started

We’ll use Python with SQLite to run our SQL commands, just like before. Make sure you have Python installed (download it from python.org if needed). SQLite is built into Python, so no extra setup is required. You can also use DB Browser for SQLite to see your tables visually, but we’ll focus on Python code for clarity.

We’ll work with our toystore.db database and create three tables:

• Toys: Stores toy details (from our last tutorial).
• Customers: Stores customer names and contact info.
• Purchases: Links customers to the toys they bought, showing relationships.

Here’s the code to set up the database and tables:

import sqlite3

# Connect to the database
conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()

# Create Toys table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS Toys (
        ToyID INTEGER PRIMARY KEY,
        Name TEXT,
        Type TEXT,
        Price REAL
    )
''')

# Create Customers table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS Customers (
        CustomerID INTEGER PRIMARY KEY,
        Name TEXT,
        Email TEXT
    )
''')

# Create Purchases table to link Toys and Customers
cursor.execute('''
    CREATE TABLE IF NOT EXISTS Purchases (
        PurchaseID INTEGER PRIMARY KEY,
        CustomerID INTEGER,
        ToyID INTEGER,
        FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
        FOREIGN KEY (ToyID) REFERENCES Toys(ToyID)
    )
''')

# Clear existing data to avoid duplicates
cursor.execute("DELETE FROM Toys")
cursor.execute("DELETE FROM Customers")
cursor.execute("DELETE FROM Purchases")

# Add toys
cursor.execute("INSERT INTO Toys (Name, Type, Price) VALUES ('Robot', 'Action Figure', 30.00)")
cursor.execute("INSERT INTO Toys (Name, Type, Price) VALUES ('Jigsaw', 'Puzzle', 10.00)")
cursor.execute("INSERT INTO Toys (Name, Type, Price) VALUES ('Teddy', 'Stuffed Animal', 15.00)")

# Add customers
cursor.execute("INSERT INTO Customers (Name, Email) VALUES ('Alice', 'alice@email.com')")
cursor.execute("INSERT INTO Customers (Name, Email) VALUES ('Bob', 'bob@email.com')")

# Add purchases (Alice bought Robot and Jigsaw, Bob bought Teddy)
cursor.execute("INSERT INTO Purchases (CustomerID, ToyID) VALUES (1, 1)")
cursor.execute("INSERT INTO Purchases (CustomerID, ToyID) VALUES (1, 2)")
cursor.execute("INSERT INTO Purchases (CustomerID, ToyID) VALUES (2, 3)")

conn.commit()
conn.close()
print("Toy store database with relationships ready!")

What’s Happening?

• Toys table: Stores toys with ToyID, Name, Type, and Price.
• Customers table: Stores customers with CustomerID, Name, and Email.
• Purchases table: Links customers to toys using CustomerID and ToyID. The FOREIGN KEY ensures CustomerID matches a CustomerID in the Customers table, and ToyID matches a ToyID in the Toys table.

This setup creates a relationship between tables: the Purchases table connects Customers and Toys like a bridge.

---

Understanding Relationships

A relationship shows how tables are connected. In our toy store:

• The Purchases table links Customers to Toys using CustomerID and ToyID.
• This is called a many-to-many relationship because one customer can buy many toys, and one toy can be bought by many customers.
• The FOREIGN KEY ensures the IDs match, keeping the data organized and valid.
• Foreign keys enforce referential integrity—ensuring the CustomerID in Purchases actually refers to a valid customer. Trying to insert a purchase with a non-existing CustomerID would result in an error.

Other types of relationships include:

• One-to-many: One customer can buy many toys, but each toy is linked to one purchase.
• One-to-one: One customer can have one favorite toy (rarely used).

Joins let us combine these tables to see the full picture!

---

Joining Tables with SQL

SQL JOIN commands combine data from multiple tables. There are several types of joins, but we’ll focus on the most common ones:

• INNER JOIN: Shows only rows where there’s a match in both tables.
• LEFT JOIN: Shows all rows from the first table, even if there’s no match in the second.
• RIGHT JOIN: Shows all rows from the second table (less common in SQLite).
• FULL JOIN: Shows all rows from both tables (not supported in SQLite).

1. INNER JOIN: Finding Matches

Show each purchase with the customer’s name and the toy’s name.

import sqlite3

conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()

print("Purchases with Customer and Toy Names:")
cursor.execute('''
    SELECT Customers.Name, Toys.Name, Toys.Price
    FROM Purchases
    INNER JOIN Customers ON Purchases.CustomerID = Customers.CustomerID
    INNER JOIN Toys ON Purchases.ToyID = Toys.ToyID
''')
for purchase in cursor.fetchall():
    print(purchase)

conn.close()

Output:

('Alice', 'Robot', 30.0)
('Alice', 'Jigsaw', 10.0)
('Bob', 'Teddy', 15.0)

2. LEFT JOIN: Including All from One Table

import sqlite3

conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()

# Add Charlie (no purchases)
cursor.execute("INSERT INTO Customers (Name, Email) VALUES ('Charlie', 'charlie@email.com')")
conn.commit()

# LEFT JOIN to show all customers
print("All Customers and Their Purchases (if any):")
cursor.execute('''
    SELECT Customers.Name, Toys.Name
    FROM Customers
    LEFT JOIN Purchases ON Customers.CustomerID = Purchases.CustomerID
    LEFT JOIN Toys ON Purchases.ToyID = Toys.ToyID
''')
for result in cursor.fetchall():
    print(result)

conn.close()

Output:

('Alice', 'Robot')
('Alice', 'Jigsaw')
('Bob', 'Teddy')
('Charlie', None)

Join Type	Includes Rows	Example Use
INNER JOIN	Only matching rows	Who bought a toy?
LEFT JOIN	All from left + matches	Which customers have no purchases?

3. Combining Joins with Filtering and Sorting

import sqlite3

conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()

print("Purchases of Toys Over $15, Sorted by Price:")
cursor.execute('''
    SELECT Customers.Name, Toys.Name, Toys.Price
    FROM Purchases
    INNER JOIN Customers ON Purchases.CustomerID = Customers.CustomerID
    INNER JOIN Toys ON Purchases.ToyID = Toys.ToyID
    WHERE Toys.Price > 15.00
    ORDER BY Toys.Price ASC
''')
for purchase in cursor.fetchall():
    print(purchase)

conn.close()

Output:

('Alice', 'Robot', 30.0)

---

Best Practices for Writing SQL JOINs

• Use explicit JOIN ... ON syntax instead of comma-based joins for clarity. :contentReference[oaicite:0]{index=0}
• Index foreign key columns like CustomerID and ToyID to speed up joins. :contentReference[oaicite:1]{index=1}
• Use table aliases (e.g., c for Customers, t for Toys) for cleaner queries. :contentReference[oaicite:2]{index=2}
• Select only needed columns—avoid using * to improve performance. :contentReference[oaicite:3]{index=3}

---

Tips for Success

• Start Simple: Try one join at a time.
• Check Relationships: Make sure your tables are linked with keys.
• Test with SELECT: Use SELECT to check your join results.
• Use Clear Names: Write Customers.Name instead of just Name.
• Practice: Create your own datasets and practice joining!

---

Common Questions

1. Are joins hard to learn?

No! They’re like matching pieces in a puzzle.

2. Do joins work with other databases?

Yes, the syntax is very similar across platforms.

3. What if I join the wrong tables?

You might get incorrect results or no data, so double-check your ON clauses.

4. Can I join more than two tables?

Absolutely! Like we did with three tables.

---

Wrapping Up

Joining tables and understanding relationships in SQL is like linking toy boxes to tell a bigger story. In this tutorial, we used INNER JOIN and LEFT JOIN to connect our Toys, Customers, and Purchases tables in toystore.db, answering questions like “Who bought what?” We also combined joins with filtering and sorting for extra power.

joins are a fun and essential skill for managing data.

Try creating your own database for something you love, like movies and actors, and practice joining tables. Use DB Browser for SQLite to see your data visually or keep experimenting with Python. With SQL joins, you’re now a data detective, ready to connect and explore information like a superhero!

Happy SQL adventures, and keep connecting those tables!

← Previous Next →

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 →

Full Practical Solution | SQL JOINs vs MongoDB Referencing

 Here’s the full solution to the Practice Assignment on SQL JOINs vs MongoDB Referencing, including expected outputs and explanations.

---

✅ Solution: SQL JOINs vs MongoDB Referencing

---

🔹 Part A: SQL Solution

✅ 1. Tables Created

CREATE TABLE Authors (
  AuthorID INT PRIMARY KEY,
  Name VARCHAR(100)
);

CREATE TABLE Books (
  BookID INT PRIMARY KEY,
  Title VARCHAR(150),
  AuthorID INT,
  FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

---

✅ 2. Sample Data Inserted

INSERT INTO Authors (AuthorID, Name) VALUES
(1, 'George Orwell'),
(2, 'J.K. Rowling');

INSERT INTO Books (BookID, Title, AuthorID) VALUES
(101, '1984', 1),
(102, 'Animal Farm', 1),
(103, 'Harry Potter', 2);

---

✅ 3. Query Using JOIN

SELECT Books.Title, Authors.Name
FROM Books
JOIN Authors ON Books.AuthorID = Authors.AuthorID;

📤 Expected Output:

Title	Name
1984	George Orwell
Animal Farm	George Orwell
Harry Potter	J.K. Rowling

🔍 This result shows book titles along with the author's name using a JOIN between Books and Authors.

---

🔹 Part B: MongoDB Solution

Note: MongoDB doesn’t support traditional SQL-style JOINs. Instead, you can simulate them using referencing (multiple queries) or the $lookup operator in aggregation pipelines.

✅ 1. Insert Documents

Authors Collection:

db.authors.insertMany([
  { _id: 1, name: "George Orwell" },
  { _id: 2, name: "J.K. Rowling" }
]);

Books Collection:

db.books.insertMany([
  { title: "1984", author_id: 1 },
  { title: "Animal Farm", author_id: 1 },
  { title: "Harry Potter", author_id: 2 }
]);

---

✅ 2. Simulating a JOIN

Step 1: Find Author Document

const author = db.authors.findOne({ name: "George Orwell" });

🔁 Expected Output:

{
  "_id": 1,
  "name": "George Orwell"
}

---

Step 2: Find Books by Author ID

db.books.find({ author_id: author._id });

🔁 Expected Output:

[
  { "title": "1984", "author_id": 1 },
  { "title": "Animal Farm", "author_id": 1 }
]

📝 To match SQL output, you'd display:

Title	Author Name
1984	George Orwell
Animal Farm	George Orwell

---

🔸 Optional: Embedded Model Query

If books were stored like this:

db.books.insertOne({
  title: "1984",
  author: {
    id: 1,
    name: "George Orwell"
  }
});

Then your query:

db.books.find({ "author.name": "George Orwell" });

🔁 Would return documents with both book title and embedded author name directly, simulating a denormalized JOIN.

---

✅ Summary

Concept	SQL (JOIN)	MongoDB (Referencing)
How data is linked	Foreign key + JOIN	Manual field referencing (or embedding)
Queries	One SQL JOIN query	Two MongoDB queries (or use $lookup)
Output	Title + Author from joined tables	Title + Author via multiple reads or embed
Query Complexity	Moderate (JOIN syntax)	Simple (but more steps)

---

Note: MongoDB’s $lookup can perform JOIN-like operations in a single aggregation query, but it's more complex and typically used for reporting or complex analytics.

---

• Move to Part 10: Transactions and Consistency

← Previous Next →

🧪 Practice Assignment: SQL JOINs vs MongoDB Referencing

---

Here's a clear and practical assignment to help readers compare SQL JOINs with MongoDB referencing. This exercise is designed to reinforce how both systems manage relationships through hands-on tasks.

---

🎯 Objective:

Understand how to build and query related data using:

• SQL with JOINs

• MongoDB with manual referencing

---

📚 Scenario:

You’re building a simple Library System with:

• A list of Authors

• A list of Books written by those authors

Each book belongs to one author, and each author can write multiple books (One-to-Many relationship).

---

🔹 Part A: SQL – Using Foreign Keys & JOINs

✅ Step 1: Create Tables

CREATE TABLE Authors (
  AuthorID INT PRIMARY KEY,
  Name VARCHAR(100)
);

CREATE TABLE Books (
  BookID INT PRIMARY KEY,
  Title VARCHAR(150),
  AuthorID INT,
  FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

---

✅ Step 2: Insert Data

INSERT INTO Authors (AuthorID, Name) VALUES
(1, 'George Orwell'),
(2, 'J.K. Rowling');

INSERT INTO Books (BookID, Title, AuthorID) VALUES
(101, '1984', 1),
(102, 'Animal Farm', 1),
(103, 'Harry Potter', 2);

---

✅ Step 3: Write a JOIN Query

Write a SQL query to display all book titles along with their author names.

Example:

SELECT Books.Title, Authors.Name
FROM Books
JOIN Authors ON Books.AuthorID = Authors.AuthorID;

---

🔹 Part B: MongoDB – Manual Referencing

✅ Step 1: Insert Documents

Authors Collection:

db.authors.insertMany([
  { _id: 1, name: "George Orwell" },
  { _id: 2, name: "J.K. Rowling" }
]);

Books Collection:

db.books.insertMany([
  { title: "1984", author_id: 1 },
  { title: "Animal Farm", author_id: 1 },
  { title: "Harry Potter", author_id: 2 }
]);

---

✅ Step 2: Query to Simulate a JOIN

Write MongoDB queries to show book titles and their author names:

Step 1: Get author document

const author = db.authors.findOne({ name: "George Orwell" });

Step 2: Find all books by that author

db.books.find({ author_id: author._id });

---

🔸 Optional Challenge: Try Embedding in MongoDB

Instead of referencing, restructure books with embedded author info:

db.books.insertOne({
  title: "1984",
  author: {
    id: 1,
    name: "George Orwell"
  }
});

Then query:

db.books.find({ "author.name": "George Orwell" });

---

🧠 Reflection Questions

1. Which method (JOIN or referencing) felt more intuitive to you?

2. How does MongoDB referencing differ from SQL JOINs?

3. What trade-offs do you notice in terms of query performance or complexity?

---

• Click Next to view solution with expected outputs

---

Feel free to share your thoughts in the comments!

← Previous Next →

SQL Joins vs NoSQL Referencing: Understanding Data Relationships Easily

🔷 Part 9: Relationships in SQL vs Referencing in NoSQL

---

This is a key concept that helps readers understand how to link data across tables (SQL) or documents (NoSQL), which is essential for real-world applications like e-commerce, school systems, or social networks.

---

📍 Introduction

Most databases store related data: customers and orders, students and classes, or products and reviews.

• In SQL, we use foreign keys and JOINs to link tables.

• In NoSQL (like MongoDB), we use manual referencing or embedding to connect documents.

Understanding these relationship methods is essential for building scalable and maintainable database designs.

---

🔸 1. Relationships in SQL (Using Joins)

SQL supports:

• One-to-One

• One-to-Many

• Many-to-Many
  …through foreign keys and JOINs.

---

✅ Example: Students and Classes (One-to-Many)

📁 Tables:

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(100)
);

CREATE TABLE Classes (
  ClassID INT PRIMARY KEY,
  StudentID INT,
  Subject VARCHAR(50),
  FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

🔍 Fetching Related Data:

SELECT Students.Name, Classes.Subject
FROM Students
JOIN Classes ON Students.StudentID = Classes.StudentID;

➡️ This JOIN connects each student to their class records.

---

🔹 2. Relationships in MongoDB (Referencing)

In NoSQL, there are two ways to handle related data:

✅ A. Manual Referencing (Normalized approach)

// students collection
{
  _id: ObjectId("stu101"),
  name: "Aisha Khan"
}

// classes collection
{
  student_id: ObjectId("stu101"),
  subject: "Math"
}

To retrieve, you need two queries:

const student = db.students.findOne({ name: "Aisha Khan" });
const classes = db.classes.find({ student_id: student._id });

✅ B. Embedding (Denormalized approach)

{
  name: "Aisha Khan",
  classes: [
    { subject: "Math" },
    { subject: "Science" }
  ]
}

➡️ This is faster for reads, but harder to update if the same class is shared across students.

---

🔄 Comparison Table: SQL Joins vs NoSQL Referencing

Feature	SQL (Joins)	MongoDB (Referencing/Embedding)
Relationship type	Foreign keys & JOINs	Manual references or embedded documents
Querying	Single JOIN query	Multiple queries or nested documents
Read performance	May be slower due to joins	Fast if embedded
Update complexity	Centralized & simple	More complex with embedded structures
Data duplication	Minimal	Possible with embedding

---

🧠 Best Practices

✅ Use SQL when:

• Data relationships are complex and frequently queried together

• You want strong referential integrity

✅ Use NoSQL referencing when:

• You need flexibility or horizontal scaling

• Data isn’t always accessed together

---

📌 Quick Summary

• SQL uses foreign keys and JOINs for linking tables.
• NoSQL uses manual referencing or embedding to relate documents.
• Joins are powerful but may affect performance in large datasets.
• Embedding is fast but risks data duplication and update complexity.

---

✅ What’s Next?

In Part 10, we’ll dive into Transactions and Consistency — how SQL ensures ACID compliance, and how NoSQL handles consistency across distributed systems.

---

Click Next for:

•  practice assignment comparing SQL JOINs and MongoDB referencing

← Previous Next →