MySQL:如果其中一个返回 NULL，则 INNER JOIN 会使 2 个 LEFT JOIN 崩溃

Question

查询中:

SELECT
    i.id, i.title, i.description,
    cities.name as city,
    GROUP_CONCAT(DISTINCT station.name) as station,
    GROUP_CONCAT(DISTINCT p.url) as photos
FROM
    items i
INNER JOIN
    cities ON cities.id = i.city_id
LEFT JOIN
    item_photos p ON p.item_id = i.id
LEFT JOIN
    item_stations s ON s.item_id = i.id
INNER JOIN
    stations ON stations.id = s.station_id
WHERE i.id = ?
LIMIT 1

如果表 item_stations 中的行不存在，则两个 LEFT JOIN 均有效:返回照片，对于电台则返回 NULL。但在这种情况下使用 INNER JOIN 查询将为照片和电台返回 NULL。如果 item_stations 中没有使用 s.item_id = i.id 所需的行，我应该如何重写查询以表示 INNER JOIN 不连接表？

Answer 1

如果您确实需要在 item_stations 和 stations 表之间进行 INNER JOIN，那么您可能需要考虑在子查询中使用 INNER JOIN:

SELECT
    i.id, i.title, i.description,
    cities.name as city,
    GROUP_CONCAT(DISTINCT s.name) as station,
    GROUP_CONCAT(DISTINCT p.url) as photos
FROM items i
INNER JOIN cities 
  ON cities.id = i.city_id
LEFT JOIN item_photos p 
  ON p.item_id = i.id
LEFT JOIN
(
  select item_id, name
  from item_stations s 
  INNER JOIN stations 
    ON stations.id = s.station_id
) 
  ON s.item_id = i.id
WHERE i.id = ?
LIMIT 1

否则我建议在两者之间使用 LEFT JOIN:

SELECT
    i.id, i.title, i.description,
    cities.name as city,
    GROUP_CONCAT(DISTINCT stations.name) as station,
    GROUP_CONCAT(DISTINCT p.url) as photos
FROM items i
INNER JOIN cities 
  ON cities.id = i.city_id
LEFT JOIN item_photos p 
  ON p.item_id = i.id
LEFT JOIN item_stations s 
  ON s.item_id = i.id
LEFT JOIN stations 
  ON stations.id = s.station_id
WHERE i.id = ?
LIMIT 1