Data Retrieval

1Concept / Definition

Data retrieval in SQL is expressed with the SELECT statement: a declarative description of which rows and columns you want, in which order. The database decides how to fetch them.

SELECT o.order_id, o.placed_at, o.total  -- projection: which columns
  FROM purchase_order o  -- source
 WHERE o.customer_id = 101  -- selection: which rows
   AND o.status = 'PAID'
 ORDER BY o.placed_at DESC, o.order_id DESC  -- ordering (deterministic)
 FETCH FIRST 20 ROWS ONLY;  -- limit (standard form of LIMIT 20)

2Why It Exists

SQL's SELECT derives from relational algebra: selection (σ), projection (π) and joins. Because it is declarative, the same query keeps working as data grows and indexes change — the optimizer adapts the execution. This independence is why SQL has outlived every generation of application frameworks built on top of it.

3Core Concepts

3.1 SELECT

// Map exactly the selected columns to a record
public record OrderSummary(long orderId, Instant placedAt, BigDecimal total) {}
 
List<OrderSummary> recent = jdbc.sql("""
        SELECT order_id, placed_at, total
          FROM purchase_order
         WHERE customer_id = :customerId AND status = 'PAID'
         ORDER BY placed_at DESC, order_id DESC
         LIMIT 20""")
    .param("customerId", customerId)
    .query(OrderSummary.class)
    .list();

3.2 WHERE

Parameters, not concatenation: always bind values (:customerId, ?) — for SQL-injection safety and plan reuse.

3.3 ORDER BY

SELECT ticket_id, due_on, priority
  FROM support_ticket
 WHERE status = 'OPEN'
 ORDER BY due_on ASC NULLS LAST, priority DESC, ticket_id;  -- deterministic

3.4 DISTINCT

-- Latest order per customer (PostgreSQL DISTINCT ON)
SELECT DISTINCT ON (customer_id) customer_id, order_id, placed_at, total
  FROM purchase_order
 ORDER BY customer_id, placed_at DESC;

Smell: DISTINCT added to "fix" duplicates caused by a join to a one-to-many table usually hides a wrong query and costs a sort/hash. Fix the join (use EXISTS, aggregate first, or join the right grain).

4How It Works Internally — From SELECT to Rows

Because evaluation is logical, the optimizer may reorder steps as long as results are equivalent — e.g. apply filters inside index scans, or use an index to deliver rows already sorted.

4ADeep Dive — Pagination Patterns

// Spring Data JPA: offset paging (fine for small tables / admin screens)
Page<OrderSummary> page = orders.findByCustomerId(customerId,
        PageRequest.of(pageNo, 20,
                Sort.by(Sort.Order.desc("placedAt"), Sort.Order.desc("id"))));
 
// Keyset scrolling for large feeds
Window<OrderSummary> window = orders.findTop20ByCustomerIdOrderByPlacedAtDescIdDesc(
        customerId, ScrollPosition.keyset());

5APIs / Features / Syntax

6Practical Examples

6.1 SELECT * broke an integration

A partner export used SELECT * and positional mapping; adding a column shifted fields and sent wrong data for a day. Explicit column lists and name-based mapping prevented recurrence.

6.2 Undefined order in tests

A test asserted the first returned row without ORDER BY; it passed for months and failed after a PostgreSQL upgrade enabled a parallel plan. Deterministic ORDER BY fixed the flakiness.

6.3 Worked example — making a filter sargable

Scenario. The daily-orders screen in an e-commerce back office took almost three seconds to load, although purchase_order had an index on placed_at. The developer had written the filter the way the business described it: "orders placed on 25 September".

-- Slow: function on the column prevents index use (Seq Scan on 30 M rows)
SELECT order_id FROM purchase_order WHERE date(placed_at) = DATE '2026-09-25';
 
-- Fast: half-open range on the raw column (Index Scan)
SELECT order_id FROM purchase_order
 WHERE placed_at >= TIMESTAMPTZ '2026-09-25 00:00+05:30'
   AND placed_at <  TIMESTAMPTZ '2026-09-26 00:00+05:30';

The explicit +05:30 offset also makes the "day" boundary unambiguous (IST), avoiding a classic time-zone bug.

What it shows. Wrapping the column in date() forces the database to compute date(placed_at) for every one of the 30 million rows before it can compare, so the index on placed_at cannot be used and the plan is a sequential scan. Rewriting the condition as a half-open range on the raw column, from midnight on the 25th up to but not including midnight on the 26th, lets the planner do an index range scan, cutting the query from 2.8 seconds to 4 milliseconds. The explicit +05:30 offset also makes "the day" unambiguous for a business operating in India, whatever the session time zone.

