Module 2 Conclusion: Advanced SELECT, Subqueries, and Views

Ten lessons ago, this module started with a simple premise: basic SELECT statements only get you so far, and real reporting requires summarizing, filtering, comparing against computed values, and packaging the result into something reusable. Along the way, a handful of ideas kept resurfacing in different disguises, GROUP BY's ordering, DISTINCT's null handling, subquery scalarity, all turning out to be the same small set of principles applied in new contexts. This conclusion pulls those threads together rather than simply repeating each lesson in sequence.

The module opened with a question worth repeating: how do you recognize when a request calls for GROUP BY rather than something else? The signal is a request phrased as "for each X, show me some summary of Y." The moment an aggregate function, COUNT, SUM, AVG, MIN, or MAX, needs to summarize each group, GROUP BY is the tool. If the request is simply "show me every unique X" with no summarizing at all, DISTINCT is the more direct choice, covered again later in the module.

Filtering splits cleanly into two clauses depending on timing. WHERE filters individual rows before any grouping happens, which is exactly why WHERE can never reference an aggregate function like SUM() or COUNT(), that value doesn't exist yet at the point WHERE runs. HAVING filters the grouped results after aggregation, which makes it the only clause that can test a condition against an aggregate value. The rule of thumb established early on still holds: if the condition needs to look at an already-summarized value, it belongs after grouping, in HAVING; if it's about the raw row, it belongs before grouping, in WHERE.

One error accounts for the majority of GROUP BY mistakes: every non-aggregated column in the SELECT list has to also appear in the GROUP BY clause, or Oracle raises ORA-00979: not a GROUP BY expression. That single rule, more than any other piece of syntax in this module, is worth committing to memory. Grouping also isn't limited to a single column; a comma-separated list of grouping columns narrows the groups further with each additional column, and the same rule applies at any number of columns, every non-aggregated column named in the SELECT list has to appear in the GROUP BY clause alongside it.

A dedicated lesson corrected a persistent misconception worth restating plainly: GROUP BY does not sort. It groups rows for aggregation and makes no promise about output order. Oracle can implement grouping as either a HASH GROUP BY or a SORT GROUP BY internally, and a query that "always" looked sorted because the optimizer happened to favor the sort-based approach can start returning unordered output the moment the optimizer's cost estimates change, with no change to the SQL text at all. ORDER BY is the only clause that guarantees sequence, and it always runs last, after FROM, WHERE, GROUP BY, HAVING, and the SELECT list have all already been evaluated.

The middle third of this module covered subqueries in depth, and the decision between the two comparison approaches, IN and =, comes down to a single question: how many rows will the subquery return?

IN accepts zero, one, or many rows without complaint, which makes it the safer default whenever a subquery's cardinality isn't structurally guaranteed. = requires a scalar subquery, exactly one row and one column, and fails at runtime with ORA-01427: single-row subquery returns more than one row the moment that guarantee breaks, often long after the query was written, once the underlying data simply grows. The safest path to a genuinely scalar subquery is building it around an aggregate function; MAX, MIN, COUNT, SUM, and AVG all mechanically collapse their input to one value regardless of how many rows they scanned, so a query like WHERE salary = (SELECT MAX(salary) FROM employees) can never fail with ORA-01427, no matter how the data changes.

Subqueries evaluate from the inside out, the same principle as resolving nested parentheses in a math expression: the innermost query runs first, and its result gets substituted into the query surrounding it, one layer at a time. Every subquery is either correlated or non-correlated. A non-correlated subquery runs once, independent of the outer query. A correlated subquery references a column from the outer query's current row and has to re-run once per outer row, which is exactly why correlated subqueries tend to cost more, a difference that goes from a rounding error to the gap between an instant query and a timeout as the outer table grows. EXISTS and NOT EXISTS are themselves almost always written as correlated subqueries, checking only whether a match exists rather than comparing specific values.

IN and = aren't the whole picture. IN supports comparing multiple columns at once against a subquery returning the same number of columns, using a row constructor. Nested subqueries resolve the same inside-out way at any depth, though readability degrades well before any real limit is reached; three or more levels of nesting is a reasonable signal to restructure as a WITH clause (a common table expression) instead, so the logic reads top to bottom rather than requiring the reader to find the innermost parentheses first.

