如果其中一个连接上没有值，MySQL 返回 null

Question

我有一个带有连接的sql查询，如果没有返回任何结果，我想为"aov.value gazlabel"显示NULL，而不是不返回任何内容，我尝试了左侧的外部连接 JOINeav_attribute_option_valueAS aov 但它没有返回任何内容。

查询：

SELECT pev.`entity_id`,
pev.`value`,
aov.`value` gazlabel
FROM `catalog_product_entity_varchar` AS pev
INNER JOIN `catalog_product_entity` AS pe
ON pe.`entity_id` = pev.`entity_id`
INNER JOIN `catalog_product_entity_int` AS pei
ON pei.`entity_id` = pe.`entity_id`
AND pei.`store_id` = pev.`store_id`
left JOIN `eav_attribute_option_value` AS aov
ON aov.`option_id` = pei.`value`
AND aov.`store_id` = pev.`store_id`
INNER JOIN `eav_attribute` as ea
ON ea.`attribute_id` = pei.`attribute_id`
WHERE pev.`attribute_id` = 71
AND pev.`store_id` IN ("0", "1")
AND ea.`attribute_code` = "front_label"
AND pe.`sku` LIKE "C10192"
ORDER BY `pev`.`store_id` DESC
LIMIT 1

Answer 1

可能是您的匹配失败了，而不是AOV的其他一些表，因此请使用更多的左连接

SELECT pev.`entity_id`,
pev.`value`,
aov.`value` gazlabel
FROM `catalog_product_entity_varchar` AS pev
LEFT JOIN `catalog_product_entity` AS pe
ON pe.`entity_id` = pev.`entity_id`
AND pe.`sku` LIKE "C10192"
LEFT JOIN `catalog_product_entity_int` AS pei
ON pei.`entity_id` = pe.`entity_id`
AND pei.`store_id` = pev.`store_id`
left JOIN `eav_attribute_option_value` AS aov
ON aov.`option_id` = pei.`value`
AND aov.`store_id` = pev.`store_id`
LEFT JOIN `eav_attribute` as ea
ON ea.`attribute_id` = pei.`attribute_id`
AND ea.`attribute_code` = "front_label"
WHERE pev.`attribute_id` = 71
AND pev.`store_id` IN ("0", "1")
ORDER BY `pev`.`store_id` DESC
LIMIT 1

或者如果只有类似的条件不匹配，则仅在第一个不匹配的表处减少左连接

SELECT pev.`entity_id`,
pev.`value`,
aov.`value` gazlabel
FROM `catalog_product_entity_varchar` AS pev
INNER JOIN `catalog_product_entity_int` AS pei
ON pei.`entity_id` = pe.`entity_id`
AND pei.`store_id` = pev.`store_id`
INNER JOIN `eav_attribute` as ea
ON ea.`attribute_id` = pei.`attribute_id`
AND ea.`attribute_code` = "front_label"
LEFT JOIN `catalog_product_entity` AS pe
ON pe.`entity_id` = pev.`entity_id`
AND pe.`sku` LIKE "C10192"
left JOIN `eav_attribute_option_value` AS aov
ON aov.`option_id` = pei.`value`
AND aov.`store_id` = pev.`store_id`
WHERE pev.`attribute_id` = 71
AND pev.`store_id` IN ("0", "1")
ORDER BY `pev`.`store_id` DESC
LIMIT 1