Takeaway. Keep indexed columns bare on one side of the comparison. If a function is unavoidable, create an expression index on exactly that function, and always confirm with EXPLAIN that the plan uses the index you expect.

6.4 Worked example — the NOT IN / NULL trap

Scenario. Marketing asked for customers who have never placed an order. The obvious NOT IN query returned zero rows in production, although everyone knew thousands of such customers existed. The same query had worked in the test database.

-- Customers who never placed an order — returns NO rows if any order has customer_id NULL
SELECT c.customer_id FROM customer c
 WHERE c.customer_id NOT IN (SELECT o.customer_id FROM purchase_order o);
 
-- Correct and efficient (anti-join)
SELECT c.customer_id FROM customer c
 WHERE NOT EXISTS (SELECT 1 FROM purchase_order o WHERE o.customer_id = c.customer_id);

What it shows. Production contained a few guest orders with customer_id NULL. NOT IN expands to a chain of inequality tests, and comparing anything with NULL yields UNKNOWN, so for every customer the whole condition becomes UNKNOWN and WHERE discards the row. The test database simply had no NULLs. NOT EXISTS asks a different question, whether a matching row exists, so NULLs in the subquery cannot affect it. Most planners also execute NOT EXISTS as an efficient anti-join.

Takeaway. Use NOT EXISTS for "has no matching rows" queries by default. Treat NOT IN with a subquery as a code-review warning, and make columns NOT NULL wherever the data model allows, because NULLs change the meaning of many SQL constructs.

6.5 Worked example — replacing a Java loop with one query

Scenario. A customer-overview page showed each customer's latest order. With 500 customers on the page it issued 501 queries, one for the list and one per customer, and took over a second even though each individual query was fast.

// Before: N+1 — one query per customer to get their latest order
for (Customer c : customers) {
    Order latest = orders.findFirstByCustomerIdOrderByPlacedAtDesc(c.getId());
    …
}
 
// After: one set-based query with DISTINCT ON
record LatestOrder(long customerId, long orderId, Instant placedAt) {}
List<LatestOrder> latest = jdbc.sql("""
        SELECT DISTINCT ON (customer_id) customer_id, order_id, placed_at
          FROM purchase_order
         WHERE customer_id = ANY(:ids)
         ORDER BY customer_id, placed_at DESC""")
    .param("ids", customerIds.toArray(Long[]::new))
    .query(LatestOrder.class).list();

What it shows. The loop is the classic N+1 problem: the cost is dominated by round trips to the database, not by the queries themselves. The replacement sends all customer IDs in one parameter and uses PostgreSQL's DISTINCT ON (customer_id) with ORDER BY customer_id, placed_at DESC, which keeps exactly the newest order per customer. One query and one round trip return all 500 rows, mapped directly into a LatestOrder record. On other databases, a window function such as ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) achieves the same result.

Takeaway. When code loops over results and queries inside the loop, think in sets instead. Database round trips are expensive; one well-written set-based query almost always beats many small ones, and statement-count assertions in tests catch regressions.

6.6 Worked example — top-N with ties

Scenario. The home page shows "Top 3 products this month". Two products tied for third place with 90 units each, and depending on the execution plan, sometimes one appeared and sometimes the other, which confused the merchandising team and made caches inconsistent.

-- Top 3 products by units sold; include ties for 3rd place
SELECT product_id, units
  FROM product_sales_30d
 ORDER BY units DESC
 FETCH FIRST 3 ROWS WITH TIES;

What it shows. LIMIT 3 simply stops after three rows, and when rows tie on the sort key, which one comes third is undefined. FETCH FIRST 3 ROWS WITH TIES, from the SQL standard and supported by PostgreSQL 13 and later, returns every row that ties with the last one, so both products with 90 units appear and the result is deterministic. If the page must show exactly three items, add a tie-breaker to ORDER BY instead, such as product_id, so the choice is at least stable.

Takeaway. Every top-N or paged query needs a deterministic order. Decide explicitly whether ties should all be shown or broken by a secondary key, and make that business decision visible in the SQL.

7Common Problems & Mistakes

• `SELECT ** in application code
• Functions on indexed columns in WHERE.
• NOT IN with nullable subquery columns.
• Missing or non-deterministic ORDER BY.
• DISTINCT masking bad joins.
• OFFSET pagination deep into large result sets.
• String-concatenated SQL (injection, no plan reuse).

8Performance & Production Considerations

9Architecture & Design Considerations

• Define read models/projections per use case rather than loading full entities.
• Require deterministic ordering and bounded results for every API list.
• Standardise keyset pagination for large or infinite lists.
• Review SQL in code reviews with plans for critical queries.

10Interview Questions & Answers