MySQL 仅在现有行上加入

Question

我有以下查询

SELECT custconcompany, custconfirstname, custconlastname, custconemail, custconphone, shipaddress1, shipaddress2, shipcity, stateabbrv, shipzip, countryname, websitecheck.formfieldfieldvalue websitevalue, excludecheck.formfieldfieldvalue excludevalue
            FROM obcisc_customers 
            JOIN ( (obcisc_shipping_addresses JOIN obcisc_countries 
                    ON obcisc_shipping_addresses.shipcountryid = obcisc_countries.countryid)
                LEFT JOIN obcisc_country_states 
                    ON obcisc_shipping_addresses.shipstateid = obcisc_country_states.stateid 
                LEFT JOIN obcisc_formfieldsessions websitecheck
                    ON obcisc_shipping_addresses.shipformsessionid = websitecheck.formfieldsessioniformsessionid 
                LEFT JOIN obcisc_formfieldsessions excludecheck
                    ON obcisc_shipping_addresses.shipformsessionid = excludecheck.formfieldsessioniformsessionid) 
ON obcisc_customers.customerid = obcisc_shipping_addresses.shipcustomerid 
            WHERE custgroupid = 11
            AND websitecheck.formfieldfieldid = 24
            AND excludecheck.formfieldfieldid = 30
            AND excludecheck.formfieldfieldvalue != 'a:1:{i:0;s:3:"Yes";}'
            ORDER BY shipstate, shipcity

这非常有效，除了我还需要它返回不存在“excludecheck.formfieldfieldid=30”的行...现在它不返回它们

Answer 1

编写 LEFT JOIN 时，要连接的表上的任何条件都应放入 ON 子句中。如果将其放入 WHERE 子句中，则会过滤掉第一个表中在第二个表中没有匹配行的结果，因为 NULL来自外连接的值将不符合条件。

SELECT custconcompany, custconfirstname, custconlastname, custconemail, custconphone, shipaddress1, shipaddress2, shipcity, stateabbrv, shipzip, countryname, websitecheck.formfieldfieldvalue websitevalue, excludecheck.formfieldfieldvalue excludevalue
FROM obcisc_customers 
JOIN obcisc_shipping_addresses
    ON obcisc_customers.customerid = obcisc_shipping_addresses.shipcustomerid 
JOIN obcisc_countries 
    ON obcisc_shipping_addresses.shipcountryid = obcisc_countries.countryid)
LEFT JOIN obcisc_country_states 
    ON obcisc_shipping_addresses.shipstateid = obcisc_country_states.stateid 
LEFT JOIN obcisc_formfieldsessions websitecheck
    ON obcisc_shipping_addresses.shipformsessionid = websitecheck.formfieldsessioniformsessionid 
       AND websitecheck.formfieldfieldid = 24
LEFT JOIN obcisc_formfieldsessions excludecheck
    ON obcisc_shipping_addresses.shipformsessionid = excludecheck.formfieldsessioniformsessionid
       AND excludecheck.formfieldfieldid = 30
       AND excludecheck.formfieldfieldvalue != 'a:1:{i:0;s:3:"Yes";}') 
WHERE custgroupid = 11
ORDER BY shipstate, shipcity

另一种方法是将 (excludecheck.formfieldfieldid = 30 OR exclucheck.formfieldfieldid IS NULL) 放入 WHERE 子句中。但这更冗长，而且我相信 MySQL 更难优化，特别是如果您有多个像这样连接的表。