SQL subqueries for testers (2026)

TL;DR

A subquery is a query used inside another SQL statement. Testers use scalar subqueries for one calculated comparison value, EXISTS or NOT EXISTS for relationship checks, correlated subqueries for per-row rules, and FROM subqueries for staged aggregation. Correctness depends on result shape: know whether the inner query can return one value, many values, or no values, and test null and duplicate behavior explicitly.

Key Takeaways

• Use a scalar subquery only when its contract guarantees at most one value.
• Use EXISTS and NOT EXISTS to test relationship presence without duplicating outer rows.
• Correlate deliberately, because a missing outer reference can turn a per-record rule into a global rule.
• Prefer NOT EXISTS over nullable NOT IN lists for absence checks.
• Use derived tables or CTEs to make intermediate grain and calculation stages reviewable.
• Validate subqueries with empty sets, multiple matches, nulls, ties, and boundary data.
• Choose the clearest correct query first, then inspect the database plan on representative volume.

SQL subqueries for testers solve questions that require one query to use the result of another: which tests are slower than the suite average, which active cases have never run, which builds contain failures, or which result is the latest for each case. The syntax can express compact, powerful test oracles without exporting data to a spreadsheet.

A subquery is not automatically advanced or slow. It is simply a nested relational expression. The QA challenge is to understand its result shape, correlation, null behavior, and business scope. This guide uses a runnable PostgreSQL fixture, while noting where a pattern is standard or where database behavior can differ.

TL;DR

1. SQL subqueries for testers: Begin With Result Shape

Every subquery has a shape. It can return one column and one row, one column and many rows, several columns and many rows, or no rows. The surrounding operator expects a particular shape. A scalar comparison such as duration_ms > (subquery) expects at most one value. IN expects one comparable column with any number of rows. EXISTS only asks whether at least one row exists, so selected columns inside it do not carry meaning.

State the shape before writing syntax. For example: the inner query returns one average duration for the same suite; the outer query returns individual results above that average. Or: the inner query returns any result for this case; NOT EXISTS keeps active cases with none. This verbal contract exposes accidental global calculations.

Empty results behave differently by context. A scalar subquery with no rows generally yields null, so a comparison becomes unknown and does not pass WHERE. IN with an empty set is false. EXISTS is false and NOT EXISTS is true. These differences should appear in fixtures, especially when a new tenant or build has no history.

Subqueries can appear in SELECT, FROM, WHERE, HAVING, and data-change statements, depending on the database. This guide focuses on read-only validation. In shared environments, read-only evidence is safer than corrective SQL. If a defect requires cleanup, route it through an approved migration or operational procedure rather than turning an investigative query into an ad hoc update.

2. Create a Runnable Test Execution Dataset

The fixture contains active and retired test cases, two builds, repeated results, one skipped case, one active case never executed, and one open defect. The data supports positive, negative, empty-set, and latest-record examples. Run the setup in a disposable PostgreSQL database.

DROP TABLE IF EXISTS defects;
DROP TABLE IF EXISTS test_results;
DROP TABLE IF EXISTS test_runs;
DROP TABLE IF EXISTS test_cases;

CREATE TABLE test_cases (
  case_id INTEGER PRIMARY KEY,
  suite_name VARCHAR(40) NOT NULL,
  case_name VARCHAR(120) NOT NULL,
  priority VARCHAR(10) NOT NULL,
  active BOOLEAN NOT NULL
);

CREATE TABLE test_runs (
  run_id INTEGER PRIMARY KEY,
  build_number VARCHAR(30) NOT NULL,
  started_at TIMESTAMP NOT NULL
);

CREATE TABLE test_results (
  result_id INTEGER PRIMARY KEY,
  run_id INTEGER NOT NULL REFERENCES test_runs(run_id),
  case_id INTEGER NOT NULL REFERENCES test_cases(case_id),
  status VARCHAR(20) NOT NULL,
  duration_ms INTEGER,
  finished_at TIMESTAMP NOT NULL,
  UNIQUE (run_id, case_id)
);

