MS Access中Left Join被识别为Inner Join的问题求助

问题背景 #

我对Access SQL不熟悉，使用MS Access时碰到个问题：Left Join被当成Inner Join执行了，原因是Access把左连接的条件放到了连接子句外面。查了相关资料没找到解决办法，求帮忙！

Access可视化设计器生成的原始查询 #

SELECT DISTINCT 
SATURN_SPRIDEN.SPRIDEN_ID,
SATURN_SPRIDEN.SPRIDEN_LAST_NAME, 
SATURN_SPRIDEN.SPRIDEN_FIRST_NAME, 
STUDENT_TYPE.STUDENT_TYPE_DESCRIPTION, 
SATURN_SGBSTDN.SGBSTDN_TERM_CODE_EFF, 
FAISMGR_RPRAWRD.RPRAWRD_OFFER_AMT AS Opportunity_Offered, 
FAISMGR_RPRAWRD_1.RPRAWRD_OFFER_AMT AS Lottery_Offered, 
FAISMGR_RPRAWRD_2.RPRAWRD_OFFER_AMT AS FSEOG_Offered, 
FAISMGR_RPRAWRD_3.RPRAWRD_OFFER_AMT AS Pell_Offered
FROM ((((SATURN_SPRIDEN INNER JOIN (SATURN_SGBSTDN INNER JOIN STUDENT_TYPE ON SATURN_SGBSTDN.SGBSTDN_STYP_CODE = STUDENT_TYPE.STUDENT_TYPE_CODE) ON SATURN_SPRIDEN.SPRIDEN_PIDM = SATURN_SGBSTDN.SGBSTDN_PIDM) 
LEFT JOIN FAISMGR_RPRAWRD ON SATURN_SPRIDEN.SPRIDEN_PIDM = FAISMGR_RPRAWRD.RPRAWRD_PIDM) 
LEFT JOIN FAISMGR_RPRAWRD AS FAISMGR_RPRAWRD_1 ON SATURN_SPRIDEN.SPRIDEN_PIDM = FAISMGR_RPRAWRD_1.RPRAWRD_PIDM) 
LEFT JOIN FAISMGR_RPRAWRD AS FAISMGR_RPRAWRD_2 ON SATURN_SPRIDEN.SPRIDEN_PIDM = FAISMGR_RPRAWRD_2.RPRAWRD_PIDM) 
LEFT JOIN FAISMGR_RPRAWRD AS FAISMGR_RPRAWRD_3 ON SATURN_SPRIDEN.SPRIDEN_PIDM = FAISMGR_RPRAWRD_3.RPRAWRD_PIDM
WHERE (((SATURN_SGBSTDN.SGBSTDN_TERM_CODE_EFF)=[entry term]) AND ((FAISMGR_RPRAWRD.RPRAWRD_AIDY_CODE)=[aidy]) AND ((FAISMGR_RPRAWRD.RPRAWRD_FUND_CODE) In ('SNMOS','SNMOSG','SNMOSR','SNMOSF')) AND ((FAISMGR_RPRAWRD_1.RPRAWRD_AIDY_CODE)=[aidy]) AND ((FAISMGR_RPRAWRD_1.RPRAWRD_FUND_CODE)='SLOTT') AND ((FAISMGR_RPRAWRD_2.RPRAWRD_AIDY_CODE)=[aidy]) AND ((FAISMGR_RPRAWRD_2.RPRAWRD_FUND_CODE)='FSEOG') AND ((FAISMGR_RPRAWRD_3.RPRAWRD_AIDY_CODE)=[aidy]) AND ((FAISMGR_RPRAWRD_3.RPRAWRD_FUND_CODE)='FPELL'));

我在Oracle SQL中实现预期结果的写法 #

SELECT DISTINCT 
    SATURN_SPRIDEN.SPRIDEN_ID,
    SATURN_SPRIDEN.SPRIDEN_LAST_NAME, 
    SATURN_SPRIDEN.SPRIDEN_FIRST_NAME, 
    STUDENT_TYPE.STUDENT_TYPE_DESCRIPTION, 
    SATURN_SGBSTDN.SGBSTDN_TERM_CODE_EFF, 
    FAISMGR_RPRAWRD.RPRAWRD_OFFER_AMT AS Opportunity_Offered, 
    FAISMGR_RPRAWRD_1.RPRAWRD_OFFER_AMT AS Lottery_Offered, 
    FAISMGR_RPRAWRD_2.RPRAWRD_OFFER_AMT AS FSEOG_Offered, 
    FAISMGR_RPRAWRD_3.RPRAWRD_OFFER_AMT AS Pell_Offered
