Complex Views

From Simple to Complex

Where a simple view (19.1) is a filter/projection over one table, a complex view draws its result from multiple tables via joins, uses aggregate functions, GROUP BY, DISTINCT, UNION, or subqueries. The syntax is identical — CREATE VIEW ... AS <query> — but the query behind it is a full-fledged, multi-table report.

Example: Department Summary View

sql
CREATE VIEW department_summary AS
SELECT
    d.id                    AS department_id,
    d.name                  AS department_name,
    COUNT(e.id)             AS headcount,
    AVG(e.salary)           AS avg_salary,
    d.budget
FROM departments d
LEFT JOIN employees e ON e.department = d.id
GROUP BY d.id, d.name, d.budget;

Querying it is still simple for the consumer:

sql
SELECT department_name, headcount, avg_salary
FROM department_summary
WHERE avg_salary > 60000;

The complexity — the JOIN, the GROUP BY, the aggregate functions — is hidden entirely behind the view name. This is the real value proposition of complex views: they let analysts and applications ask simple questions ("which departments have average salary over 60000?") without knowing anything about how employees and departments relate.

Subqueries and Set Operations

Complex views can also embed correlated subqueries or UNION:

sql
CREATE VIEW above_avg_earners AS
SELECT e.id, e.name, e.department, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e.department
);

This view lists employees earning more than their own department's average — a computation that would be awkward to inline in every consuming query, but trivial to reuse once wrapped in a view.

The Contrast With Simple Views

That last row is the practical consequence that matters most: once a view aggregates or joins, the database can no longer trace a single output row back to a single, unambiguous underlying row, which is precisely why complex views are generally not directly updatable.

Edge Cases

• NULLs from outer joins: department_summary uses a LEFT JOIN, so a department with zero employees still appears with headcount = 0 (via COUNT(e.id), which ignores NULLs) rather than being dropped, as an INNER JOIN would do.
• Performance: every query against a complex view re-executes the full join/aggregation. Querying department_summary for one department still computes the aggregate for all departments internally unless the optimizer can push the WHERE predicate down — this is a common reason to consider a materialized view (19.6) for expensive complex views that are queried often but change rarely.
• Nested complex views (a complex view built on another view) can compound this cost — see 19.8.

Key Takeaways / Interview Q&A

Q: Give a one-line definition of a complex view. A: A stored query spanning multiple tables and/or using aggregation, GROUP BY, DISTINCT, or set operations.

Q: Why is a LEFT JOIN often preferred over INNER JOIN in a summary view like department_summary? A: So that rows with no matching child records (e.g., departments with no employees) are still represented, typically with 0/NULL aggregate values, instead of silently vanishing.

Q: Can a complex view itself be joined with another table in a query? A: Yes — a view can be used anywhere a table can, including as one side of a JOIN in an outer query.