CREATE TABLE defects (
  defect_id INTEGER PRIMARY KEY,
  case_id INTEGER REFERENCES test_cases(case_id),
  status VARCHAR(20) NOT NULL,
  severity VARCHAR(10) NOT NULL
);

INSERT INTO test_cases VALUES
  (1, 'checkout', 'card payment succeeds', 'p0', TRUE),
  (2, 'checkout', 'declined card is rejected', 'p0', TRUE),
  (3, 'profile', 'user updates display name', 'p1', TRUE),
  (4, 'profile', 'legacy avatar migration', 'p2', FALSE),
  (5, 'checkout', 'gift card and coupon combine', 'p1', TRUE);

INSERT INTO test_runs VALUES
  (101, '2026.07.100', '2026-07-10 09:00:00'),
  (102, '2026.07.101', '2026-07-11 09:00:00');

INSERT INTO test_results VALUES
  (1001, 101, 1, 'passed', 900, '2026-07-10 09:01:00'),
  (1002, 101, 2, 'failed', 1200, '2026-07-10 09:02:00'),
  (1003, 101, 3, 'passed', 400, '2026-07-10 09:03:00'),
  (1004, 101, 4, 'passed', 500, '2026-07-10 09:04:00'),
  (1005, 102, 1, 'passed', 1100, '2026-07-11 09:01:00'),
  (1006, 102, 2, 'passed', 1300, '2026-07-11 09:02:00'),
  (1007, 102, 3, 'skipped', NULL, '2026-07-11 09:03:00');

INSERT INTO defects VALUES
  (7001, 2, 'open', 'high'),
  (7002, 4, 'closed', 'low');

The UNIQUE (run_id, case_id) constraint states that a case has at most one result per run. That matters later when a scalar lookup assumes uniqueness. If the real platform allows retries, the schema needs an attempt dimension and the query must select an intended attempt. Never borrow a one-row assumption from this fixture without checking the target model.

3. Use Scalar Subqueries for One Comparison Value

A scalar subquery appears where one value is expected. The following returns results slower than the average of all non-null result durations. AVG always produces one aggregate row, even when no input rows qualify, so the subquery shape is safe. With an empty eligible set, the value is null and the comparison returns no matches.

SELECT result_id, case_id, duration_ms
FROM test_results
WHERE duration_ms > (
  SELECT AVG(duration_ms)
  FROM test_results
  WHERE duration_ms IS NOT NULL
)
ORDER BY duration_ms DESC;

The global average is an illustrative diagnostic, not a universal performance oracle. Checkout and profile tests may have different normal durations, and a slow test can shift the average upward. A baseline should follow the performance requirement, environment, data, and historical method approved by the team.

A scalar subquery can also retrieve a configured threshold or expected value. It must return no more than one row. If configuration accidentally contains two active thresholds and the subquery is not aggregated or constrained, PostgreSQL reports that more than one row was returned. That failure is valuable because it exposes an invalid assumption. Do not silence it with LIMIT 1 unless ordering and selection are defined by the business contract.

A subquery in SELECT can calculate a repeated contextual value, but a join or window function may express set-level work more clearly. For one overall comparison value, a scalar subquery is readable. For a value per suite or tenant, use intentional correlation, aggregation followed by a join, or a window function. SQL window functions for testers shows how to preserve rows while calculating peer facts.

4. Use IN for Membership, With Clear NULL Semantics

IN (subquery) asks whether an expression equals any value returned by a one-column subquery. The following selects test case details for cases that failed at least once. Duplicate case IDs in the subquery do not duplicate outer rows because IN is a membership test, not a join.

SELECT case_id, suite_name, case_name
FROM test_cases
WHERE case_id IN (
  SELECT case_id
  FROM test_results
  WHERE status = 'failed'
)
ORDER BY case_id;

