SQL for Java Developers: What ORMs Hide

Two weeks after the checkout service from the Spring track went live, the on-call phone buzzed at 2 a.m. The admin dashboard — a simple "revenue per customer" page — had taken the site down. Not the checkout, not payments: a read-only report. Forty thousand customers, and the page fired 40,001 SQL queries to render. Each customer row triggered its own lazy-loaded query for that customer's orders, the connection pool drained, and checkout requests started timing out behind a dashboard nobody was even supposed to look at during a sale.

The same night gave us a second wound. Finance's monthly export — "total refunded per SKU" — ran for minutes against the order-lines table and then died. A developer had written WHERE SUM(quantity * unit_price) > 500, the database rejected it (aggregates aren't allowed in WHERE), so they "fixed" it by loading every row into Java and summing in a loop. Ten million rows, over the wire, into an ArrayList.

Both incidents had the same root cause: the team could write Java in its sleep, but the SQL was invisible. The ORM generated it, nobody read it, and nobody could read a query plan. This post is the missing half of your database education — the SQL your ORM hides from you, verified against a real database (H2 2.3.232 for every example below; I'll flag where Postgres differs). We stay in the checkout-service world from the Spring track: the same customers, orders, and order_items tables.

By the end, you should be able to look at a repository method, predict roughly what SQL it needs, read the database's chosen access path, and recognize when Java is doing work the database should have done.

The schema we'll query

Three tables, the shape every order system grows into. Customers place orders; orders have line items:

CREATE TABLE customers (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    country CHAR(2) NOT NULL,
    referred_by BIGINT
);

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    status VARCHAR(20) NOT NULL,
    placed_at TIMESTAMP NOT NULL
);

CREATE TABLE order_items (
    id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id),
    sku VARCHAR(64) NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL
);

And the seed data — run these inserts before any query below:

INSERT INTO customers VALUES (1,'alice@example.com','Alice','IN',NULL);
INSERT INTO customers VALUES (2,'bob@example.com','Bob','US',NULL);
INSERT INTO customers VALUES (3,'carol@example.com','Carol','IN',NULL);
INSERT INTO customers VALUES (4,'dave@example.com','Dave','US',NULL);
INSERT INTO customers VALUES (5,'eve@example.com','Eve','IN',1);

INSERT INTO orders VALUES (101,1,'SHIPPED',TIMESTAMP '2026-09-02 10:00:00');
INSERT INTO orders VALUES (102,1,'SHIPPED',TIMESTAMP '2026-09-20 11:00:00');
INSERT INTO orders VALUES (103,2,'SHIPPED',TIMESTAMP '2026-09-05 09:30:00');
INSERT INTO orders VALUES (104,3,'CANCELLED',TIMESTAMP '2026-09-12 14:00:00');
INSERT INTO orders VALUES (105,5,'SHIPPED',TIMESTAMP '2026-09-25 16:00:00');
INSERT INTO orders VALUES (106,2,'PENDING',TIMESTAMP '2026-10-01 08:00:00');

INSERT INTO order_items VALUES (1,101,'BOOK-001',2,25.00);
INSERT INTO order_items VALUES (2,101,'MUG-042',1,12.50);
INSERT INTO order_items VALUES (3,102,'BOOK-007',1,60.00);
INSERT INTO order_items VALUES (4,103,'KEYB-100',1,85.00);
INSERT INTO order_items VALUES (5,104,'BOOK-001',3,25.00);
INSERT INTO order_items VALUES (6,105,'MUG-042',4,12.50);
INSERT INTO order_items VALUES (7,106,'BOOK-007',2,60.00);

Our test data: five customers (Dave has never ordered; Eve was referred by Alice), six orders (one PENDING, one CANCELLED), seven line items. Small enough to check by hand — which is exactly why it's good for learning. Every result below is real output from running the query.

Joins: inner, left, and self

A join glues rows from two tables using a match condition, usually a foreign key. The three you need:

INNER JOIN keeps only rows that match on both sides. "Show me shipped line items with their customer and order" — customers with no orders, and orders with no items, silently vanish:

SELECT c.name, o.id AS order_id, oi.sku, oi.quantity, oi.unit_price
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
INNER JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'SHIPPED'
ORDER BY o.id, oi.id;

