SQL CTEs vs Subqueries: Why Your Query Returns Different Row Counts

You refactor a long SQL query into a Common Table Expression (CTE) to improve readability.

The query executes successfully.

No syntax errors.

No warnings.

But something unexpected happens.

The original query returned 18,245 rows.

The CTE version returns 17,912.

Or perhaps it returns 19,034.

How can two seemingly equivalent queries produce different results?

The answer is usually not that CTEs behave differently from subqueries. In most modern database engines, they are logically equivalent in many situations. Instead, differences usually arise from changes introduced during refactoring—such as altered filtering, join conditions, aggregation order, duplicate elimination, or query optimization behavior.

This article explains why CTEs and subqueries sometimes return different row counts, how to identify the real cause, and how to rewrite complex SQL safely.

What You'll Learn

After reading this guide, you'll understand:

• What CTEs and subqueries are.
• Whether they should return identical results.
• Common causes of row count differences.
• How joins and filtering affect results.
• Execution plan considerations.
• Best practices for debugging complex SQL.

Understanding CTEs

A Common Table Expression (CTE) is a temporary named result set defined using the WITH clause.

Example:

WITH ActiveCustomers AS (
    SELECT *
    FROM Customers
    WHERE IsActive = 1
)
SELECT *
FROM ActiveCustomers;

CTEs improve readability by breaking complex logic into manageable steps.

Understanding Subqueries

A subquery performs a similar role but is embedded directly within another SQL statement.

Example:

SELECT *
FROM (
    SELECT *
    FROM Customers
    WHERE IsActive = 1
) AS ActiveCustomers;

In many scenarios, this query is logically equivalent to the previous CTE example.

Should Row Counts Match?

If both queries implement identical logic, the answer is generally yes.

Different row counts usually indicate that something else changed during refactoring.

Typical causes include:

• Join logic
• Filter placement
• Aggregation order
• DISTINCT usage
• Window functions
• Recursive processing
• Optimizer behavior in specific databases

Filtering Before vs After a JOIN

One of the most common causes involves moving a filter.

Example:

Original:

SELECT *
FROM Orders
JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.Country = 'USA';

Refactored:

WITH USCustomers AS (
    SELECT *
    FROM Customers
    WHERE Country = 'USA'
)

SELECT *
FROM Orders
JOIN USCustomers
ON Orders.CustomerID = USCustomers.CustomerID;

These are usually equivalent.

However, changing the join type or moving additional filters can alter the final row count.

LEFT JOIN vs INNER JOIN

A subtle join change often produces different results.

Example:

Original:

LEFT JOIN Customers

Refactored:

INNER JOIN Customers

Even if everything else remains unchanged, unmatched rows disappear.

DISTINCT Can Hide Problems

Developers sometimes introduce:

SELECT DISTINCT

during refactoring.

While duplicates disappear, so do clues about the underlying issue.

If row counts change unexpectedly, temporarily remove DISTINCT and inspect the duplicated rows.

GROUP BY Changes Result Sets

Aggregation changes row counts by design.

Example:

Before grouping:

5000 rows

After grouping:

120 rows

If aggregation moves inside or outside a CTE, the result set may differ.

Window Functions

Window functions calculate values without reducing row counts.

However, filtering after computing:

ROW_NUMBER()

or

RANK()

can remove different records depending on where the calculation occurs.

NULL Handling

NULL comparisons frequently produce unexpected results.

Examples include:

WHERE Region = NULL

instead of:

WHERE Region IS NULL

When refactoring queries, verify that NULL handling remains unchanged.

Correlated Subqueries

A correlated subquery executes relative to each outer row.

Example:

SELECT *
FROM Orders o
WHERE EXISTS (
    SELECT 1
    FROM Customers c
    WHERE c.CustomerID = o.CustomerID
);

Replacing correlated logic with a CTE may unintentionally change semantics if the join conditions are not preserved exactly.

Recursive CTEs

Recursive CTEs behave differently from ordinary subqueries.

They generate hierarchical data such as:

• Organization charts
• Category trees
• Folder structures
• Bills of materials

Incorrect termination conditions can dramatically change row counts.

Compare Intermediate Results

Instead of debugging the final query immediately:

Compare:

SELECT COUNT(*)

after each logical step.

For example:

WITH FilteredOrders AS (...)

Then:

SELECT COUNT(*)
FROM FilteredOrders;

This quickly identifies where rows are added or removed.

Compare Primary Keys

Rather than comparing counts alone, compare identifiers.

Example:

SELECT OrderID

from both versions.

Questions to ask:

• Which IDs disappeared?
• Which IDs appeared unexpectedly?
• Why?