Not every subquery is the best tool for the job. A subquery functioning purely as a filter list, the way IN is commonly used, can often be rewritten as a join, which lets the optimizer use indexes on the join condition directly and frequently completes the work in a single pass rather than two separate scans. The one place subqueries remain close to essential is finding rows that are not in a list, something genuinely awkward to express as a join.

Pulling the module's subquery guidance into a single decision path:

1. Does the comparison need a set of rows to match against, or one computed value? A set calls for IN; a single guaranteed value calls for =.
2. Does the subquery reference a column from the outer query? If so, it's correlated, and it runs once per outer row rather than once overall; budget for that cost, or look for a JOIN or GROUP BY equivalent.
3. Could the subquery's column contain a NULL, combined with NOT IN? If so, NOT EXISTS avoids the silent empty-result-set failure entirely.
4. Is the subquery just building a filter list from an otherwise unrelated table? A join equivalent is worth testing; it's frequently the more efficient shape for exactly that pattern.
5. Is the query three or more levels deep in nested subqueries? A WITH clause restructures the same logic to read in execution order instead of inside out.

DISTINCT and GROUP BY solve related but distinct problems: GROUP BY partitions rows for aggregation, DISTINCT eliminates duplicate rows from a result set with no aggregation involved at all. The two can produce identical output when a GROUP BY query has no aggregate functions in its SELECT list, which is exactly why beginners conflate them, but the underlying purposes stay separate.

DISTINCT applies to the entire selected row as a unit, not to each column independently; adding an already-unique column like a primary key to a DISTINCT query defeats it entirely, since no two rows can ever match on that column. DISTINCT also sits inside aggregate functions, COUNT(DISTINCT expr) being the most common form, answering "how many unique values" rather than "how many rows." For large tables where an exact count isn't necessary, Oracle AI Database 26ai's APPROX_COUNT_DISTINCT trades a small amount of precision for significantly faster performance, the right tool for a dashboard metric rather than a number headed into a financial report.

One habit worth catching: reaching for DISTINCT the instant a query returns more rows than expected after adding a join, without asking why those extra rows appeared. That's usually a join producing a one-to-many row multiplication, and DISTINCT papering over the symptom rather than the underlying join being the wrong shape for the question being asked.

The module's final lesson tied everything preceding it into a single idea: a view is a saved query that becomes a stable, reusable presentation layer, and the aggregation, filtering, and deduplication techniques covered throughout this module are frequently exactly what ends up living inside a view's definition. A dashboard view built on GROUP BY and COUNT(DISTINCT ...) isn't a new topic; it's this module's techniques, permanently saved under a name other consumers can query without reconstructing the logic themselves.

WITH CHECK OPTION closes a gap that's easy to miss: without it, an UPDATE through a filtered view can silently push a row outside the view's own condition, making it disappear from later queries with no error to signal anything unusual happened. Not every view is updatable either; a view built around GROUP BY, DISTINCT, or aggregate functions is a read-only presentation layer by nature, since there's no sensible way to translate a write against a summarized value back into a change on the underlying detail rows.

Materialized views trade data freshness for read speed by physically storing a view's result rather than recomputing it on every query, worth reaching for once a specific view is demonstrated to be slow under real load, not as a default applied preemptively. And because a view's definition is stored as queryable text in Oracle's data dictionary (ALL_VIEWS, DBA_VIEWS, USER_VIEWS), dependency tracking and searching for which views reference a given table are both things ordinary SQL can answer directly, no external tooling required.

One idea surfaced in three completely different contexts across this module, and it's worth naming explicitly now that all three examples are visible at once: NULL doesn't behave like an ordinary value in a comparison, and every one of the surprising behaviors covered in this module traces back to the same root cause.

First, NOT IN against a subquery that might return a NULL: the entire comparison can silently return zero rows for every outer row, not an error, just an empty result set that looks like a data problem rather than a query problem. This happens because NOT IN is logically a chain of ANDed inequality comparisons, and the moment any one of them involves a NULL, the whole chain evaluates to unknown rather than true. NOT EXISTS sidesteps this entirely, since it checks for the existence of a match rather than comparing against a list that might contain an unknown value.

Second, a scalar subquery that returns zero rows: the comparison evaluates to NULL, and comparing anything to NULL produces an unknown result, not a definite FALSE. In a plain WHERE clause, an unknown result gets excluded the same way a FALSE would, which makes the distinction easy to miss, until that same condition gets wrapped in NOT. NOT applied to an unknown result stays unknown; it doesn't flip to TRUE, which means negating a condition that returned zero rows because of a NULL comparison still returns zero rows, not the full result set someone might expect.

