Module 6: SQL Joins, Subqueries & Views

A JOIN combines columns from two or more tables based on a related column between them. Relational database power relies directly on the ability to query normalized relations using joins.

Join Syntaxes

-- 1. INNER JOIN: Retrieve employees and their department details
SELECT e.emp_id, e.name, d.dept_name
FROM Employees e
INNER JOIN Departments d ON e.dept_id = d.dept_id;

-- 2. LEFT JOIN: Retrieve all employees, even if not yet assigned to any department
SELECT e.emp_id, e.name, d.dept_name
FROM Employees e
LEFT JOIN Departments d ON e.dept_id = d.dept_id;
-- If dept_id is null in employee, d.dept_name will evaluate to NULL.

Beyond standard foreign-key joins, SQL provides specialized join operations for cartesian spaces, automatic schema matching, and hierarchical self-referencing relationships.

1. CROSS JOIN (Cartesian Product)

Produces the Cartesian product of two tables. Every row from table $A$ is paired with every row from table $B$. If $A$ has 10 rows and $B$ has 20 rows, the result contains $10 imes 20 = 200$ rows.

-- Generate all possible color and size combinations:
SELECT colors.name, sizes.label 
FROM Colors 
CROSS JOIN Sizes;

2. NATURAL JOIN (Use With Caution!)

Joins two tables based on all columns that share the exact same column name and data type. It automatically suppresses duplicate join columns in the result.

Production Hazard: If someone adds an unrelated column named created_at or status to both tables, the query silently changes its join condition and returns empty or corrupted results!

3. SELF JOIN

A table is joined with itself. Crucial for modeling hierarchical or recursive data, such as employees and their managers, or graph adjacency lists.

-- Display each employee alongside their direct manager's name:
SELECT e.emp_name AS Employee, 
       COALESCE(m.emp_name, 'Top Executive / CEO') AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.manager_id = m.emp_id;

A Subquery is an SQL query nested inside another SELECT, INSERT, UPDATE, or DELETE statement. Subqueries can be independent or correlated.

Code Comparison: Independent vs Correlated

-- 1. Independent Subquery: Find employees earning more than the company average
SELECT emp_name, salary 
FROM Employees 
WHERE salary > (SELECT AVG(salary) FROM Employees);

-- 2. Correlated Subquery: Find employees earning more than their own department average
SELECT e.emp_name, e.salary, e.dept_id
FROM Employees e
WHERE e.salary > (
    SELECT AVG(d.salary) 
    FROM Employees d 
    WHERE d.dept_id = e.dept_id -- References outer alias 'e'
);

Database Views

A View is a virtual table defined by an underlying SQL query. It does not store raw tuples physically (unless it is a Materialized View); instead, the database generates the view dynamically whenever queried.

• Security: Restricts user access to specific columns (e.g., hiding password hashes and salaries from general analysts).
• Query Simplification: Encapsulates monstrous 5-table joins into a clean, reusable virtual relation.