Joins connect entities, subqueries and CTEs shape logic, analytical operators calculate context, and materialized views avoid repeated work.
Combine, test, or exclude matching entities.
Name intermediate logic and organize a complex query.
Calculate ranking, running, and peer-aware values.
Reuse stored results through transparent rewrite.
Inner, outer, semi, anti, and cross joins answer different questions even when they use the same relationship keys.
Keep only pairs that satisfy the join condition.
Also preserve unmatched rows from one or both sides.
Return one side when at least one match exists.
Return one side only when no match exists.
Produce the Cartesian product of both inputs.
Join with ranges or inequalities, often via nested loops.
Broadcast, shuffle, bucket shuffle, and colocate strategies trade flexibility for increasingly strict data placement.
Subqueries embed a relational question; CTEs give intermediate result sets a name and can express recursive hierarchies.
Nest a value, set, or relation inside another expression.
Name a query stage before the main statement.
GROUPING SETS selects exact groupings, ROLLUP adds hierarchical totals, and CUBE generates every combination.
Compute only the explicitly requested grouping combinations.
Add subtotals along an ordered dimension hierarchy.
Compute every grouping combination across dimensions.
Window functions preserve row detail while adding rankings, offsets, running aggregates, and peer-aware calculations.
Alias functions simplify migration, Java and Python UDFs add custom computation, and complex types keep nested values queryable.
Map a compatible name or reusable expression.
Implement scalar, aggregate, window, or table logic.
Use Python logic where ecosystem value offsets overhead.
Query arrays, maps, structs, and JSON without flattening first.
Synchronous materialized views provide strongly consistent single-table acceleration; asynchronous views support complex multi-table results with managed refresh.
Single-table acceleration maintained with base data.
Flexible stored results refreshed by policy.