MySQL与SQL Server中子查询和内连接的差异及查询异常排查

问题分析 #

你的核心问题是MySQL与SQL Server对同一查询逻辑的处理差异，以及SQL Server中出现的行数缺失和性能瓶颈。先拆解原查询的目标逻辑：

• 需要获取所有jobs行的Status、Number，以及对应job状态为INVOICED时的任意一条invoicedDate（无匹配则返回NULL）。
• 在MySQL中，子查询无匹配时返回NULL，主查询会保留所有行；但在SQL Server中，你的查询意外过滤掉了子查询为NULL的行，同时嵌套子查询的写法导致性能暴跌（相当于对183000行jobs逐行执行子查询，属于嵌套循环的低效操作）。

为什么SQL Server行数不匹配？ #

大概率是两种情况之一：

1. 你改写查询时误用了INNER JOIN，自动过滤掉了无匹配的jobs行，只保留了有对应invoices且状态为INVOICED的行（也就是那1700条）；
2. 原嵌套子查询的写法在SQL Server中被优化器误判，意外触发了行过滤（虽然理论上不该发生，但嵌套子查询的执行计划本身就容易出现数据库间的差异）。

解决方案：用LEFT JOIN + 聚合/窗口函数替代嵌套子查询 #

这种写法在MySQL 8.0+和SQL Server中完全兼容，既保证返回所有jobs行，又能大幅提升性能（避免逐行嵌套查询）。

方案1：取最新发票日期（最常用场景） #

如果你的需求是获取对应job的最新发票日期，直接用聚合函数MAX即可，写法最简洁：

SELECT 
    j.status AS "Status",
    j.number AS "Number",
    i.max_invoiced_date AS "Invoiced Date"
FROM jobs AS j
LEFT JOIN (
    SELECT 
        i2.jobKey,
        MAX(i2.invoicedDate) AS max_invoiced_date
    FROM invoices AS i2
    INNER JOIN jobs AS j2 ON i2.jobKey = j2.id
    WHERE j2.status = 'INVOICED'
    GROUP BY i2.jobKey
) AS i ON j.id = i.jobKey

方案2：取任意一条发票日期（匹配原TOP 1逻辑） #

如果确实需要任意一条发票日期（不关心新旧），可以用窗口函数FIRST_VALUE实现：

SELECT 
    j.status AS "Status",
    j.number AS "Number",
    i.invoicedDate AS "Invoiced Date"
FROM jobs AS j
LEFT JOIN (
    SELECT 
        i2.jobKey,
        FIRST_VALUE(i2.invoicedDate) OVER (PARTITION BY i2.jobKey) AS invoicedDate
    FROM invoices AS i2
    INNER JOIN jobs AS j2 ON i2.jobKey = j2.id
    WHERE j2.status = 'INVOICED'
    GROUP BY i2.jobKey, i2.invoicedDate -- 去重避免重复行
) AS i ON j.id = i.jobKey

写法说明 #

• LEFT JOIN：确保主查询的所有jobs行都被保留，即使没有匹配的invoices数据，Invoiced Date列会返回NULL；
• 子查询聚合/窗口函数：先对invoices按jobKey分组处理，再与主表连接，避免了逐行嵌套查询的性能损耗；
• 索引优化：确保jobs.id、invoices.jobKey上有主键或普通索引，这是提升连接性能的关键。

思路验证 #

你的初始需求逻辑是完全正确的——获取所有jobs行+对应INVOICED状态的发票日期，问题出在嵌套子查询的写法上。改用JOIN+聚合/窗口函数的方式，既能保证跨数据库的逻辑一致性，又能彻底解决性能和行数匹配的问题。

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