WHERE子句在子查询内外执行结果不一致问题分析

Let's break down exactly why these two queries return different results—it all boils down to when you apply your filters relative to the GROUP BY operation, plus a critical quirk in how MySQL handles non-standard GROUP BY syntax.

First, Let's Restate the Two Queries Clearly #

Query 1 (Filters Outside Subquery):

SELECT * 
FROM (
    SELECT d.* 
    FROM downloads AS d 
    LEFT JOIN ps_customer AS pc ON d.id_customer = pc.id_customer 
    WHERE pc.active = 1 AND d.id_customer IS NOT NULL 
    GROUP BY id_product, id_customer
) AS tmp 
WHERE YEAR(tmp.date_download) = 2015 AND tmp.name = 'Antescofo';

Query 2 (Filters Inside Subquery):

SELECT * 
FROM (
    SELECT d.* 
    FROM downloads AS d 
    LEFT JOIN ps_customer AS pc ON d.id_customer = pc.id_customer 
    WHERE pc.active = 1 AND d.id_customer IS NOT NULL 
      AND YEAR(d.date_download) = 2015 AND d.name = 'Antescofo'
    GROUP BY id_product, id_customer
) AS tmp;

Core Differences Explained #

1. Filter Timing Changes the Dataset Used for Grouping

• Query 1 works like this:

  1. First, it pulls all downloads records linked to active customers (pc.active=1 and d.id_customer IS NOT NULL).
  2. Then it groups those records by id_product and id_customer. Because you're selecting d.* but only grouping by two columns, MySQL (with ONLY_FULL_GROUP_BY disabled, the default in older versions) will pick a random row from each group to return in the subquery.
  3. Finally, it filters those randomly selected rows to keep only where the date is 2015 and name is 'Antescofo'.

• Query 2 works differently:

  1. First, it narrows down the downloads records to only those that meet all criteria: active customers, 2015 download date, and name 'Antescofo'.
  2. Then it groups this smaller, pre-filtered dataset by id_product and id_customer—again picking a random row from each group, but all rows in the groups already meet the date/name filters.

The key here is that in Query 1, you might have groups where some rows are 2015/'Antescofo' and others aren't—but MySQL could pick a non-matching row for the subquery result. That row would then get filtered out in the outer WHERE clause, reducing your final results. In Query 2, you eliminate those non-matching rows before grouping, so every row in the groups is eligible.

2. The Non-Standard GROUP BY Is a Hidden Culprit

Your subquery uses SELECT d.* with GROUP BY id_product, id_customer—this violates SQL standard rules, which require that every column in your SELECT clause is either in the GROUP BY or wrapped in an aggregate function (like MAX(), MIN()).

MySQL allows this if ONLY_FULL_GROUP_BY is turned off, but it doesn't guarantee consistency: it just grabs whichever row from the group it finds first. This randomness is why moving the filters inside vs outside changes your results—you're dealing with different sets of "random" rows.

How to Verify This #

To confirm, run just the subquery from Query 1 and look at the date_download and name columns. You'll likely see some rows where the date isn't 2015 or the name isn't 'Antescofo'—those rows get dropped in the outer WHERE clause. Compare that to the subquery from Query 2: every row will already meet the date/name criteria, so none get dropped.

Fixing the Query for Consistency #

If you want consistent results regardless of filter placement, follow SQL standards:

• Either add all columns from d.* to your GROUP BY (not ideal if there are many columns), or
• Use aggregate functions to specify which row from each group you want (e.g., MAX(d.date_download), MIN(d.name)), or
• If you just want distinct (id_product, id_customer) pairs that meet all criteria, use DISTINCT instead of GROUP BY (since you're not aggregating anything here):

SELECT DISTINCT d.* 
FROM downloads AS d 
LEFT JOIN ps_customer AS pc ON d.id_customer = pc.id_customer 
WHERE pc.active = 1 AND d.id_customer IS NOT NULL 
  AND YEAR(d.date_download) = 2015 AND d.name = 'Antescofo';

内容的提问来源于stack exchange，提问作者ryancey