NAME  | ORDER_ID | SKU      | QUANTITY | UNIT_PRICE
Alice | 101      | BOOK-001 | 2        | 25.00
Alice | 101      | MUG-042  | 1        | 12.50
Alice | 102      | BOOK-007 | 1        | 60.00
Bob   | 103      | KEYB-100 | 1        | 85.00
Eve   | 105      | MUG-042  | 4        | 12.50

Notice who's missing: Carol (her only order was cancelled, filtered by the WHERE) and Dave (no orders at all). The inner join didn't error — it just quietly dropped them. That silence is a common source of "the report doesn't add up" bugs.

LEFT JOIN keeps every row from the left table, filling NULLs where nothing matches. "Every customer with their order count, including customers who never ordered":

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

ID | NAME  | ORDER_COUNT
1  | Alice | 2
2  | Bob   | 2
3  | Carol | 1
4  | Dave  | 0
5  | Eve   | 1

Dave survives with a count of 0. Two details matter here: we count o.id, not * — COUNT(*) counts the joined row itself and would give Dave 1, because the left join still produces one row for him (with NULL order columns). COUNT(o.id) ignores NULLs. This is the single most common COUNT bug in reports.

Principle: group by entity identity (c.id), not a display label (c.name) — two customers can share a name, and grouping by name alone would silently merge their rows.

Self join joins a table to itself with two aliases — for hierarchical or referral data. "Each customer and who referred them":

SELECT c.name AS customer, r.name AS referred_by
FROM customers c
LEFT JOIN customers r ON r.id = c.referred_by
ORDER BY c.name;

CUSTOMER | REFERRED_BY
Alice    | null
Bob      | null
Carol    | null
Dave     | null
Eve      | Alice

The aliases c and r make one table play two roles. Without them the database can't tell which name you mean.

Decision rule: use INNER JOIN when the question requires a match on both sides, and LEFT JOIN when rows from the left side must survive even when no match exists — "all customers", "all days", "all SKUs". If your report's totals don't reconcile, check the join type before anything else.

GROUP BY: WHERE filters rows, HAVING filters groups

Here's the revenue-per-customer report from the opening story, done right — in the database, not in an ArrayList:

SELECT c.id,
       c.name,
       SUM(oi.quantity * oi.unit_price) AS revenue,
       COUNT(DISTINCT o.id) AS orders_placed
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'CANCELLED'
  AND o.placed_at >= TIMESTAMP '2026-09-01 00:00:00'
GROUP BY c.id, c.name
HAVING SUM(oi.quantity * oi.unit_price) > 60.00
ORDER BY revenue DESC;

ID | NAME  | REVENUE | ORDERS_PLACED
2  | Bob   | 205.00  | 2
1  | Alice | 122.50  | 2