Case 2 returns. Adding DISTINCT inside the subquery is usually unnecessary for membership semantics, though the optimizer and database can handle the physical plan differently. Write the simplest accurate expression and inspect performance if the data is large.

IN is convenient when the inner query naturally returns a reusable set of keys. EXISTS can be clearer when the relationship is expressed through several correlated columns. Both can represent equivalent logic, and modern optimizers often transform them. There is no honest rule that one keyword is always faster.

Positive IN handles a null in the candidate set without making a known match false, but nonmatching comparisons can become unknown. The larger danger is NOT IN. If the subquery yields any null, x NOT IN (...) may be unknown for every x, producing no rows. A foreign key can be nullable even when sample data is not. Use NOT EXISTS for absence checks or explicitly prove and enforce non-nullability.

Do not paste a huge production ID list into test code. Keep expected keys in controlled fixture tables, parameterized values, or versioned artifacts that make provenance and scope reviewable.

5. Use EXISTS for Relationship Presence

EXISTS returns true when its subquery produces at least one row. The selected expression inside is conventionally 1 because only row presence matters. The following returns builds containing at least one failed result.

SELECT run_id, build_number
FROM test_runs AS tr
WHERE EXISTS (
  SELECT 1
  FROM test_results AS r
  WHERE r.run_id = tr.run_id
    AND r.status = 'failed'
)
ORDER BY run_id;

The correlation r.run_id = tr.run_id ties each inner check to the current outer run. Run 101 returns and run 102 does not. If the correlation were omitted, the one failed result anywhere in the table would make EXISTS true for every run. That is a dangerous bug because the query looks plausible and returns valid rows.

EXISTS avoids duplicating a run when several failures occur. An inner join would return one combination per failure unless grouped or deduplicated. When the requirement is purely binary, presence or absence, EXISTS communicates the oracle precisely.

Add all dimensions required by identity. In a multi-tenant result store where run IDs are only tenant-unique, correlate both tenant and run ID. Also align lifecycle and time scope. A historical failure should not mark today's build failed if the intended relationship is the current execution.

The database can stop searching logically after finding a match, though physical behavior is optimizer-dependent. Indexes on correlated and selective columns may help. Never use SELECT * inside EXISTS as a claim that fields are validated. They are not projected to the outer result, and their values do not affect truth beyond row eligibility.

6. Use NOT EXISTS for Missing Test Evidence

NOT EXISTS is a strong anti-join pattern. It returns an outer row only when the correlated inner query finds no eligible match. The following identifies active cases never executed in any run.

SELECT tc.case_id, tc.suite_name, tc.case_name
FROM test_cases AS tc
WHERE tc.active = TRUE
  AND NOT EXISTS (
    SELECT 1
    FROM test_results AS r
    WHERE r.case_id = tc.case_id
  )
ORDER BY tc.case_id;

Case 5 returns. Retired case 4 does not because the outer population deliberately includes only active cases. This query answers lifetime execution coverage in the fixture. A release gate would probably restrict the inner rows to a target build, test plan, platform, or time window. Requirements define what counts as evidence.

To find active cases missing from build 2026.07.101, correlate the case and link results to the target run inside the subquery. Keep the build restriction inside the match definition. Then test a build with no results, a case marked skipped, a retired case, and a case added after execution started. Whether skipped counts as executed or covered should be a declared policy.

NOT EXISTS also finds orphan staging records, API entities missing audit history, migrated users without preferences, or paid orders without captured payments. The outer table should be the control population. The subquery should define an acceptable match, not merely any related row.

Compare this with a left anti-join in SQL joins for testers. Both forms can be correct. Prefer the one that makes population and match eligibility easiest to review in the surrounding query.

7. Write Correlated Subqueries Without Losing Scope

A correlated subquery references columns from an outer query. Conceptually, it answers an inner question for each outer row. The following returns results slower than the average non-null duration for cases in the same suite.

