Joins, Subqueries, Grouping, Views, and Set Operations

Why Multi-Table Querying Matters

Real applications rarely keep all information in one table. Data is separated to reduce redundancy and preserve structure, which means meaningful reporting usually requires combining tables using joins and grouping operations.

Inner, Left, Right, and Full Joins

SELECT o.order_id, c.customer_name
FROM Orders o
INNER JOIN Customers c
    ON o.customer_id = c.customer_id;

An inner join returns matching rows from both sides. Outer joins help preserve unmatched rows from one or both tables depending on the type of analysis required.

Group By and Aggregation

SELECT department_id, COUNT(*) AS employee_count
FROM Employees
GROUP BY department_id;

Aggregation is essential in dashboards, billing summaries, operational reporting, and management analytics.

Subqueries

SELECT name
FROM Students
WHERE student_id IN (
    SELECT student_id
    FROM Enrollments
    WHERE course_id = 101
);

Subqueries are useful, but students should also learn when joins are clearer or more efficient depending on the database engine and query shape.

Views and Abstraction

A view is a virtual table built from a query. Views help simplify repeated logic, enforce controlled access, and provide stable reporting interfaces to business users.

Set Operations

UNION, INTERSECT, and EXCEPT are used to combine or compare result sets. These operations are especially helpful in audit tasks, reconciliation, and advanced reporting.