An order search page asks for three orders. The database returns three rows, but the interface shows only one order.
The query has joined payments and order items before applying LIMIT. One order has several matching child records, so it occupies all three result rows.
This is a pagination error caused by a mismatch between the requested unit—an order—and the unit being limited—a joined detail row.
This tutorial builds a reproducible order search with three rules:
- At least one successful payment exists.
- No refund is currently processing.
- The order contains product COURSE-A.
We will implement the filter, verify its total count, page over orders, and fetch details without changing which orders belong on the page.
This is part 3 of the SQL row-count series. The examples below use a deliberately constructed seven-order dataset, not production records or a performance benchmark. The goal is to make each incorrect query fail for an identifiable reason, then verify the corrected order set.
Decide What One Result Row Represents
Before choosing JOIN or EXISTS, define the output:
| Requirement | Suitable query shape |
|---|---|
| Show each order with its payment records | JOIN, with a detail-level result |
| Show orders with at least one successful payment | EXISTS |
| Exclude orders with any processing refund | NOT EXISTS |
| Show orders whose successful payments total at least 100 | Aggregate the payments before testing the threshold |
| Page through orders, then display their payments | Select the order page first, then retrieve details |
JOIN is not inherently incorrect. A detail report should return details.
The error is treating a detail-level result as if it already contains one row per order.
Monetary fan-out follows the same pattern, but is covered separately in Why Your SQL SUM Is Too High. For this article, the concern is order eligibility, exclusion, and pagination.
Create the Reproducible Dataset
Run this setup once in a fresh disposable database. It does not drop existing tables.
-- Run once in a fresh disposable database.
CREATE TABLE ep_orders (
id BIGINT PRIMARY KEY, created_at DATETIME NOT NULL
);
CREATE TABLE ep_payments (
id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL, method VARCHAR(20) NOT NULL,
amount DECIMAL(12,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES ep_orders(id)
);
CREATE TABLE ep_refunds (
id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL,
FOREIGN KEY (order_id) REFERENCES ep_orders(id)
);
CREATE TABLE ep_items (
id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL,
product_code VARCHAR(30) NOT NULL,
FOREIGN KEY (order_id) REFERENCES ep_orders(id)
);
INSERT INTO ep_orders VALUES
(1,'2026-07-01 09:00:00'), (2,'2026-07-02 09:00:00'),
(3,'2026-07-03 09:00:00'), (4,'2026-07-04 09:00:00'),
(5,'2026-07-05 09:00:00'), (6,'2026-07-06 09:00:00'),
(7,'2026-07-06 09:00:00');
INSERT INTO ep_payments VALUES
(11,1,'SUCCEEDED','CARD',150),
(21,2,'SUCCEEDED','CARD',150),
(31,3,'FAILED','CARD',200),
(41,4,'SUCCEEDED','CARD',150),
(51,5,'SUCCEEDED','BANK',120),
(52,5,'SUCCEEDED','CARD',20),
(61,6,'SUCCEEDED','CARD',60),
(62,6,'SUCCEEDED','CARD',70),
(71,7,'SUCCEEDED','CARD',150),
(72,7,'SUCCEEDED','BANK',10);
INSERT INTO ep_refunds VALUES
(201,2,'PROCESSING'), (202,2,'COMPLETED'),
(501,5,'COMPLETED');
INSERT INTO ep_items VALUES
(101,1,'COURSE-A'), (201,2,'COURSE-A'),
(301,3,'COURSE-A'), (401,4,'COURSE-B'),
(501,5,'COURSE-A'), (601,6,'COURSE-A'),
(701,7,'COURSE-A'), (702,7,'COURSE-A');All timestamps use the same reporting time zone. Order IDs are globally unique in this simplified schema.
The intended eligibility is easy to inspect:
| Order | Payment facts | Refund facts | Product | Eligible? |
|---|---|---|---|---|
| 1 | Successful card payment: 150 | None | COURSE-A | Yes |
| 2 | Successful card payment: 150 | Processing and completed | COURSE-A | No |
| 3 | Failed card payment: 200 | None | COURSE-A | No |
| 4 | Successful card payment: 150 | None | COURSE-B | No |
| 5 | Successful bank payment: 120; card payment: 20 | Completed | COURSE-A | Yes |
| 6 | Successful card payments: 60 and 70 | None | COURSE-A | Yes |
| 7 | Successful card payment: 150; bank payment: 10 | None | Two COURSE-A lines | Yes |
The qualifying order set is 1, 5, 6, and 7.
Orders 6 and 7 have the same creation timestamp. That deliberate tie will demonstrate why ordering needs a unique secondary key.
Refund rows represent separate refund requests with their current statuses. They are not a status-history table. If your system stores status transitions as separate rows, derive each request’s current state before applying the exclusion rule.
Reproduce the Broken Page
This query expresses the payment and product filters through joins:
SELECT o.id, o.created_at, p.id AS payment_id, i.id AS item_id
FROM ep_orders AS o
JOIN ep_payments AS p
ON p.order_id = o.id AND p.status = 'SUCCEEDED'
JOIN ep_items AS i
ON i.order_id = o.id AND i.product_code = 'COURSE-A'
WHERE NOT EXISTS (
SELECT 1 FROM ep_refunds AS r
WHERE r.order_id = o.id AND r.status = 'PROCESSING'
)
ORDER BY o.created_at DESC, o.id DESC, p.id, i.id
LIMIT 3;The first three rows are:
| Order | Payment ID | Item ID |
|---|---|---|
| 7 | 71 | 701 |
| 7 | 71 | 702 |
| 7 | 72 | 701 |
Order 7 has two matching payments and two matching item lines. It produces four combinations before the next order is reached.
LIMIT 3 selects three combinations, not three orders.
If Java or the frontend deduplicates the result afterward, the page contains one order. Advancing the SQL offset by three can even expose the remaining row for order 7 on the next page.
Deduplicating after pagination cannot recover the qualifying orders that the database never returned.
SELECT DISTINCT on only the parent columns can be a valid alternative before LIMIT. It becomes ineffective at collapsing parent rows when varying payment or item IDs are also selected. That projection-dependent behavior is explained in Why SUM(DISTINCT amount) Is Not a General Fix.
For a pure eligibility query, EXISTS states the requirement directly.
Filter the Parent Orders with EXISTS and NOT EXISTS
Use the order table as the outer result and keep the child tables inside predicates:
SELECT o.id, o.created_at
FROM ep_orders AS o
WHERE EXISTS (
SELECT 1 FROM ep_payments AS p
WHERE p.order_id = o.id AND p.status = 'SUCCEEDED'
)
AND NOT EXISTS (
SELECT 1 FROM ep_refunds AS r
WHERE r.order_id = o.id AND r.status = 'PROCESSING'
)
AND EXISTS (
SELECT 1 FROM ep_items AS i
WHERE i.order_id = o.id AND i.product_code = 'COURSE-A'
)
ORDER BY o.created_at DESC, o.id DESC
LIMIT 3 OFFSET 0;The first page is:
| Order | Created at |
|---|---|
| 7 | 2026-07-06 09:00:00 |
| 6 | 2026-07-06 09:00:00 |
| 5 | 2026-07-05 09:00:00 |
Each outer row is still one order. Multiple matching payments or items do not create additional outer rows. This depends on the outer query starting from the uniquely identified order rows: EXISTS filters an already duplicated outer result but does not deduplicate it.
EXISTS is true when its subquery returns any row; NOT EXISTS is true when it returns none. SELECT 1 is a conventional way to express the check—the selected value is not the information being returned to the outer query. MySQL EXISTS documentation
The order_id correlation is essential. Omitting it would ask whether a matching payment or item exists anywhere in the table.
In a multi-tenant schema where identifiers are only unique within a tenant, include tenant identity in the relationships and scope the outer query to the authorized tenant.
Exclusion Means “None Match,” Not “Some Do Not Match”
A tempting refund filter is:
SELECT DISTINCT o.id
FROM ep_orders AS o
JOIN ep_refunds AS r ON r.order_id = o.id
WHERE r.status <> 'PROCESSING'
ORDER BY o.id;It returns orders 2 and 5.
Order 2 qualifies because it has a completed refund, despite also having a processing refund. Orders with no refunds disappear because the query uses an inner join.
The predicate means:
There exists a refund whose status is not PROCESSING.
The intended rule is:
There does not exist a refund whose status is PROCESSING.
Express that directly:
SELECT o.id
FROM ep_orders AS o
WHERE NOT EXISTS (
SELECT 1 FROM ep_refunds AS r
WHERE r.order_id = o.id AND r.status = 'PROCESSING'
)
ORDER BY o.id;This isolated exclusion check returns orders 1, 3, 4, 5, 6, and 7. The other eligibility conditions still need to be applied for the complete search.
A properly constructed LEFT JOIN restricted to processing refunds, followed by an IS NULL test on a non-null refund key, can also express this anti-match. NOT EXISTS is useful because it makes the absence requirement explicit.
If a real schema permits NULL statuses, define their meaning. A condition status = ‘PROCESSING’ will not treat NULL as processing. NOT EXISTS cannot decide whether an unknown status should block an order; that is a business rule.
Keep Conditions Together When One Record Must Satisfy Them
Consider a different filter: orders with a successful card payment greater than 100.
Put all three conditions inside the same EXISTS:
SELECT o.id
FROM ep_orders AS o
WHERE EXISTS (
SELECT 1 FROM ep_payments AS p
WHERE p.order_id = o.id
AND p.status = 'SUCCEEDED'
AND p.amount > 100
AND p.method = 'CARD'
)
ORDER BY o.id;The result is orders 1, 2, 4, and 7.
Now split the amount and method requirements into two EXISTS predicates:
SELECT o.id
FROM ep_orders AS o
WHERE EXISTS (
SELECT 1 FROM ep_payments AS p
WHERE p.order_id = o.id
AND p.status = 'SUCCEEDED' AND p.amount > 100
)
AND EXISTS (
SELECT 1 FROM ep_payments AS p
WHERE p.order_id = o.id
AND p.status = 'SUCCEEDED' AND p.method = 'CARD'
)
ORDER BY o.id;This returns 1, 2, 4, 5, and 7.
Order 5 now qualifies because it has:
- A successful bank payment of 120.
- A different successful card payment of 20.
No single payment satisfies both the amount and card conditions.
Both queries are valid. They answer different questions.
Also, two EXISTS predicates do not require two distinct payments: the same payment may satisfy both. If the business requires distinct events, express that additional requirement.
These examples isolate payment semantics. They intentionally omit the refund and product filters; do not interpret their outputs as the final searchable order set.
A Total-Paid Threshold Requires Aggregation
“Has a payment of at least 100” is different from “has paid at least 100 in total.”
Order 6 has successful payments of 60 and 70. Neither reaches 100 individually, but together they total 130.
One way to express the aggregate threshold is:
SELECT o.id
FROM ep_orders AS o
WHERE EXISTS (
SELECT 1 FROM ep_payments AS p
WHERE p.order_id = o.id AND p.status = 'SUCCEEDED'
GROUP BY p.order_id
HAVING SUM(p.amount) >= 100
)
ORDER BY o.id;This isolated payment-total check returns 1, 2, 4, 5, 6, and 7.
GROUP BY establishes a per-order payment group. HAVING decides whether its total meets the requirement.
Do not replace this with EXISTS(SELECT SUM(amount) …). Without grouping or a rejecting HAVING condition, an aggregate query returns a result row even when no source rows match; its sum may be NULL, but EXISTS still sees a row.
A derived table of payment totals joined to orders is another reasonable form. Choose based on the report’s required output and measured execution plan.
This example sums successful payment records only. If the requirement is net paid after refunds, define and calculate that different measure before filtering.
Count the Same Qualifying Order Set
A page total should count orders using the same eligibility predicate:
SELECT COUNT(*) AS total_orders
FROM ep_orders AS o
WHERE EXISTS (
SELECT 1 FROM ep_payments AS p
WHERE p.order_id = o.id AND p.status = 'SUCCEEDED'
)
AND NOT EXISTS (
SELECT 1 FROM ep_refunds AS r
WHERE r.order_id = o.id AND r.status = 'PROCESSING'
)
AND EXISTS (
SELECT 1 FROM ep_items AS i
WHERE i.order_id = o.id AND i.product_code = 'COURSE-A'
);The result is 4.
The query counts the outer order rows, so child multiplicity does not inflate it.
Maintain the predicate in one shared application-level query builder or reusable SQL fragment where appropriate. Avoid separately maintaining almost-identical list and count filters that can drift over time.
For the second offset page, keep the same predicate and ordering and change only the limit clause:
LIMIT 3 OFFSET 3;Apply this replacement to the complete first-page query above. The second page returns order 1.
Do not count only the current page or include LIMIT in the definition of the total qualifying set.
Separate list and count statements can still see different data if the underlying records change between executions. If exact agreement is required, use an appropriate consistent-read strategy or a materialized result set. Shared SQL logic alone does not freeze the data.
Use a Stable Order and an Explicit Cursor
The report sorts by:
ORDER BY o.created_at DESC, o.id DESCThe unique ID resolves the timestamp tie between orders 6 and 7. Without a tie-breaker, the order among equal timestamps is not fully specified.
For sequential navigation, a keyset cursor can identify the last order from the previous page.
The first page ends at order 5, created at 2026-07-05 09:00:00. The next query is:
SELECT o.id, o.created_at
FROM ep_orders AS o
WHERE EXISTS (
SELECT 1 FROM ep_payments AS p
WHERE p.order_id = o.id AND p.status = 'SUCCEEDED'
)
AND NOT EXISTS (
SELECT 1 FROM ep_refunds AS r
WHERE r.order_id = o.id AND r.status = 'PROCESSING'
)
AND EXISTS (
SELECT 1 FROM ep_items AS i
WHERE i.order_id = o.id AND i.product_code = 'COURSE-A'
)
AND (
o.created_at < '2026-07-05 09:00:00'
OR (o.created_at = '2026-07-05 09:00:00' AND o.id < 5)
)
ORDER BY o.created_at DESC, o.id DESC
LIMIT 3;It returns order 1, matching the offset-based second page.
In an application, bind the timestamp and ID from the cursor as parameters. The literals here make the example executable. The cursor contains actual sort values, not an offset disguised as an order ID.
A useful tie-boundary test starts immediately after order 7. With its timestamp and ID in the cursor, order 6 must still appear even though its timestamp is equal.
Keyset pagination can avoid walking a deep offset, but it does not provide a historical snapshot. New refunds can change eligibility; changes to the sort fields can move records across the boundary.
Keep the filter and sort definition fixed across a cursor sequence. For exports that must represent one stable point in time, use a suitable snapshot or capture the qualifying IDs. A creation-time cutoff alone does not freeze later payment or refund changes.
Fetch Details After Choosing the Order Page
An API may need three orders and all their successful payment records.
Select the three orders first, then expand their details. For MySQL 8.0 and later, a CTE can express that sequence:
WITH page_orders AS (
SELECT o.id, o.created_at
FROM ep_orders AS o
WHERE EXISTS (
SELECT 1 FROM ep_payments AS p
WHERE p.order_id = o.id AND p.status = 'SUCCEEDED'
)
AND NOT EXISTS (
SELECT 1 FROM ep_refunds AS r
WHERE r.order_id = o.id AND r.status = 'PROCESSING'
)
AND EXISTS (
SELECT 1 FROM ep_items AS i
WHERE i.order_id = o.id AND i.product_code = 'COURSE-A'
)
ORDER BY o.created_at DESC, o.id DESC
LIMIT 3
)
SELECT o.id, o.created_at, p.id AS payment_id, p.amount
FROM page_orders AS o
LEFT JOIN ep_payments AS p
ON p.order_id = o.id AND p.status = 'SUCCEEDED'
ORDER BY o.created_at DESC, o.id DESC, p.id;The output has six rows:
Order 7: payments 71, 72
Order 6: payments 61, 62
Order 5: payments 51, 52That is correct: the selected parent page contains three orders, and their detail expansion contains six payment rows.
There is deliberately no outer LIMIT 3. Applying one would truncate the details again.
The application can group the rows into three order objects. Alternatively, fetch the parent page and then batch-load child records for its IDs using bound parameters.
If the response needs several independent child collections, separate batch reads often make assembly clearer than joining all detail collections into one cross-product. Keep the parent ordering explicitly; an IN list does not guarantee result order.
Match Indexes to the Access Pattern
The eligibility checks suggest lookup paths such as:
payments: (order_id, status)
refunds: (order_id, status)
items: (order_id, product_code)
orders: (created_at, id)These are candidates for evaluation, not a mandatory set of new indexes.
Review existing indexes, foreign-key indexes, tenant scope, other filters, and write costs. A selective product-driven search may benefit from a different leading column order than a parent-driven probe.
Do not assume that correlated SQL means one complete child-table scan per order. MySQL can transform eligible EXISTS and NOT EXISTS predicates into semijoin and antijoin strategies. Eligibility and the selected strategy depend on the query shape and optimizer decisions. MySQL semijoin and antijoin transformations
Likewise, do not claim EXISTS is always faster than JOIN. The primary reason for using it here is to preserve the intended output grain.
Inspect execution plans and test representative parameters before making performance claims.
What the Reproduction Verifies
The SQL shown above was executed against explicit expected results. The checks cover:
| Check | Expected result |
|---|---|
| Joined detail query with LIMIT 3 | Order IDs 7, 7, 7 |
| First order page | 7, 6, 5 |
| Total matching orders | 4 |
| Second offset page | 1 |
| Cursor continuation after order 5 | 1 |
| One card payment greater than 100 | 1, 2, 4, 7 |
| Separate amount and card existence checks | 1, 2, 4, 5, 7 |
| Successful payment total at least 100 | 1, 2, 4, 5, 6, 7 |
| Incorrect non-processing refund join | Incorrectly includes order 2 |
| Correct processing-refund exclusion | Excludes order 2; retains no-refund orders |
| Payment details for the first order page | Six detail rows for three orders |
Additional checks cover cursor continuation across equal timestamps, an eligibility change caused by a new processing refund, and the ungrouped-aggregate EXISTS pitfall.
All 14 result checks passed in an isolated in-memory database using Python 3.12.14 and SQLite 3.53.1. The SQL uses syntax intended for MySQL 8.0+, including the CTE example. MySQL server execution, DECIMAL rounding, execution plans, and performance were not validated here. The payment amounts in this fixture are whole currency units; the checks do not establish accounting precision across engines.
To run a compact check yourself, save the setup SQL block as fixture.sql and the following script as verify.py in the same directory. Run python verify.py with Python 3. It uses only the standard library and creates its own in-memory database. The six assertions below cover the full order set, both offset pages, the count, a cursor crossing the timestamp tie, and an eligibility change. The other query results can be compared with the table above.
from pathlib import Path
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.executescript(Path("fixture.sql").read_text(encoding="utf-8"))
eligible = """
EXISTS (SELECT 1 FROM ep_payments p
WHERE p.order_id = o.id AND p.status = 'SUCCEEDED')
AND NOT EXISTS (SELECT 1 FROM ep_refunds r
WHERE r.order_id = o.id AND r.status = 'PROCESSING')
AND EXISTS (SELECT 1 FROM ep_items i
WHERE i.order_id = o.id AND i.product_code = 'COURSE-A')
"""
base = "SELECT o.id FROM ep_orders o WHERE " + eligible
order = " ORDER BY o.created_at DESC, o.id DESC"
def ids(sql):
return [row[0] for row in db.execute(sql)]
assert ids(base + order) == [7, 6, 5, 1]
assert ids(base + order + " LIMIT 3 OFFSET 0") == [7, 6, 5]
assert ids(base + order + " LIMIT 3 OFFSET 3") == [1]
assert db.execute("SELECT COUNT(*) FROM ep_orders o WHERE "
+ eligible).fetchone()[0] == 4
cursor = """ AND (o.created_at < '2026-07-06 09:00:00'
OR (o.created_at = '2026-07-06 09:00:00' AND o.id < 7))"""
assert ids(base + cursor + order + " LIMIT 3") == [6, 5, 1]
db.execute("INSERT INTO ep_refunds VALUES (701, 7, 'PROCESSING')")
assert ids(base + order + " LIMIT 3") == [6, 5, 1]
print("6 checks passed; SQLite", sqlite3.sqlite_version)Expected output in the verification environment: 6 checks passed; SQLite 3.53.1. No package installation or live database connection is required. For a MySQL check, run the setup and article queries in a disposable MySQL schema and compare their returned rows before inspecting execution plans.
Preserve the Unit the User Is Paging Through
For an order search, establish eligibility at the order level, exclude orders with disqualifying facts, and apply pagination to that parent set.
Then fetch the details needed for display.
Use JOIN when detail rows belong in the output. Use EXISTS when a related record only determines eligibility. Use NOT EXISTS when no disqualifying record may exist. Use aggregation when the condition concerns a total rather than an individual event.
The practical check is simple: if the interface promises three orders, make sure LIMIT is selecting three orders.