3.3 Querying, Joining & Aggregating Data

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:

1. LIKE: Standard SQL pattern matching using % (matches zero or more characters) and _ (matches exactly one character). Case-sensitive.
2. ILIKE: PostgreSQL-specific extension providing case-insensitive pattern matching using % and _.
3. 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 \times 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.