SELECT
  r.result_id,
  tc.suite_name,
  r.duration_ms
FROM test_results AS r
JOIN test_cases AS tc ON tc.case_id = r.case_id
WHERE r.duration_ms > (
  SELECT AVG(r2.duration_ms)
  FROM test_results AS r2
  JOIN test_cases AS tc2 ON tc2.case_id = r2.case_id
  WHERE tc2.suite_name = tc.suite_name
    AND r2.duration_ms IS NOT NULL
)
ORDER BY tc.suite_name, r.duration_ms DESC;

The alias from the outer query, tc.suite_name, establishes correlation. Without it, every result would be compared with one global average. Add environment, platform, branch, or execution mode if those affect the valid peer group. A per-suite calculation is only meaningful when the suite itself is the approved performance population.

Correlation does not require the database to execute the inner query literally once per row. The optimizer may decorrelate or transform it. Performance should be measured with the actual plan and representative distribution, not predicted only from syntax. Still, complex correlated logic can be harder to review and tune than a grouped derived table or window function.

Test ties and nulls. > excludes results exactly equal to the average, while >= includes them. Null durations never satisfy the comparison. If a suite has no non-null durations, the scalar average is null and no result qualifies. Decide whether that should produce no exception or a separate missing-metric defect.

A correlated query should be explainable as outer identity plus inner peer definition. If that sentence is hard to state, split the work into named stages.

8. Find the Latest Record Per Entity

Latest-record queries appear in status validation, audit testing, event histories, and retry analysis. The first task is to define latest: greatest timestamp, greatest monotonic sequence, latest accepted event, or latest version according to domain ordering. A timestamp can tie, and client-generated time can arrive out of order.

One portable anti-join form returns a result when no newer result exists for the same case:

SELECT r.result_id, r.case_id, r.status, r.finished_at
FROM test_results AS r
WHERE NOT EXISTS (
  SELECT 1
  FROM test_results AS newer
  WHERE newer.case_id = r.case_id
    AND (
      newer.finished_at > r.finished_at
      OR (newer.finished_at = r.finished_at AND newer.result_id > r.result_id)
    )
)
ORDER BY r.case_id;

The result ID acts as a deterministic tie-breaker in this fixture. That is safe only if larger IDs represent later accepted results. If IDs are random UUIDs, ordering them does not create chronology. The schema should expose a defined sequence or event version when latest state matters.

A scalar subquery using MAX(finished_at) is shorter but can return multiple outer rows when timestamps tie. A second maximum or tie-break predicate is then needed. PostgreSQL DISTINCT ON can solve this elegantly but is vendor-specific. ROW_NUMBER() is broadly supported and often the clearest modern solution, especially when returning several columns.

Test retries, identical timestamps, late arrivals, corrected historical records, and cases with no result. Do not conflate latest result with current business status if a separate state machine or materialized state is authoritative. The query validates a derivation only when ordering semantics match the contract.

9. Put Subqueries in FROM to Create Reviewable Stages

A subquery in FROM, often called a derived table, produces rows that the outer query can filter, join, or summarize. It is useful when an aggregate must be calculated before another operation. The derived result should have a clear alias and grain.

SELECT
  summary.suite_name,
  summary.executed_cases,
  summary.failed_cases
FROM (
  SELECT
    tc.suite_name,
    COUNT(*) AS executed_cases,
    SUM(CASE WHEN r.status = 'failed' THEN 1 ELSE 0 END) AS failed_cases
  FROM test_results AS r
  JOIN test_cases AS tc ON tc.case_id = r.case_id
  WHERE r.run_id = 101
  GROUP BY tc.suite_name
) AS summary
WHERE summary.failed_cases > 0
ORDER BY summary.suite_name;

The inner result has one row per suite for run 101. The outer query keeps suites with failures. This is different from applying WHERE r.status = 'failed' before grouping, which would remove passing executions and make executed_cases equal only failed cases. Staging preserves the correct denominator.