Walk the execution order, because it's not the order you wrote it: FROM/JOIN builds the row set → WHERE throws away individual rows (Carol's cancelled order dies here, before any summing) → GROUP BY bundles the survivors per customer → aggregates compute per bundle → HAVING throws away whole groups (Eve's 50.00 revenue isn't > 60, so she's out) → ORDER BY sorts what's left. Dave never appears because the inner join to orders drops him before grouping even starts — another reason the join-type decision above matters.

Clause	Filters…	Sees aggregates?	Example
WHERE	individual rows, before grouping	No — WHERE SUM(x) > 1 is a syntax error	WHERE o.status <> 'CANCELLED'
HAVING	whole groups, after aggregation	Yes	HAVING SUM(oi.quantity * oi.unit_price) > 60.00

Decision rule: put predicates on individual input rows in WHERE — it shrinks the row set before the expensive grouping and sorting happen. Use HAVING for predicates that depend on the grouped/aggregated result: HAVING isn't a second WHERE, use it when the condition only exists after grouping. And never "fix" a WHERE-on-aggregate error by summing in Java: that's how ten million rows end up in an ArrayList.

Subqueries vs joins: same answer, different readability

"Which customers ordered BOOK-007?" Two spellings, identical real output:

-- as a join
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE oi.sku = 'BOOK-007'
ORDER BY c.name;

-- as a subquery
SELECT name FROM customers
WHERE id IN (SELECT o.customer_id FROM orders o
             JOIN order_items oi ON oi.order_id = o.id
             WHERE oi.sku = 'BOOK-007')
ORDER BY name;

NAME
Alice
Bob

Optimizers can often transform simple IN/EXISTS membership queries into efficient semi-join-like plans, so don't assume the join spelling is automatically faster: use the join when you need columns from the other table, the subquery when you're only testing membership ("customers who have ever…"). Correlated subqueries — ones that reference the outer query per row, like "customers whose latest order is pending" — deserve extra scrutiny because they may lead to repeated work. Check the plan; depending on the problem, a join or window function may express the operation more efficiently.

Decision rule: write the readable version first, then check the plan (next section) instead of guessing which is faster. If you can't read the plan, you can't have the argument.

Indexes: turning a table scan into a seek

Finance's export query was this, against an order_lines table with ten million rows:

SELECT SUM(qty * price) AS refunded_total
FROM order_lines
WHERE sku = 'SKU-77777';

Before touching anything, ask the database what it plans to do. Every serious database answers — H2 with EXPLAIN, Postgres with EXPLAIN (and EXPLAIN (ANALYZE, BUFFERS) for the version that actually runs the query). One caution: EXPLAIN ANALYZE actually executes the statement. Be careful with writes and expensive production queries. From Java, it's just another query — the plan comes back as rows you print (the JDBC mechanics are in the Spring track's "JDBC First: Connections, Pools & Transactions" post):

package com.javamakeuse.orders;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class PlanInspector {

    /** Prints the database's plan for the given query.
     *  This helper issues plain EXPLAIN. For PostgreSQL measured execution,
     *  explicitly use EXPLAIN (ANALYZE, BUFFERS) after considering that
     *  it runs the query. */
    public static void printPlan(Connection c, String sql) throws SQLException {
        try (Statement s = c.createStatement();
             ResultSet rs = s.executeQuery("EXPLAIN " + sql)) {
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
        }
    }
}

Here is the genuine plan H2 2.3.232 produced for that query on a 400,000-row table before any index (formatting is H2's own):

SELECT
    SUM("QUANTITY" * "UNIT_PRICE") AS "REFUNDED_TOTAL"
FROM "PUBLIC"."ORDER_LINES"
    /* PUBLIC.ORDER_LINES.tableScan */
WHERE "SKU" = 'SKU-77777'
GROUP BY ()

The line that matters is the comment: tableScan. No index is usable, so the engine reads every row and checks the predicate itself. Then we add one index and re-run EXPLAIN:

CREATE INDEX idx_order_lines_sku ON order_lines(sku);

SELECT
    SUM("QUANTITY" * "UNIT_PRICE") AS "REFUNDED_TOTAL"
FROM "PUBLIC"."ORDER_LINES"
    /* PUBLIC.IDX_ORDER_LINES_SKU: SKU = 'SKU-77777' */
WHERE "SKU" = 'SKU-77777'
GROUP BY ()

The plan changed from tableScan to an index range lookup on IDX_ORDER_LINES_SKU. That's the entire game: same query, same results, different access path. How much less work? On a 10,000,000-row version of the table, EXPLAIN ANALYZE actually executes and reports a measured scanCount — rows the engine touched:

-- 10,000,000-row table, measured with H2 EXPLAIN ANALYZE:
-- before: /* PUBLIC.T.tableScan */              /* scanCount: 10000001 */
-- after:  /* PUBLIC.IDX_T_SKU: SKU = 'SKU-777777' */ /* scanCount: 11 */

Ten million rows touched versus eleven. That's measured execution work in this H2 run, not a guessed optimizer cost. One honest caveat from the lab: on a warm, in-memory engine both versions clocked ~0.06 ms — H2 scans memory absurdly fast, so the wall clock didn't move. The scanCount is the portable signal of work done; on a production database, touching millions of rows can translate into much more CPU, buffer activity and I/O than reading a small selective subset. Measure the actual PostgreSQL plan and timings.

What's inside that index? A B-tree: a sorted tree of key → row-pointer entries. A lookup walks from the root to the relevant leaf range for SKU-777777 instead of reading every page:

Because entries are sorted, a B-tree also serves ORDER BY sku and range scans (WHERE sku BETWEEN …). B-tree indexes can also support many ordered range operations and some prefix searches such as appropriately indexed LIKE 'ABC%'; exact behavior depends on the database and collation/operator rules. Equality on the leftmost column is what matters for composite indexes:

-- "one customer's shipped orders": equality on both columns
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- "orders of one customer, newest first": equality plus ordering
CREATE INDEX idx_orders_customer_placed ON orders(customer_id, placed_at DESC);

Column order is one of the central design choices in a composite index: (customer_id, status) accelerates WHERE customer_id = ? and WHERE customer_id = ? AND status = ?. It generally cannot efficiently seek by status alone using the leading-key ordering, because customer_id is the first key — a status-only search can't navigate the tree (the engine might still scan the index for other reasons, but that's not a seek). For WHERE customer_id = ? ORDER BY placed_at DESC, you'd want (customer_id, placed_at DESC) instead: the status column buys nothing for the ordering. A covering index goes one step further: if every column the query needs lives in the index, the optimizer may be able to use an index-only/covering access path, reducing or sometimes avoiding table/heap reads. Take SELECT customer_id, status FROM orders WHERE customer_id = ? — with the (customer_id, status) composite index in place, the plan uses it directly (/* PUBLIC.IDX_ORDERS_CUSTOMER_STATUS: CUSTOMER_ID = ? */), and both selected columns already live in the index entries.

