mysql - 将使用子查询的查询重写为使用联接的查询时导致 SQL 错误

Question

原始查询:

SELECT      o.offering_number,
        o.english_description,
        o.french_description,
        fop.price_amount,
        fop.price_type_code,
        fop.price_status_code,
        fop.offering_id,
        (SELECT fop1.price_amount from facility_offering_price fop1
                WHERE fop.offering_id = fop1.Offering_Id
                    AND fop1.price_type_code = 5
                AND fop1.price_status_code = 3
            ) as 'priceAmount'
            from facility_offering_price fop
            join offering o on fop.offering_id = o.offering_id
                WHERE fop.price_start_date = '15-10-28'
                AND fop.price_status_code IN (1,2)
                /*AND (price_status_code IS NULL)*/
                AND fop.price_type_code = 5
                /*AND (o.offering_number IS NULL)*/
                    ORDER BY o.offering_number ASC, fop.price_sequence_number ASC;

它产生一个条目的结果。

查询结果:

SELECT      o.offering_number,
        o.english_description,
        o.french_description,
        fop.price_amount,
        fop2.price_amount,
        fop.price_type_code,
        fop.offering_id,
        fop2.offering_id
            from facility_offering_price fop
            join offering o on fop.offering_id = o.offering_id
            inner join
                (select
                    fop1.offering_id,
                    fop1.price_amount
                            from facility_offering_price fop1
                            WHERE fop1.price_type_code = 5
                            AND fop1.price_status_code = 3
                ) fop2 on fop.offering_id = fop2.offering_id
                WHERE fop.price_start_date = '15-10-28'
                AND fop.price_status_code IN (1,2)
                /*AND (price_status_code IS NULL)*/
                AND fop.price_type_code = 5
                /*AND (o.offering_number IS NULL)*/
                    ORDER BY o.offering_number ASC, fop.price_sequence_number ASC;

结果集为空。但是，如果我请求 fop1.price_status_code = 1，就会找到一个条目。

我无法理解这个问题，非常感谢您的帮助。

Answer 1

尝试使用LEFT JOIN代替。从 SELECT a, subquery AS val FROM ... 到联接的转换可以通过这种方式更准确地反射(reflect)出来。当子查询没有结果时，原始查询将返回带有 NULL val 的行；您的版本最终完全省略了这些行。