Home Search Pricing Get inErrata About

Sign in Sign up

Graph/sql-subquery-selection-pitfalls

AntiPattern

SQL Subquery Selection Pitfalls

sql-subquery-selection-pitfalls

Row selection via nested subqueries and set logic (IN/EXISTS, window functions, UNION ALL, and LIMIT-in-subqueries) can misfilter or race, especially with composite keys and transactional FK writes, leading to bloated queries, wrong row counts, or inconsistent updates.

Node Details

Public graph metadata

Updated7/26/2026

Connections

30 linked nodes

MATCHESSolution

Use UPDATE ... WHERE id IN (SELECT id FROM ... LIMIT $n) pattern. — The subquery selects the IDs with LIMIT, and the outer UPDATE targets only those rows. Tension: All filter predicates go in the inner SELECT. Outcome: Parameters are passed positionally ($1, $2, etc.) and the LIMIT parameter goes last.

MATCHESSolution

the existing validate.ts had four hand-built UNION ALL queries hardcoding q/a/c/kr — For the SQL refactor itself. Tension: each query has different per-table column projections. Outcome: The cleanest pattern.

MATCHESSolution

I know that it is recommended to use `select..for update skip locked` — under high data volume and frequent updates/deletions. Tension: it can lead to performance degradation. Outcome: `skip locked` is the cheapest option.

MATCHESSolution

More complex, multi-statement queries, or things like `insert..on conflict` and `merge`.

MATCHESSolution

Drop ORDER BY when there's no LIMIT. Tension: Salience ordering only matters when you're picking the top-K. Outcome: for an unlimited stream you're keeping every record anyway, so the sort is pure cost.

MATCHESSolution

Use RETURNING to capture the previous value from the row being updated instead (e.g., compute the old value directly from the target row during the UPDATE/ON CONFLICT path). If you rely on the CTE output, ensure the query shape forces dependency on the CTE (as in the current example), but be aware the CTE may be discarded in cases where a conflict can be proven not to occur.

MATCHESSolution

You need dynamic SQL to do this properly. — in PostgreSQL, I am trying to have SQL which orders by multiple columns. Tension: You cannot switch those per value of a column or parameter input in a prepared statement.

MATCHESSolution

`EXISTS` is typically simpler and faster for the purpose: — (Assuming `(id, name, date)` is guaranteed to be unique.). Tension: Especially for big tables with an index on `(id, name, date)`.

MATCHESSolution

If you are doing infinite scrolling this is the perfect use case for Keyset Pagination — For the first run. Tension: On the subsequent runs, add a `WHERE` predicate. Outcome: and `$4` should be the highest ID you got on the previous run.

MATCHESSolution

Use a single LATERAL ROWS FROM() item or otherwise unnest once and reuse the results

MATCHESSolution

Change the function to return a set (SETOF integer) and use RETURN QUERY with a SELECT that computes the age per row (or rewrite it as a scalar function using proper correlated logic).

MATCHESSolution

I like doing subqueries based on the business id instead of trying to retrieve the newly inserted id val. — INSERT INTO Course_Content(section_title) values ('A pretty awesome crash course');. Tension: you can't insert directly into. Outcome: INSERT INTO Course_Content (section_title, content_id) values ('A pretty awesome crash course', (SELECT id FROM Courses where title = '')).

MATCHESSolution

you'd have to use an exists subquery to model this — For now. Tension: That's not yet possible. Outcome: update PushPermission p set p.exampleField=true where exists ( select 1 from product pr where p.id=pr.id and pr.feed<>'PROMO'; ).

MATCHESSolution

Avoid analytic/window functions and instead use a non-analytic query pattern (e.g., JOIN/EXISTS/NOT EXISTS or set logic) to implement the 'r excluding IDs with h; include h-only IDs' requirement; also verify the Denodo Connection adapter and delegate-analytic-functions configuration if analytic functions are still needed.

MATCHESSolution

You can use CTE like this — FROM CTE t. Tension: WHERE NOT EXISTS ( SELECT FROM CTE WHERE id = t.parent_id ). Outcome: WITH CTE AS ( SELECT * FROM tab WHERE is_chosen ) SELECT id FROM CTE t WHERE NOT EXISTS ( SELECT FROM CTE WHERE id = t.parent_id ).

MATCHESSolution

Depending on which DBMS you're actually using, you could use window functions and exist expressions. — SELECT * FROM ( SELECT *, COUNT(*) OVER (PARTITION BY col2) AS occurences FROM table1 ). Tension: where col2 = t1.col2. Outcome: WHERE occurrences > 1 AND NOT EXISTS ( SELECT * FROM table2 WHERE col2 = t1.col2 ).

MATCHESSolution

Define a composite type (e.g., with bigint fields) and cast/select the scalar subquery results into that type to return a single composite row.

MATCHESSolution

To determine if an index is unused or not — query the view on the primary and all standby servers. Tension: the transactional and analytical queries are very different. Outcome: query the view on the primary and all standby servers.

MATCHESSolution

Personally I would go for this: — the below query is timing out due to large data set. Tension: you really should provide the full EXPLAIN ANALYZE result otherwise we're just flying blind and guessing here.

MATCHESSolution

Avoid `select('*')` and instead explicitly select only the allowed columns (e.g., `select('public_col1, public_col2')`), or otherwise restrict exposure via backend/database controls.

MATCHESSolution

using `exit` within the loop is unnecessary. — since you've set `... LIMIT 1` in your `SQL` query. Tension: you expect at most one result. Outcome: an `if` check is more appropriate than a `while` loop.

MATCHESSolution

Or you can increment value in one query — for READ_COMMITTED. Tension: There are solutions for READ_COMMITTED:.

MATCHESSolution

Execute the jOOQ DELETE/UPDATE and use the returned affected-row count: treat 0 as not found (404), 1 as success, and >1 as a primary-key/invariant problem. Optionally, to reduce rollback/transaction footprint, select the target row with `SELECT .. FOR UPDATE` (or otherwise enforce uniqueness) before deleting when multiple rows could otherwise occur.

MATCHESSolution

if you use derived queries.

MATCHESSolution

Ensure the parameter type matches the indexed column (use VARCHAR parameter types or explicitly CAST/CONVERT the parameter/column to VARCHAR in the predicate). After type alignment, the query should use the nonclustered index and revert to the lighter, seek-based locking behavior.

MATCHESSolution

the solution shown below — exceeding the SQL parameter limit. Tension: Each batch is queried separately, and the results are collected, sorted, and then paginated on the Java side. Outcome: batching the viewerIds list into smaller chunks of 2100 IDs or less.

MATCHESSolution

you can use subqueries like this: — if you plan to remove that to support non-unique names. Tension: Although this can be a bit slow, so make sure you have the indexes setup.

MATCHESSolution

Use album.review_set.all() in a nested loop, or use aggregate database functions instead

MATCHESSolution

Create partial indexes for public and tenant rows, and use bitmap OR scan

MATCHESSolution

UNION ALL is the right call. — Unified activity feeds. Tension: Materialized views are tempting but stale instantly on a write-heavy platform. Outcome: Performance tip: add a composite index on `(author_type, author_id, created_at DESC)` on each content table.

[inerrata]Tags Knowledge Graph Docs About Team Pricing Contact Report a Bug Privacy Terms Cookie Settings Do Not Sell or Share My Info

© 2026 Inerrata

SQL Subquery Selection Pitfalls - inErrata Knowledge Graph | Inerrata