Primary key comparison often reveals the true issue much faster than comparing aggregates.

Inspect Execution Plans

Execution plans may reveal:

• Join order
• Index usage
• Predicate pushdown
• Hash joins
• Nested loops
• Merge joins
• Materialization

Although different execution plans do not necessarily change results, they can expose unintended query modifications.

Database Optimizer Differences

Database engines optimize CTEs differently.

Examples include:

• PostgreSQL
• SQL Server
• Oracle
• MySQL
• MariaDB

Some optimizers inline CTEs automatically, while others may materialize them depending on the version, query structure, or optimizer decisions.

Understanding your database engine helps explain performance differences, though logically equivalent queries should still produce the same results.

Real-World Example

A business intelligence team maintains a monthly sales report that joins customers, orders, invoices, and payment information. To improve readability, a developer rewrites a deeply nested query using multiple CTEs.

The new query executes successfully but reports 1,500 fewer sales records than the previous version. After comparing row counts at each CTE stage, the team discovers that a filter on invoice status was moved inside an intermediate CTE before a LEFT JOIN. This unintentionally removed rows that were previously preserved until later in the query.

By restoring the original filter order and validating each intermediate result set, the CTE version produces the same output while remaining significantly easier to understand and maintain.

Debug Incrementally

A practical debugging workflow is:

1. Validate each CTE independently.
2. Count rows after every stage.
3. Compare primary keys.
4. Review joins.
5. Verify filters.
6. Inspect execution plans.
7. Compare the final output.

Breaking complex SQL into smaller pieces reduces debugging time dramatically.

Don't Blame the CTE

In most cases, the CTE itself is not responsible.

Instead, the issue comes from:

• Modified logic
• Changed joins
• Earlier filtering
• Duplicate elimination
• Aggregation
• Refactoring mistakes

Understanding this distinction helps focus debugging efforts where they matter most.

Best Practices Checklist

When comparing CTEs and subqueries:

✅ Compare row counts after every step

✅ Validate JOIN conditions

✅ Compare primary keys

✅ Review filter placement

✅ Check aggregation order

✅ Inspect execution plans

✅ Test with representative datasets

✅ Keep refactoring incremental

✅ Document logical assumptions

✅ Benchmark performance separately from correctness

Common Mistakes to Avoid

Avoid:

❌ Assuming CTEs automatically change query behavior

❌ Moving filters without understanding the impact

❌ Using DISTINCT to hide duplicate problems

❌ Ignoring NULL comparisons

❌ Changing JOIN types unintentionally

❌ Debugging only the final result

❌ Comparing counts without comparing actual rows

Readability and Correctness Go Together

CTEs make SQL easier to understand, review, and maintain, especially for large analytical queries. However, readability improvements should never come at the expense of correctness. Every refactoring should be accompanied by careful validation to ensure that business logic remains unchanged.

Well-structured SQL is valuable only when it produces accurate results.

Trust the Data, Not Assumptions

When two SQL queries return different row counts, assume there is a logical difference until proven otherwise. By comparing intermediate results, reviewing joins and filters, and validating execution plans, you can systematically identify the source of the discrepancy rather than relying on guesswork.

Developing this disciplined approach will help you write SQL that is not only easier to maintain but also more reliable in production environments.

Frequently Asked Questions (FAQ)

Are CTEs faster than subqueries?

Not necessarily. Performance depends on the database engine, query optimizer, indexes, and data distribution. Modern optimizers often transform logically equivalent CTEs and subqueries into similar execution plans, though some engines may materialize CTEs under certain conditions.

Why do my CTE and subquery return different row counts?

The most common causes are changes introduced during refactoring, such as different join conditions, filter placement, aggregation order, duplicate elimination, or NULL handling. The CTE syntax itself is rarely the direct cause.

How can I find where rows disappear?

Count rows after each logical step, inspect intermediate CTEs, compare primary keys instead of only totals, and review execution plans. This incremental approach quickly identifies the stage where the result set changes.

Should I always use CTEs instead of subqueries?

Not always. CTEs generally improve readability and maintainability for complex queries, while simple subqueries may be more concise. Choose the approach that best communicates the query's intent without sacrificing correctness or performance.

Wrapping Summary

Common Table Expressions and subqueries are both powerful SQL constructs, and when written with identical logic, they should generally produce the same results. If your row counts differ after converting a subquery into a CTE, the root cause is almost always a change in query logic rather than the SQL construct itself.

By validating intermediate results, comparing primary keys, reviewing joins and filters, and analyzing execution plans, you can confidently refactor complex SQL while preserving correctness. A methodical debugging process not only resolves row count discrepancies but also leads to cleaner, more maintainable, and more reliable database queries.