Index	Best for	Watch out
Single-column B-tree	equality / range on one heavily-filtered column	low-cardinality columns often make weak standalone indexes unless the predicate is highly selective; measure the plan
Composite (a, b)	queries filtering on a, or a + b together	can't seek efficiently on b-only predicates; order matters
Covering (a, b) for SELECT a, b … WHERE a = ?	hot read queries — answers come from the index alone	wider index = more write overhead and memory

When indexes hurt: the write overhead

Every index is a second data structure the database must maintain. Insert a row, and each index gets a new entry; update an indexed column, and entries move. I measured it on H2 with 200,000 batched inserts into two identical tables:

insert 200,000 rows, no secondary indexes: 1665 ms
insert 200,000 rows, 3 secondary indexes:   5831 ms

Same rows, same machine: 3.5× slower writes with three extra indexes. That 3.5× is this H2 experiment, not an index tax constant — but the principle holds: every additional index adds write, storage, and maintenance cost. On a write-heavy checkout path — order placement doing several inserts per request — that overhead lands directly on latency. This is why "just index everything" is not a strategy.

Decision rule: index for your read patterns, and prove each index earns its keep: add it for a slow query you've measured (via EXPLAIN), not for a query you imagine. Frequently joined foreign-key columns and columns used by selective hot-query predicates/orderings are common candidates — but confirm with workload and plans; an FK constraint alone doesn't mean "create an index". Revisit indexes as query patterns and data distributions change — an index nobody's plan uses is pure write tax.

What the ORM hides from you

Back to the 2 a.m. incident. The dashboard code looked innocent — the kind of code an ORM tutorial teaches:

package com.javamakeuse.orders;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedHashMap;
import java.util.Map;

public class OrderReports {

    /** N+1: 1 query for the customers, then one query per customer. */
    public static Map<String, Integer> orderCountsNPlusOne(Connection c) throws SQLException {
        Map<String, Integer> counts = new LinkedHashMap<>();
        try (Statement s = c.createStatement();
             ResultSet rs = s.executeQuery("SELECT id, name FROM customers")) {
            while (rs.next()) {
                long id = rs.getLong("id");
                String name = rs.getString("name");
                try (PreparedStatement ps = c.prepareStatement(
                        "SELECT COUNT(*) FROM orders WHERE customer_id = ?")) {
                    ps.setLong(1, id);
                    try (ResultSet r2 = ps.executeQuery()) {
                        r2.next();
                        counts.put(name, r2.getInt(1));
                    }
                }
            }
        }
        return counts;
    }