FROM 
    SATURN_SPRIDEN
    INNER JOIN SATURN_SGBSTDN 
        ON SATURN_SPRIDEN.SPRIDEN_PIDM = SATURN_SGBSTDN.SGBSTDN_PIDM
    INNER JOIN STUDENT_TYPE 
        ON SATURN_SGBSTDN.SGBSTDN_STYP_CODE = STUDENT_TYPE.STUDENT_TYPE_CODE
    LEFT JOIN FAISMGR_RPRAWRD 
        ON SATURN_SPRIDEN.SPRIDEN_PIDM = FAISMGR_RPRAWRD.RPRAWRD_PIDM 
        AND FAISMGR_RPRAWRD.RPRAWRD_AIDY_CODE = [aidy] 
        AND FAISMGR_RPRAWRD.RPRAWRD_FUND_CODE IN ('SNMOS', 'SNMOSG', 'SNMOSR', 'SNMOSF')
    LEFT JOIN FAISMGR_RPRAWRD AS FAISMGR_RPRAWRD_1 
        ON SATURN_SPRIDEN.SPRIDEN_PIDM = FAISMGR_RPRAWRD_1.RPRAWRD_PIDM 
        AND FAISMGR_RPRAWRD_1.RPRAWRD_AIDY_CODE = [aidy] 
        AND FAISMGR_RPRAWRD_1.RPRAWRD_FUND_CODE = 'SLOTT'
    LEFT JOIN FAISMGR_RPRAWRD AS FAISMGR_RPRAWRD_2 
        ON SATURN_SPRIDEN.SPRIDEN_PIDM = FAISMGR_RPRAWRD_2.RPRAWRD_PIDM 
        AND FAISMGR_RPRAWRD_2.RPRAWRD_AIDY_CODE = [aidy] 
        AND FAISMGR_RPRAWRD_2.RPRAWRD_FUND_CODE = 'FSEOG'
    LEFT JOIN FAISMGR_RPRAWRD AS FAISMGR_RPRAWRD_3 
        ON SATURN_SPRIDEN.SPRIDEN_PIDM = FAISMGR_RPRAWRD_3.RPRAWRD_PIDM 
        AND FAISMGR_RPRAWRD_3.RPRAWRD_AIDY_CODE = [aidy] 
        AND FAISMGR_RPRAWRD_3.RPRAWRD_FUND_CODE = 'FPELL'
WHERE 
    SATURN_SGBSTDN.SGBSTDN_TERM_CODE_EFF = [entry term];

尝试过的方案及问题 #

我试了@jarlh给出的以下写法，但Access直接崩溃，没有报错：

SELECT DISTINCT 
SATURN_SPRIDEN.SPRIDEN_ID,
SATURN_SPRIDEN.SPRIDEN_LAST_NAME, 
SATURN_SPRIDEN.SPRIDEN_FIRST_NAME, 
STUDENT_TYPE.STUDENT_TYPE_DESCRIPTION, 
SATURN_SGBSTDN.SGBSTDN_TERM_CODE_EFF, 
FAISMGR_RPRAWRD.RPRAWRD_OFFER_AMT AS Opportunity_Offered, 
FAISMGR_RPRAWRD_1.RPRAWRD_OFFER_AMT AS Lottery_Offered, 
FAISMGR_RPRAWRD_2.RPRAWRD_OFFER_AMT AS FSEOG_Offered, 
FAISMGR_RPRAWRD_3.RPRAWRD_OFFER_AMT AS Pell_Offered
FROM (((((( SATURN_SPRIDEN 
INNER JOIN SATURN_SGBSTDN ON (SATURN_SPRIDEN.SPRIDEN_PIDM = SATURN_SGBSTDN.SGBSTDN_PIDM))
INNER JOIN STUDENT_TYPE ON (SATURN_SGBSTDN.SGBSTDN_STYP_CODE = STUDENT_TYPE.STUDENT_TYPE_CODE)) 
LEFT JOIN FAISMGR_RPRAWRD ON (SATURN_SPRIDEN.SPRIDEN_PIDM = FAISMGR_RPRAWRD.RPRAWRD_PIDM AND FAISMGR_RPRAWRD.RPRAWRD_AIDY_CODE=[aidy] AND FAISMGR_RPRAWRD.RPRAWRD_FUND_CODE In ('SNMOS','SNMOSG','SNMOSR','SNMOSF'))) 
LEFT JOIN FAISMGR_RPRAWRD AS FAISMGR_RPRAWRD_1 ON (SATURN_SPRIDEN.SPRIDEN_PIDM = FAISMGR_RPRAWRD_1.RPRAWRD_PIDM AND FAISMGR_RPRAWRD_1.RPRAWRD_AIDY_CODE=[aidy] AND FAISMGR_RPRAWRD_1.RPRAWRD_FUND_CODE='SLOTT'))
LEFT JOIN FAISMGR_RPRAWRD AS FAISMGR_RPRAWRD_2 ON (SATURN_SPRIDEN.SPRIDEN_PIDM = FAISMGR_RPRAWRD_2.RPRAWRD_PIDM AND FAISMGR_RPRAWRD_2.RPRAWRD_AIDY_CODE=[aidy] AND FAISMGR_RPRAWRD_2.RPRAWRD_FUND_CODE ='FSEOG'))
LEFT JOIN FAISMGR_RPRAWRD AS FAISMGR_RPRAWRD_3 ON (SATURN_SPRIDEN.SPRIDEN_PIDM = FAISMGR_RPRAWRD_3.RPRAWRD_PIDM AND FAISMGR_RPRAWRD_3.RPRAWRD_AIDY_CODE=[aidy] AND FAISMGR_RPRAWRD_3.RPRAWRD_FUND_CODE='FPELL'))
WHERE ((SATURN_SGBSTDN.SGBSTDN_TERM_CODE_EFF)=[entry term]) AND ((FAISMGR_RPRAWRD.RPRAWRD_AIDY_CODE)=[aidy]);

