set-based query
A single query that works on a whole set of rows at once, instead of code that loops and queries row by row.
In more detail
Joins, aggregates and window functions let the database do in one statement what a loop would do with many queries. That cuts round trips and lets the planner choose an efficient route.
Related terms
- N+1 query problem — A performance bug where code runs one query for a list and then one more query per item, so N items cost N+1 queries.
- window function — A calculation across a set of rows related to the current row that, unlike GROUP BY, keeps every input row in the answer.
- join — Combining rows from two tables into one result by pairing rows according to a rule, usually matching keys.
- aggregate function — A function that turns many input rows into one value, such as COUNT, SUM, AVG, MIN or MAX.
Taught in
- Window Functions: Replacing Application Loops — PostgreSQL, Indexing & Relational Data Modeling
- The N Plus One Problem — PostgreSQL, Indexing & Relational Data Modeling