    /** One query: the database does the grouping, not your loop. */
    public static Map<String, Integer> orderCountsOneQuery(Connection c) throws SQLException {
        Map<String, Integer> counts = new LinkedHashMap<>();
        try (Statement s = c.createStatement();
             ResultSet rs = s.executeQuery("""
                     SELECT c.id, c.name, COUNT(o.id) AS order_count
                     FROM customers c
                     LEFT JOIN orders o ON o.customer_id = c.id
                     GROUP BY c.id, c.name
                     ORDER BY c.name""")) {
            while (rs.next()) {
                counts.put(rs.getString(2), rs.getInt(3));
            }
        }
        return counts;
    }
}

With JPA the loop is usually invisible — a lazy-loaded collection accessed in a template or a stream().map() — but the SQL is identical. I'm reproducing the same query shape with JDBC so you can see the SQL count directly; JPA lazy loading can create the same 1+N pattern implicitly. I ran both versions against the test database with a query counter:

N+1 pattern: customers=5 -> SQL queries executed = 6
JOIN+GROUP BY pattern: SQL queries executed = 1

Six round trips versus one, for five customers. At forty thousand customers it's 40,001 — the exact number from the incident. Each trip pays network latency, connection checkout, parse and plan time; the database work itself is trivial. The ORM didn't make the queries slow. It made them numerous, and hid the count.

The N+1 is the most famous thing ORMs hide, but not the only one. A few more, with where to learn them properly:

Lazy vs eager loading decides when related rows are fetched. Lazy loading defers fetching until the relationship is accessed; when repeated across a collection of parent entities, that can produce N+1 queries. EAGER specifies that the relationship must be available eagerly; it does not guarantee one SQL JOIN — inspect the generated SQL rather than assuming the fetch strategy. Neither default is right; the access pattern decides. The full treatment, with fetch joins and locking, is in JPA Performance: LAZY/EAGER, N+1, Fetch Joins & Locking.

The SQL itself. For quick local debugging, spring.jpa.show-sql=true can expose generated SQL — and read what your repository methods actually emit. For serious debugging/tests, use your logging configuration or query-count instrumentation so statements and bindings can be inspected systematically. If you can't predict the SQL a line of Java produces, you're flying blind — the mapping annotations are covered in Spring Data JPA: Entities, Repositories & Relationships.

Your own hand-written SQL. Sometimes the right answer is a JdbcTemplate or a repository @Query with the exact join you wrote in this post. Connections, pools, and transaction boundaries for that code live in JDBC First: Connections, Pools & Transactions.

Decision rule: the ORM owns the mapping; you own the queries. Any time a page or job touches a collection of entities, count the SQL statements before you ship — in a test, with logging on. If the statement count grows roughly with the number of parent rows, investigate N+1.

What's next

You now have the foundation to read common application queries, investigate slow ones with plans, and recognize what your ORM may be hiding. The whole post in one mental model:

Java asks for data
  → What rows does the question require?   JOIN — who survives (INNER vs LEFT)
  → Which input rows survive?              WHERE
  → What becomes one result row?           GROUP BY
  → Which groups survive?                  HAVING
  → How must results be ordered?           ORDER BY
  → How will the database execute it?      EXPLAIN
  → Can the database reach the needed rows
    without scanning unnecessary data?     INDEX
  → How many times is Java causing
    that SQL work to happen?               ORM

Three lines to carry forward: the ORM owns the mapping; you still own the queries. A query that returns the right answer can still be operationally wrong. Don't guess whether an index helps — read the plan before and after.

The next post in this track, "Kafka with Java: Producers, Consumers, Delivery Semantics & Idempotency", leaves the database behind: when the checkout service needs to tell the warehouse, the email service, and analytics about an order — reliably, in order, without duplicates — that's messaging.

Field check before you move on: take the revenue-per-customer query from this post and run it against a real Postgres, not H2 — use the Testcontainers setup from Testcontainers: Real Databases in Tests to run the exercise against real PostgreSQL. Then: (1) run EXPLAIN (ANALYZE, BUFFERS) on the query and find the access path for each table; (2) create an index appropriate to one of its join/filter paths, re-run, and compare the chosen plan and actual rows/buffers/timing; (3) drop that index and verify the plan changes back; (4) write a test that counts executed statements for an order-history page in your own codebase — if the count scales with rows, you've found an N+1. Bring the plan output to the next post; we'll need the habit.

Continue: Java Learning Roadmap 2026