解决办法 #

Access SQL对左连接的语法处理比较严格，核心问题是不能在WHERE子句中对左连接的表加过滤条件，否则会自动转为内连接。正确的写法是把所有左连接表的过滤条件全部移到ON子句中，同时注意Access要求多表连接时必须用括号明确嵌套关系，但不要过度嵌套导致解析崩溃。

下面是适配Access SQL的正确写法：

SELECT DISTINCT 
    SATURN_SPRIDEN.SPRIDEN_ID,
    SATURN_SPRIDEN.SPRIDEN_LAST_NAME, 
    SATURN_SPRIDEN.SPRIDEN_FIRST_NAME, 
    STUDENT_TYPE.STUDENT_TYPE_DESCRIPTION, 
    SATURN_SGBSTDN.SGBSTDN_TERM_CODE_EFF, 
    FAISMGR_RPRAWRD.RPRAWRD_OFFER_AMT AS Opportunity_Offered, 
    FAISMGR_RPRAWRD_1.RPRAWRD_OFFER_AMT AS Lottery_Offered, 
    FAISMGR_RPRAWRD_2.RPRAWRD_OFFER_AMT AS FSEOG_Offered, 
    FAISMGR_RPRAWRD_3.RPRAWRD_OFFER_AMT AS Pell_Offered
FROM 
    ((((SATURN_SPRIDEN 
    INNER JOIN SATURN_SGBSTDN ON SATURN_SPRIDEN.SPRIDEN_PIDM = SATURN_SGBSTDN.SGBSTDN_PIDM)
    INNER JOIN STUDENT_TYPE ON SATURN_SGBSTDN.SGBSTDN_STYP_CODE = STUDENT_TYPE.STUDENT_TYPE_CODE)
    LEFT JOIN FAISMGR_RPRAWRD ON 
        SATURN_SPRIDEN.SPRIDEN_PIDM = FAISMGR_RPRAWRD.RPRAWRD_PIDM 
        AND FAISMGR_RPRAWRD.RPRAWRD_AIDY_CODE = [aidy] 
        AND FAISMGR_RPRAWRD.RPRAWRD_FUND_CODE IN ('SNMOS','SNMOSG','SNMOSR','SNMOSF'))
    LEFT JOIN FAISMGR_RPRAWRD AS FAISMGR_RPRAWRD_1 ON 
        SATURN_SPRIDEN.SPRIDEN_PIDM = FAISMGR_RPRAWRD_1.RPRAWRD_PIDM 
        AND FAISMGR_RPRAWRD_1.RPRAWRD_AIDY_CODE = [aidy] 
        AND FAISMGR_RPRAWRD_1.RPRAWRD_FUND_CODE = 'SLOTT')
    LEFT JOIN FAISMGR_RPRAWRD AS FAISMGR_RPRAWRD_2 ON 
        SATURN_SPRIDEN.SPRIDEN_PIDM = FAISMGR_RPRAWRD_2.RPRAWRD_PIDM 
        AND FAISMGR_RPRAWRD_2.RPRAWRD_AIDY_CODE = [aidy] 
        AND FAISMGR_RPRAWRD_2.RPRAWRD_FUND_CODE = 'FSEOG')
    LEFT JOIN FAISMGR_RPRAWRD AS FAISMGR_RPRAWRD_3 ON 
        SATURN_SPRIDEN.SPRIDEN_PIDM = FAISMGR_RPRAWRD_3.RPRAWRD_PIDM 
        AND FAISMGR_RPRAWRD_3.RPRAWRD_AIDY_CODE = [aidy] 
        AND FAISMGR_RPRAWRD_3.RPRAWRD_FUND_CODE = 'FPELL'
WHERE 
    SATURN_SGBSTDN.SGBSTDN_TERM_CODE_EFF = [entry term];

关键说明 #

1. 所有针对左连接表FAISMGR_RPRAWRD及其别名的过滤条件，全部从WHERE移到对应的ON子句中，确保左连接逻辑生效。
2. 保留Access要求的嵌套括号结构，但避免多余的括号（比如每个ON子句外的括号），减少解析负担，防止崩溃。
3. DISTINCT保留，避免多表连接产生重复数据。

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