SQL Queries, Joins, and Aggregates

SELECT, JOIN, GROUP BY, and HAVING — the queries every backend engineer writes daily.

Fresher12 min read

Read these first

Relational Schema Design

Why this matters

Backend engineers write SELECT queries daily for APIs, admin tools, and incident investigation.
JOIN mistakes — accidental cross products, wrong keys — are the top cause of slow reports and inflated row counts.
Aggregations power VaultCommerce dashboards: revenue by category, top customers, monthly trends.
Interview SQL rounds test JOIN + GROUP BY fluency on realistic e-commerce schemas.

Clustered (PK)

Table rows (sorted by PK)

Row 1id=1

Row 2id=2

Row 3id=3

Non-clustered (email)

email index→ row pointer

Table heapunordered rows

A clustered index sorts table rows by key. Non-clustered indexes point to row locations.

SELECT fundamentals

Retrieve columns from a single table with filtering and sorting:

Java

SELECT id, sku, name, price_cents
FROM products
WHERE category_id = 12
  AND stock > 0
ORDER BY price_cents ASC
LIMIT 20;

WHERE filters before rows are returned; ORDER BY sorts; LIMIT caps result size for paginated catalog APIs.

Essential clauses

SELECT — Columns or expressions to return.
FROM — Source table(s).
WHERE — Row filter (no aggregates here).
ORDER BY — Sort direction; combine with LIMIT for pagination.

Inner and left joins

Joins combine rows from related tables on matching keys. VaultCommerce's order detail page needs customer name plus line items:

Java

SELECT
    o.id AS order_id,
    c.email,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price_cents
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.id = 5001;

INNER JOIN returns only rows with matches on both sides. To include customers who never ordered, use LEFT JOIN:

Java

SELECT c.email, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.email;

Customers with zero orders still appear with order_count = 0.

Aggregations and GROUP BY

Summarise rows into groups:

Java

SELECT
    DATE_TRUNC('month', o.created_at) AS month,
    SUM(o.total_cents) AS revenue_cents,
    COUNT(*) AS order_count
FROM orders o
WHERE o.status = 'PAID'
GROUP BY DATE_TRUNC('month', o.created_at)
ORDER BY month DESC;

SUM and COUNT are aggregate functions. Every non-aggregated column in SELECT must appear in GROUP BY.

HAVING vs WHERE

WHERE filters individual rows before grouping. HAVING filters groups after aggregation:

Java

SELECT p.category_id, SUM(oi.quantity) AS units_sold
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'PAID'
  AND o.created_at >= '2026-01-01'
GROUP BY p.category_id
HAVING SUM(oi.quantity) > 1000
ORDER BY units_sold DESC;

Categories with fewer than 1000 units sold are excluded by HAVING, not WHERE.

Practical patterns for VaultCommerce APIs

Common query shapes

Pagination — ORDER BY id LIMIT 50 OFFSET 100 (prefer keyset pagination on id > last_seen at scale).
Existence check — SELECT 1 FROM wishlists WHERE customer_id = ? AND product_id = ? LIMIT 1.
Top-N per group — Window functions (covered in module 3) or subqueries with ROW_NUMBER().

Quick recall

Everything you need if you only revisit this box.

WHERE filters rows; JOIN connects related tables on key equality.
INNER JOIN drops unmatched rows; LEFT JOIN preserves the left table.
GROUP BY with aggregates summarises data; every bare column must be grouped.
HAVING filters aggregated groups; WHERE runs before aggregation.
Always specify explicit ON clauses to avoid accidental Cartesian products.

Test yourself

Answer these before moving on — recall is what makes it stick.