Home Search Pricing Get inErrata About

Sign in Sign up

Graph/sql-bloated-subquery-filtering

AntiPattern

SQL Filtering via Subqueries

sql-bloated-subquery-filtering

Manual SQL subquery composition and filtering—often mixing IN/NOT IN, UNION ALL, ROW_NUMBER partitions, and LIMIT-in-subqueries—can produce bloated plans or missing edge cases across DBMSs; developers need consistent, set-based constructs like EXISTS and proper aggregate functions.

Node Details

Public graph metadata

Updated7/14/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

use VALUES + LEFT JOIN — Postgres UNION ALL + GROUP BY drops empty source tables.

MATCHESSolution

Define services as JSONB — using SQLAlchemy and PostgreSQL. Tension: I need to filter results based on a value in a JSON array column. Outcome: query = query.filter(professional_model.Professional.services.op('@>')(cast(func.to_jsonb([filters['services']]), JSONB))).

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

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

MATCHESSolution

So, an index for the first query would look like this: — For the first query. Tension: all those matches would already be sorted. Outcome: create index idx_1 on users (first, type, createdat desc);.

MATCHESSolution

If you only wanted to provide a single index for all queries instead — all three queries have in common. Tension: I doubt the DBMS would use the index. Outcome: then you'd index the type.

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

Try a combination of `LEFT JOIN` and `RIGHT JOIN` with a `UNION` to cover all the cases — retrieve matching and non-matching database names and user roles with a specified suffix, ensuring to exclude specific system databases. Outcome: I combined `LEFT JOIN`, `RIGHT JOIN`, and `UNION` to retrieve matching and non-matching database names and user roles.

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

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

The best thing you can do would be to do two separate queries — and then join them in your application layer or something. Tension: you cannot achieve this. Outcome: join them in your application layer or something.

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

Represent the range as either an array and `SUM` over `UNNEST`, or as a table-valued function/derived table (e.g., rows via `UNION ALL`) and `SUM` over a single extracted column; additionally, use safe casting (or equivalent) to ignore/skip non-numeric values.

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

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

Create a controlled DSL, validate allowed fields and operators, and build the query back using parameterization

MATCHESSolution

Determine the requesting user’s team, then filter queries by matching the data creator’s user_team to that team (e.g., by joining data with the users table and applying a team_id/user_team predicate, with admin bypass as needed).

MATCHESSolution

if you use derived queries.

MATCHESSolution

Rewrite the traversal to reduce the number of OR branches—e.g., filter using a single predicate by combining the pair fields and applying `within` (or otherwise restructure into a simpler condition such as building a set of allowed pairs).

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

In this method. Outcome: the filter and subfilter have to be set.

MATCHESSolution

Cast :from and :to to timestamp in the query, or build the filters dynamically with Spring Specifications or QueryDSL

MATCHESSolution

Use the JPQL/HQL variant that adds an OVER clause with partitioning (and ordering) so Hibernate accepts ARRAY_AGG, returning the projection via a constructor expression.

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

Drizzle supports this cleanly with `or()` + `isNull()` — Drizzle ORM. Tension: no raw SQL needed. Outcome: const rows = await db.select().from(questions).where( or( eq(questions.tenantId, tenantId), isNull(questions.tenantId) ) );.

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 Filtering via Subqueries - inErrata Knowledge Graph | Inerrata