Mastering SQL: The Ultimate Guide to Querying and Managing Relational Databases

📘 Chapter 4: Joins and Subqueries

🧠 Introduction

Modern applications often rely on multiple related tables to store data efficiently. To query across them, SQL provides two powerful tools:

• Joins – Combine rows from multiple tables
• Subqueries – Use a query within another query

This chapter explores how to use both tools to retrieve meaningful, cross-referenced information from relational databases.

🔗 1. Joins in SQL

A join lets you combine data from two or more tables based on a related column, typically a foreign key.

🔹 Types of Joins

✅ Example Database Structure

Customers

Orders

🔍 2. INNER JOIN

Returns rows only where there is a match between tables.

sql

SELECT Customers.Name, Orders.Product, Orders.Amount

FROM Customers

INNER JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

📋 Output

⬅️ 3. LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table and matched rows from the right. Unmatched rows get NULL.

sql

SELECT Customers.Name, Orders.Product

FROM Customers

LEFT JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

📋 Output

➡️ 4. RIGHT JOIN

Like LEFT JOIN, but includes all rows from the right table.

sql

SELECT Orders.Product, Customers.Name

FROM Orders

RIGHT JOIN Customers

ON Orders.CustomerID = Customers.CustomerID;

🔄 5. FULL OUTER JOIN

Returns all records from both tables. If no match exists, returns NULL.

sql

SELECT Customers.Name, Orders.Product

FROM Customers

FULL OUTER JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

Note: Not supported in some DBMS like MySQL (use UNION instead).

♻️ 6. SELF JOIN

Joins a table to itself. Useful for hierarchical or comparative data.

✅ Example: Employees Table

sql

SELECT E.Name AS Employee, M.Name AS Manager

FROM Employees E

JOIN Employees M

ON E.ManagerID = M.EmpID;

🔄 7. Subqueries in SQL

A subquery is a SQL query nested inside another query.

📚 Types of Subqueries

🧩 8. Subquery in WHERE Clause

sql

SELECT Name FROM Customers

WHERE CustomerID IN (

    SELECT CustomerID FROM Orders WHERE Amount > 500

);

Returns customers who placed orders above 500.

🧮 9. Subquery in SELECT Clause

sql

SELECT Name,

    (SELECT COUNT(*) FROM Orders WHERE Orders.CustomerID = Customers.CustomerID) AS TotalOrders

FROM Customers;

🔃 10. Correlated Subqueries

These refer to outer query values for each row.

sql

SELECT Name FROM Customers C

WHERE EXISTS (

    SELECT * FROM Orders O WHERE O.CustomerID = C.CustomerID

);

🔀 11. Subquery vs JOIN – When to Use

🧪 Practice Exercise

sql

-- Create tables

CREATE TABLE Departments (

    DeptID INT PRIMARY KEY,

    DeptName VARCHAR(50)

);

CREATE TABLE Employees (

    EmpID INT,

    Name VARCHAR(50),

    Salary INT,

    DeptID INT,

    FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)

);

-- Insert sample data

INSERT INTO Departments VALUES (1, 'HR'), (2, 'Engineering');

INSERT INTO Employees VALUES

(1, 'Alice', 60000, 2),

(2, 'Bob', 55000, 2),

(3, 'Carol', 50000, 1);

-- JOIN Example: Employees and Departments

SELECT E.Name, D.DeptName

FROM Employees E

JOIN Departments D ON E.DeptID = D.DeptID;

-- Subquery: Highest salary

SELECT Name FROM Employees

WHERE Salary = (SELECT MAX(Salary) FROM Employees);

📚 Summary Table