Derived tables can pre-aggregate several one-to-many inputs before joining, preventing multiplication. They can also normalize a field once, calculate a status classification, or limit eligibility to an approved snapshot. Name calculated columns in business terms so the outer query reads like a test.

Do not assume nesting automatically materializes the result. Query optimizers can inline, reorder, or otherwise transform relational expressions while preserving semantics. If snapshot consistency or materialization is required, use documented database features and transaction isolation, not visual nesting as a guarantee.

For aggregate exception design, SQL group by and having for testers provides complementary patterns.

10. Choose Between a CTE, Subquery, Join, and Window Function

Several SQL forms can express the same result. Choose the form that makes grain, population, and dependencies clearest, then verify its plan where volume matters. A common table expression begins with WITH and gives an intermediate result a name. It can make a multistage test oracle easier to review than deeply nested parentheses.

Here is the missing-case query expressed with named stages:

WITH target_cases AS (
  SELECT case_id, suite_name, case_name
  FROM test_cases
  WHERE active = TRUE
),
executed_cases AS (
  SELECT DISTINCT case_id
  FROM test_results
  WHERE run_id = 102
)
SELECT tc.case_id, tc.suite_name, tc.case_name
FROM target_cases AS tc
WHERE NOT EXISTS (
  SELECT 1
  FROM executed_cases AS ec
  WHERE ec.case_id = tc.case_id
)
ORDER BY tc.case_id;

The CTE names communicate populations, but DISTINCT should still be justified by the schema. Here the unique constraint already guarantees one result per case and run, so it is redundant. That observation can lead to a simpler query and a stronger understanding of data contracts.

11. Test Subqueries for NULL, Duplicates, Empty Sets, and Ties

A happy-path fixture is not enough. For a scalar subquery, add zero matching rows and two matching rows. Confirm whether empty becomes null and whether multiple rows fail loudly. For IN and NOT IN, add a null candidate. For EXISTS, add several matches and confirm the outer row still appears once. Remove the correlation temporarily in a review environment to see how dramatically the result changes.

For latest-record logic, add identical timestamps, out-of-order arrival, and a corrected historical event. For average comparisons, add null measures, one-row peer groups, extreme values, and two values exactly on the boundary. For absence checks, add an excluded inactive entity and a related row whose status does not qualify.

A strong SQL test oracle has an oracle of its own. Calculate expected IDs manually from a small deterministic fixture and assert the exact set in automation. A row count alone can pass when one expected row is replaced by one unexpected row. Sort only for deterministic presentation, not as a substitute for set comparison.

When using subqueries against API test data, bind identifiers through the database driver. Do not concatenate request input into SQL. Keep tenant and run scopes explicit. If the query accesses eventual-consistency storage, poll a supported observable outcome to a deadline rather than adding a fixed sleep and assuming the subquery is wrong.

Treat a zero-row exception result carefully. It means the query found no exceptions in the observed snapshot. It does not prove the query model, data freshness, permissions, or source completeness were correct.

12. Practice SQL subqueries for testers Safely and Efficiently

Use a consistent workflow. First, write the outer population as a standalone query and verify its identifiers. Second, write the inner query independently where possible and inspect its shape. Third, add correlation and predict results for two outer rows. Fourth, combine them, then test empty, duplicate, null, and boundary cases. Fifth, inspect the plan on representative data and add or verify indexes through normal database review.

For performance, correlation keys, filter selectivity, table statistics, and distribution matter. PostgreSQL's EXPLAIN can show the chosen plan, while EXPLAIN ANALYZE actually executes the statement. Use execution-capable plan commands only on safe read queries in approved environments because they can consume significant resources. Other databases use their own plan interfaces.