Third, DISTINCT's deliberate exception to this whole pattern: while NULL never equals another NULL under ordinary = comparison, DISTINCT explicitly treats every NULL in a column as identical for the purpose of duplicate elimination, collapsing several NULL rows down to a single one. This is the one place in the module where NULL's usual behavior is intentionally overridden rather than causing a surprise.

The takeaway that spans all three: whenever a column's nullability isn't guaranteed, treat every comparison involving that column, NOT IN, a scalar subquery destined for =, or a DISTINCT count, as a place worth double-checking, rather than trusting that the comparison behaves the way ordinary two-valued logic would suggest.

A second theme ran through two separate lessons in this module and is worth naming as one idea rather than two: something that happens to work today because of how the optimizer chose to execute a query is not the same as something the SQL language actually guarantees.

GROUP BY makes no promise about output order. Whether Oracle implements a given GROUP BY as a HASH GROUP BY or a SORT GROUP BY is a cost-based decision the optimizer can change between executions of the exact same query, and a SORT GROUP BY's output happening to look sorted is a side effect of its internal mechanism, not a contract. DISTINCT carries the identical caveat for the identical reason: its duplicate-elimination mechanism can be hash-based or sort-based, and any apparent ordering in its output is just as incidental. In both cases, the fix is the same: add an explicit ORDER BY the moment a specific order actually matters, rather than relying on what a particular execution plan happened to produce.

Several lessons in this module surfaced genuine 26ai-specific enhancements worth collecting into one list, since they're scattered individually across earlier pages:

• GROUP BY ALL automatically groups by every non-aggregated expression in the SELECT list, removing the need to repeat that list a second time in the GROUP BY clause.
• Grouping by column alias or ordinal position is now supported in GROUP BY, HAVING, and their ROLLUP/CUBE/GROUPING SETS extensions, though positional grouping specifically requires the group_by_position_enabled parameter to be explicitly turned on; it's disabled by default.
• The FILTER clause applies a condition directly to the values an aggregate function considers, producing several differently-conditioned aggregates in a single pass without a subquery or a CASE expression buried inside SUM().
• APPROX_COUNT_DISTINCT trades a small amount of precision for substantially faster performance than COUNT(DISTINCT ...) on large tables, suited to dashboard-grade metrics rather than figures that need to be exact.
• Large literal IN lists benefit from optimizer-level vector in-list processing, a performance improvement that requires no change to how the IN clause itself is written.

None of these change what any of the underlying SQL constructs fundamentally do; they make existing patterns faster to write or faster to execute. The vocabulary and mental models covered throughout this module, what GROUP BY groups, what DISTINCT eliminates, what a scalar subquery requires, apply identically whether or not any of these newer conveniences are in use.

A handful of facts from across this module are worth having in one place:

Situation	What Happens
Non-aggregated column missing from GROUP BY	ORA-00979: not a GROUP BY expression
Scalar subquery (=) returns more than one row	ORA-01427: single-row subquery returns more than one row
Scalar subquery (=) returns zero rows	Comparison is unknown; row excluded, not an error
NOT IN subquery contains a NULL	Entire result set can silently return zero rows
DISTINCT encounters multiple NULLs	All collapse into a single NULL row
GROUP BY or DISTINCT output order	Never guaranteed without an explicit ORDER BY
= subquery vs. IN subquery	= requires exactly one row; IN accepts zero, one, or many
View built on GROUP BY, DISTINCT, or aggregates	Read-only; not updatable through INSERT/UPDATE/DELETE

Looking Ahead

This module moved from summarizing rows, to filtering both raw and grouped results, to comparing values against computed subqueries, to eliminating duplicates, and finally to packaging all of it into views other people and tools can rely on without reconstructing the logic themselves. Every one of those techniques compounds: a well-built view is often just a GROUP BY query, a DISTINCT count, or a correlated subquery from earlier in this module, given a name and a stable home. The syntax in each lesson matters less on its own than the decision-making it was built to support, when to group versus deduplicate, when a subquery needs to be scalar versus a set, when NULL changes the answer, and when something that looks like a guarantee is really just today's execution plan. Carrying that judgment forward is what turns these ten lessons into queries that hold up in practice, not just on the page.

Click the link below to test your proficiency in advanced uses of the SELECT statement.
SQL Select - Quiz