Joins, Subqueries, Grouping, Views, and Set Operations
Go beyond single-table queries and learn how relational power emerges when data from multiple tables is combined and summarized.
Inside this chapter
- Why Multi-Table Querying Matters
- Inner, Left, Right, and Full Joins
- Group By and Aggregation
- Subqueries
- Views and Abstraction
- Set Operations
Series navigation
Study the chapters in order for the clearest path from database fundamentals and SQL to transactions, indexing, recovery, distributed systems, tuning, and advanced DBMS engineering understanding. Use the navigation at the bottom to move smoothly across the full tutorial series.
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.