SQL in PostgreSQL: Queries, Joins, CTEs, and Window Functions

SQL in PostgreSQL: Queries, Joins, CTEs, and Window Functions

Structured Query Language is the primary tool a database administrator uses to retrieve, analyze, and validate data. PostgreSQL 16 implements the SQL standard fully and adds several powerful extensions. This lesson moves beyond single-table SELECT statements and builds the skills you need to write complex, production-quality queries.

Joins: Combining Data from Multiple Tables

Most real databases spread related data across many tables. A join tells PostgreSQL how to match rows from two or more tables.

• INNER JOIN returns only rows where the join condition is satisfied in both tables. Rows with no match are excluded from both sides.
• LEFT OUTER JOIN returns all rows from the left table and matched rows from the right; unmatched right-side columns appear as NULL.
• FULL OUTER JOIN returns all rows from both tables; NULLs fill columns where no match exists on either side.

Always qualify column names with the table name or alias when joining tables that share column names. Ambiguous references cause errors and make queries harder to read.

```sql

SELECT p.product_name, c.category_name

FROM products p

INNER JOIN categories c ON p.category_id = c.category_id;

```

Aggregates and GROUPING SETS

Aggregate functions such as COUNT, SUM, AVG, MIN, and MAX collapse many rows into summary values. GROUP BY controls which columns define each group. PostgreSQL extends this with GROUPING SETS, which produces multiple levels of aggregation in a single query without UNION ALL:

```sql

SELECT region, category_name, SUM(quantity_sold)

FROM sales

GROUP BY GROUPING SETS ((region, category_name), (region), ());

```

The empty set `()` produces a grand total. This is far more efficient than writing three separate queries.

Correlated Subqueries

A correlated subquery references a column from the outer query. PostgreSQL re-evaluates it once for each row of the outer query. Correlated subqueries are readable for small datasets but can be slow on large tables because of repeated execution. Always check the query plan with EXPLAIN ANALYZE before deploying them in production.

```sql

SELECT p.product_name

FROM products p

WHERE p.unit_price > (

SELECT AVG(unit_price) FROM products p2

WHERE p2.category_id = p.category_id

);

```

Common Table Expressions (CTEs)

A Common Table Expression (introduced with the WITH keyword) names a temporary result set that you can reference later in the same query. CTEs improve readability by breaking a complex query into named, logical steps. In PostgreSQL 12 and later, the optimizer can inline non-recursive CTEs, so there is usually no performance penalty compared to a subquery.

```sql

WITH avg_prices AS (

SELECT category_id, AVG(unit_price) AS avg_price

FROM products

GROUP BY category_id

)

SELECT p.product_name, p.unit_price, a.avg_price

FROM products p

JOIN avg_prices a ON p.category_id = a.category_id

WHERE p.unit_price > a.avg_price;

```

A recursive CTE includes a UNION ALL that references the CTE itself, making it possible to traverse hierarchical data such as an organizational chart or a product category tree. The recursive term must eventually produce no rows, or the query will run indefinitely — PostgreSQL enforces a `max_recursive_iterations` safeguard, but always design a clear termination condition.

Window Functions

Window functions perform calculations across a set of rows that are related to the current row, without collapsing those rows into a single output row the way GROUP BY does. You define the window with an OVER clause.

• ROW_NUMBER() assigns a unique sequential integer to each row within its partition.
• RANK() assigns the same rank to ties and skips subsequent rank numbers.
• LAG(column, offset) returns the value from a previous row within the partition, useful for period-over-period comparisons.

```sql

SELECT region, product_name, total_sales,

RANK() OVER (PARTITION BY region ORDER BY total_sales DESC) AS sales_rank

FROM regional_sales;

```

Window functions execute after WHERE and GROUP BY filtering but before the outer ORDER BY. This means you cannot filter on a window function result in the same WHERE clause — wrap the query in a CTE or subquery first.

Data Safety and Best Practices

Although SELECT queries do not modify data, you must still follow safe practices in a shared database environment. Always run complex queries against a development or training instance (such as the inventory_db VM provided) before running them on production. Never use a superuser role for routine querying; use a role with only SELECT privileges on the relevant tables. If you accidentally begin a transaction that modifies data, issue ROLLBACK immediately. Before running any long-running query in production, estimate its cost with EXPLAIN ANALYZE on the training instance first to avoid locking tables or overwhelming shared resources.

Putting It Together

The progression from joins to aggregates to CTEs to window functions reflects the way query complexity grows in real DBA work. Master each layer before combining them. Read the query plan at each stage to understand what PostgreSQL is actually doing — this skill underpins performance tuning, which you will study in a later lesson.