Vigneshwaralingam

Posted on Mar 25, 2025

SQL Day 3: Mastering Joins and Subqueries.

📌 Introduction

Today, I explored Joins and Subqueries in SQL, which are essential for handling complex data relationships across multiple tables. I learned different types of joins, when to use subqueries, and when not to use them.

📝 What I Learned Today

1️⃣ Why Do We Use Joins?

Joins are used to combine data from multiple tables based on related columns. Instead of writing multiple queries, joins allow us to fetch everything in a single query efficiently.

🔹 Example Scenario

Consider two tables:

students (id, name, age, dept_id)
departments (dept_id, dept_name)

If I want to display each student's name along with their department name, I can use a join instead of running separate queries.

2️⃣ Types of Joins in SQL

Join Type	Description	Example Use Case
INNER JOIN	Returns only matching records from both tables	Fetch students who belong to an existing department
LEFT JOIN	Returns all records from the left table and matching ones from the right table	Fetch all students, even if they don't have a department
RIGHT JOIN	Returns all records from the right table and matching ones from the left table	Fetch all departments, even if no students are assigned
FULL JOIN	Returns all records from both tables, filling unmatched rows with NULL	Show all students and all departments, even if some don’t match
CROSS JOIN	Returns the Cartesian product of both tables (every row pairs with every row)	Used in scenarios like seating arrangements
SELF JOIN	Joins a table with itself	Find employees and their managers in the same table

3️⃣ INNER JOIN (or just JOIN)

Returns only matching records from both tables.

SELECT students.name, departments.dept_name  
FROM students  
INNER JOIN departments ON students.dept_id = departments.dept_id;

✅ Use when we only need records with matches in both tables.

4️⃣ LEFT JOIN (or LEFT OUTER JOIN)

Returns all records from the left table and matching records from the right table. If there's no match, NULL is returned.

SELECT students.name, departments.dept_name  
FROM students  
LEFT JOIN departments ON students.dept_id = departments.dept_id;

✅ Use when we want all students, even if some don’t belong to a department.

5️⃣ RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all records from the right table and matching records from the left table.

SELECT students.name, departments.dept_name  
FROM students  
RIGHT JOIN departments ON students.dept_id = departments.dept_id;

✅ Use when we want all departments, even if some have no students.

6️⃣ FULL JOIN (or FULL OUTER JOIN)

Returns all records from both tables, filling unmatched rows with NULL.

SELECT students.name, departments.dept_name  
FROM students  
FULL JOIN departments ON students.dept_id = departments.dept_id;

✅ Use when we want to see both students and departments, including unmatched records.

7️⃣ CROSS JOIN

Creates every possible combination of rows from both tables.

SELECT students.name, departments.dept_name  
FROM students  
CROSS JOIN departments;

✅ Use when we need to compare all combinations (e.g., seating arrangements, testing scenarios).

8️⃣ SELF JOIN

Joins a table with itself, useful for hierarchical data like employees and managers.

SELECT A.name AS Employee, B.name AS Manager  
FROM employees A  
JOIN employees B ON A.manager_id = B.id;

✅ Use when comparing data within the same table.

9️⃣ When to Use Subqueries Instead of Joins?

Subqueries are queries inside another query. They are useful when we need intermediate results for filtering or calculations.

🔹 Example: Finding Students from the Largest Department

SELECT name FROM students  
WHERE dept_id = (SELECT dept_id FROM departments ORDER BY student_count DESC LIMIT 1);

✅ Use when a query depends on another query’s result.

🔟 When Not to Use Joins or Subqueries?

❌ When NOT to Use Joins

When performance is a concern (Joins slow down if tables are large).
When data is denormalized (i.e., everything is already in one table).
When columns lack proper indexing (slows down joins).

❌ When NOT to Use Subqueries

When a join can achieve the same result more efficiently.
When multiple subqueries make execution slower.
When subqueries return large data sets (causing performance issues).

✅ Instead of this subquery:

SELECT name FROM students WHERE dept_id IN (SELECT dept_id FROM departments);

We can use a JOIN instead (better performance):

SELECT students.name FROM students  
JOIN departments ON students.dept_id = departments.dept_id;

🎯 Key Takeaways

Joins combine data from multiple tables efficiently.
INNER JOIN: Matches records in both tables.
LEFT JOIN: Includes all records from the left table, with NULL for unmatched rows.
RIGHT JOIN: Includes all records from the right table, with NULL for unmatched rows.
FULL JOIN: Includes all records from both tables.
Use subqueries when needed but avoid them if joins can do the job faster.
Always optimize queries for performance, especially when handling large datasets.

Stay tuned for Day 4! 🚀✨

DEV Community