Avoid arbitrary LIMIT 1 in scalar subqueries. It hides multiplicity unless a deterministic ORDER BY encodes the intended record. Avoid selecting every column in derived tables, which broadens data exposure and makes grain unclear. Keep diagnostics read-only and return bounded business keys rather than personal data.

A practical learning sequence is scalar aggregate, positive IN, correlated EXISTS, NOT EXISTS, per-peer correlated comparison, derived aggregation, then window-function alternative. Explain each result in terms of population and shape. That explanation is as important in an interview as correct syntax, because it proves you can detect when a query is logically valid but tests the wrong requirement.

Interview Questions and Answers

Q: What is a subquery?

A subquery is a query nested inside another SQL statement. Its output becomes a value, set, row source, or presence test for the outer query. I identify its expected shape before choosing =, IN, EXISTS, or a FROM alias.

Q: What is a correlated subquery?

It references a column from the outer query, so its result is logically evaluated in the context of each outer row. I verify every identity dimension in the correlation, such as tenant and case ID. A missing correlation can turn a per-row rule into a global rule.

Q: What is the difference between IN and EXISTS?

IN compares one expression with values from a one-column result. EXISTS asks whether any correlated row satisfies its predicates. Both can express membership, so I choose the clearer form and validate null behavior rather than claiming one is always faster.

Q: Why is NOT EXISTS often safer than NOT IN?

If a NOT IN subquery returns null, comparisons can become unknown and no outer rows may pass. NOT EXISTS states absence of a correlated match and avoids that candidate-list problem. The correlation still must be complete and correct.

Q: What happens when a scalar subquery returns multiple rows?

The database reports an error because one value was expected. I treat that as evidence that uniqueness or filtering assumptions are wrong. I do not add LIMIT 1 unless deterministic ordering and selection are part of the requirement.

Q: How do you find tests that never executed?

I select the eligible case population and use NOT EXISTS for an acceptable result linked to each case. I include the target build, plan, environment, or date when the requirement is release-specific. I define whether skipped or canceled outcomes count as execution.

Q: When would you replace a subquery with a window function?

I use a window function when I need row detail and a peer calculation together, such as ranking results per case or comparing duration with a suite average. It can remove a self-correlation and make partitions explicit. I still define deterministic tie handling.

Q: How do you test a subquery itself?

I verify the outer and inner queries independently, state the inner shape, and use a small fixture with no rows, one row, multiple rows, nulls, duplicates, and ties. I assert exact identifiers, not only a count. Then I inspect the plan on representative volume.

Common Mistakes

• Using = with a subquery that can legitimately return several rows.
• Adding LIMIT 1 to hide duplicate configuration instead of defining selection.
• Forgetting the correlation predicate in EXISTS, which can make every outer row pass.
• Omitting tenant, build, version, or lifecycle scope from a correlated condition.
• Using NOT IN when the inner result can contain null.
• Assuming a CTE is automatically stored, cached, or faster.
• Comparing every test with one global average when suites have different behavior.
• Defining latest by timestamp without a deterministic tie-breaker.
• Treating no exception rows as proof without validating permissions and freshness.
• Converting an investigative subquery directly into an unreviewed UPDATE or DELETE.

Conclusion

SQL subqueries for testers are most dependable when result shape and scope are explicit. Use scalar subqueries for one guaranteed value, IN for a value set, EXISTS and NOT EXISTS for relationship truth, correlated subqueries for per-row context, and derived tables or CTEs for reviewable stages.

Run the fixture and predict each exact case or run ID before executing a query. Then add a null duration, a duplicate timestamp, and a new case with no result. If your oracle explains all three without an arbitrary limit or hidden global scope, you have moved from knowing subquery syntax to using subqueries as trustworthy QA evidence.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• SQL for QA Tutorial for Beginners (2026)
• SQL group by and having for testers (2026)
• SQL joins for testers (2026)
• SQL window functions for testers (2026)
• Docker basics for testers (2026)
• Docker for QA Tutorial for Beginners (2026)