Study guide
3.3 Querying, Joining & Aggregating Data
PostgreSQL Associate Exam
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Exam Overview & PostgreSQL Foundation
1.1 EDB PostgreSQL Associate Certification Overview 1.2 The PostgreSQL Relational Model & Architecture Principles 1.3 Study Strategies, Lab Setup & Exam-Day Execution
2 Chapter 2: PostgreSQL Architecture & Installation
2.1 The PostgreSQL Server Process Model 2.2 Database Clusters & Cluster Initialization (initdb) 2.3 Filesystem Layout, Service Control & Startup/Shutdown 2.4 Server Configuration Parameters
3 Chapter 3: Demonstrating SQL Knowledge
3.1 Relational Data Types & Table Design 3.2 Integrity Constraints & Data Validation 3.3 Querying, Joining & Aggregating Data 3.4 DML Operations, Transaction Control & MVCC Mechanics
4 Chapter 4: PostgreSQL Administrative Tools
4.1 Interactive Command-Line Mastery with psql 4.2 GUI Administration with pgAdmin 4.3 Client & Server Command-Line Utilities 4.4 System Catalogs & Information Schema
5 Chapter 5: Core Database Administration & Security
5.1 Managing Databases, Schemas & Tablespaces 5.2 Roles, Privileges & Object Ownership 5.3 Client Authentication & pg_hba.conf Architecture
6 Chapter 6: Routine Maintenance & Data Operations
6.1 The PostgreSQL Vacuuming Engine & MVCC Space Reclamation 6.2 Analyzing Query Statistics & Reindexing 6.3 High-Performance Data Loading & Data Movement
7 Chapter 7: Backup and Recovery Strategies
7.1 Logical Backups with pg_dump & pg_dumpall 7.2 Restoring Logical Backups 7.3 Physical Base Backups with pg_basebackup 7.4 Write-Ahead Logging & Point-in-Time Recovery
8 Chapter 8: High Availability & Replication
8.1 Physical Streaming Replication Architecture 8.2 Synchronous vs. Asynchronous Replication 8.3 Replication Slots & Standby Management 8.4 Failover, Switchover & High-Availability Design
9 Chapter 9: Monitoring, Diagnostics & Performance Tuning
9.1 Cumulative Statistics & Activity Monitoring 9.2 Query Execution Plans & Index Optimization 9.3 Server Logging Configuration & Autovacuum Tuning 9.4 Server Memory Sizing & Core Performance Parameters
3.3 Querying, Joining & Aggregating Data
Key Takeaways Explain This Topic AI
PostgreSQL processes SQL statements through a strict logical lifecycle: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT/OFFSET.
Equality comparisons against NULL (e.g., col = NULL) evaluate to UNKNOWN and silently discard matching rows; relational queries must use IS NULL, IS NOT NULL, or the null-safe comparison operator IS NOT DISTINCT FROM.
PostgreSQL's ORDER BY clause sorts NULL values at the very top (NULLS FIRST) for descending sorts and at the very bottom (NULLS LAST) for ascending sorts by default, which can be explicitly controlled using NULLS FIRST or NULLS LAST.
The HAVING clause filters aggregated groups after the GROUP BY reduction stage, whereas the WHERE clause filters individual base tuples before aggregation occurs.
The PostgreSQL-specific DISTINCT ON (expressions) construct retains the first row of each group based on a specified sort order, requiring that the leftmost ORDER BY expressions match the DISTINCT ON expressions.
Last updated: September 2026
3.3 Querying, Joining & Aggregating Data
Note
Exam Blueprint Focus : EDB lists "SQL Queries and Data Handling" as a required competency for this certification (it publishes no percentage weight for it), and querying is the largest single body of testable syntax in this guide. Candidates must demonstrate deep fluency in SELECT projections, three-valued boolean filtering ( WHERE ), pattern matching ( LIKE , ILIKE , and POSIX regular expressions), join behaviors ( INNER , LEFT , RIGHT , FULL , CROSS ), aggregate computations ( COUNT(*) , COUNT(col) , SUM , AVG ), GROUP BY grouping rules, HAVING filters, and PostgreSQL's unique DISTINCT ON feature.
Executing high-performance relational queries requires an understanding of SQL's logical execution pipeline. While a query is written starting with SELECT , the database engine processes clauses in an entirely different sequence: identifying candidate tables, applying row filters, aggregating partitions, filtering summary groups, projecting columns, eliminating duplicates, sorting, and slicing paginated windows.
Logical Query Execution Lifecycle
To predict query results and write optimal SQL, you must understand the engine's logical processing sequence: 1. FROM & JOINs ──> Identifies candidate tables and builds joined tuple streams
- 2. WHERE ──> Filters base input rows using boolean predicate expressions
- 3. GROUP BY ──> Reduces rows into partition buckets sharing group keys
- 4. HAVING ──> Filters aggregated bucket summaries (cannot use column aliases)
- 5. SELECT ──> Evaluates column expressions, window functions, and scalar subqueries
- 6. DISTINCT ──> Deduplicates projected row sets (or evaluates DISTINCT ON)
- 7. ORDER BY ──> Sorts remaining tuples (supports NULLS FIRST / NULLS LAST)
- 8. LIMIT / OFFSET ──> Slices and offsets the final result set for client transmission
- Filtering & Pattern Matching
- Three-Valued Logic and NULL Comparisons
In SQL, boolean logic evaluates to one of three states: TRUE , FALSE , or UNKNOWN . All comparison operators ( = , <> , != , < , > , <= , >= ) evaluate to UNKNOWN when either operand is NULL : -- BROKEN: This query returns ZERO rows, even if rows have status = NULL!
SELECT * FROM orders WHERE status = NULL;
- - CORRECT: Relational null-testing operators
- SELECT * FROM orders WHERE status IS NULL;
- SELECT * FROM orders WHERE status IS NOT NULL;
- - NULL-Safe Equality (Treats two NULLs as equal, evaluates to TRUE or FALSE)
- SELECT * FROM orders WHERE status IS NOT DISTINCT FROM 'shipped';
- Pattern Matching: LIKE , ILIKE , and POSIX Regular Expressions
PostgreSQL provides three tiers of text pattern matching:
LIKE : Standard SQL pattern matching using % (matches zero or more characters) and _ (matches exactly one character). Case-sensitive.
ILIKE : PostgreSQL-specific extension providing case-insensitive pattern matching using % and _ .
POSIX Regular Expressions: Powerful regular expression pattern matching supporting character classes, quantifiers, and alternation:
~ : Matches regular expression, case-sensitive.
~* : Matches regular expression, case-insensitive.
!~ : Does not match regular expression, case-sensitive.
!~* : Does not match regular expression, case-insensitive. -- Case-insensitive search matching 'PostgreSQL', 'postgresql', 'POSTGRESQL'
SELECT * FROM articles WHERE title ILIKE '%postgres%';
- - POSIX Regex: Find email addresses ending in .org or .edu (case-insensitive)
- SELECT email FROM users WHERE email ~* '^[a-z0-9._%+-]+@[a-z0-9.-]+\.(org|edu)$';
- Sorting & Pagination: ORDER BY , LIMIT , and OFFSET
- Sorting and Default NULL Positioning
The ORDER BY clause sorts output rows in ascending ( ASC ) or descending ( DESC ) order. A common exam trap involves the default positioning of NULL values in PostgreSQL:
Ascending ( ASC ) default : NULLS LAST (NULLs appear at the very bottom of the result set).
Descending ( DESC ) default : NULLS FIRST (NULLs appear at the very top of the result set).
Explicit Control : You can override the default using NULLS FIRST or NULLS LAST explicitly: -- Top earners first, but keep employees with unknown (NULL) salaries at the bottom
- SELECT employee_name, salary
- FROM employees
- ORDER BY salary DESC NULLS LAST;
- Pagination Mechanics
PostgreSQL supports result-set windowing using LIMIT and OFFSET or the SQL:2008 standard FETCH FIRST : -- Page 3 (Rows 21 through 30)
- SELECT * FROM audit_logs
- ORDER BY event_time DESC
- LIMIT 10 OFFSET 20;
- - SQL Standard equivalent
- SELECT * FROM audit_logs
- ORDER BY event_time DESC
- OFFSET 20 ROWS
- FETCH FIRST 10 ROWS ONLY;
Tip
Performance Consideration for Large Offsets : OFFSET 1000000 LIMIT 10 forces the PostgreSQL engine to scan, sort, and process 1,000,010 rows before discarding the first 1,000,000 and returning 10. For high-volume pagination, use Keyset Pagination (Seek Method) using an indexed column: WHERE event_id < :last_seen_id ORDER BY event_id DESC LIMIT 10 .
Relational Join Mechanics
Joins combine tuples from two or more relations based on matching column predicates. INNER JOIN: LEFT OUTER JOIN: FULL OUTER JOIN:
- [ Table A ] [ Table B ] [ Table A ] [ Table B ] [ Table A ] [ Table B ]
- ├──[ Matched ]──┤ ├──[ Matched ]──┤ ├──[ Matched ]──┤
- └──[ Unmatched A ] └──[ Unmatched A ]
- └──[ Unmatched B ]
- 1. INNER JOIN
Returns only the tuples where the join condition evaluates to TRUE across both tables. Unmatched rows from either table are discarded. SELECT c.name, o.order_id, o.amount
- FROM customers c
- INNER JOIN orders o ON c.id = o.customer_id;
- 2. LEFT OUTER JOIN (or LEFT JOIN )
Preserves all rows from the left table. If a left-table row finds matching tuples in the right table, the matched data is combined. If no match exists, all projected columns from the right table are filled with NULL . -- Find customers who have NEVER placed an order (Anti-Join pattern)
- SELECT c.name
- FROM customers c
- LEFT JOIN orders o ON c.id = o.customer_id
- WHERE o.order_id IS NULL;
- 3. RIGHT OUTER JOIN and FULL OUTER JOIN
RIGHT OUTER JOIN : Preserves all rows from the right table, populating unmatched left columns with NULL .
FULL OUTER JOIN : Preserves all rows from both tables. Matched rows are joined together; unmatched rows from either side are returned with NULL for the opposite table's columns.
4. CROSS JOIN
Produces the Cartesian product of both tables, combining every row in table A with every row in table B ( M × N M \times N M × N rows). A CROSS JOIN takes no ON condition: -- Generates all shirt size and color combinations
- SELECT s.size_name, c.color_name
- FROM sizes s
- CROSS JOIN colors c;
- Join Syntax: ON vs. USING
When join columns share the exact same column name across both tables, you can use the shorthand USING (column_name) syntax. Unlike ON a.dept_id = b.dept_id which retains two independent columns in the internal projection, USING (dept_id) combines them into a single column: SELECT dept_id, e.name, d.name
- FROM employees e
- INNER JOIN departments d USING (dept_id);
- Aggregations, GROUP BY , and HAVING
Aggregations collapse multiple input rows into a single summary tuple.
Standard Aggregate Functions
COUNT(*) : Counts the total number of input rows, including rows containing NULL values or duplicates.
COUNT(column) : Counts the number of rows where column is NOT NULL .
SUM(column) / AVG(column) : Computes the sum or arithmetic mean of non-null values. If all input values are NULL , SUM returns NULL (not zero).
MIN(column) / MAX(column) : Returns the minimum or maximum non-null value.
The GROUP BY Rule
When using GROUP BY , every column in the SELECT projection list that is not wrapped in an aggregate function must appear in the GROUP BY clause (unless the table's primary key is in the GROUP BY , which functionally determines the remaining columns in PostgreSQL): -- Invalid: e.department_id is missing from GROUP BY
-- SELECT department_id, employee_title, AVG(salary) FROM employees GROUP BY department_id; -- ERROR!
- - Valid grouping
- SELECT department_id, employee_title, AVG(salary)
- FROM employees
- GROUP BY department_id, employee_title;
- WHERE vs. HAVING Filtering
A classic certification topic is distinguishing WHERE from HAVING :
WHERE : Evaluated before grouping and aggregation. It filters individual input rows. It cannot reference aggregate functions ( WHERE COUNT(*) > 5 is a syntax error).
HAVING : Evaluated after grouping and aggregation. It filters aggregated summary groups. It can reference aggregate functions and grouping columns. SELECT department_id, COUNT(*) AS active_employees, AVG(salary) AS avg_sal
- FROM employees
- WHERE status = 'ACTIVE' -- Filters base rows BEFORE aggregation
- GROUP BY department_id
- HAVING COUNT(*) >= 5 AND AVG(salary) > 75000; -- Filters groups AFTER aggregation
- Deduplication: DISTINCT vs. DISTINCT ON
- Standard SQL DISTINCT
The standard DISTINCT clause evaluates the entire projected SELECT list and removes duplicate rows: SELECT DISTINCT department_id, job_level FROM employees;
PostgreSQL-Specific DISTINCT ON (expressions)
PostgreSQL provides a proprietary, highly efficient extension: DISTINCT ON (expr1, expr2, ...) :
It retains only the first row of each set of rows where the given expressions evaluate to equal.
Because "the first row" is unpredictable unless rows are sorted, DISTINCT ON must be paired with an ORDER BY clause .
The Golden Rule of DISTINCT ON : The expressions in DISTINCT ON (...) must match the leftmost expressions in the ORDER BY clause! -- Scenario: Retrieve the single highest-paid employee for each department
- SELECT DISTINCT ON (department_id)
- department_id,
- employee_name,
- salary,
- hire_date
- FROM employees
- ORDER BY department_id, salary DESC, hire_date ASC;
In this query, PostgreSQL groups rows by department_id , sorts employees within each department by salary DESC , and picks the very first tuple. This replaces complex subqueries, self-joins, or window functions ( ROW_NUMBER() ) with a single, highly optimized query pass.