MySQL 左连接和并集

Question

这个问题已经有答案了:

Another way to improve the SQL Query to avoid union? (4 个回答)

已关闭 5 年前。

拥有这样的数据库结构:

汽车

id, name, color
1, mercedes, red
2, volvo, blue
3, bmw, green

car_alt

id, car_id, color
1, 1, green
2, 1, blue
3, 2, red
4, 2, blue

我想要得到这样的结果:

id, name, color
1, mercedes, red
1, mercedes, green
1, mercedes, blue
2, volvo, blue
2, volvo, red
2, volvo, blue
3, bmw, green

目前我使用这个查询:

SELECT id, name, color
FROM car
UNION
SELECT car.id, car.name, car_alt.color
FROM car
INNER JOIN car_alt ON car.id = car_alt.id_car

有没有办法不使用 UNION 来做到这一点？ ？我想知道是否可以只使用LEFT JOIN添加一些空行，例如:

SELECT car.id, car.name, COALESCE(car_alt.color, car.color)
FROM car
LEFT JOIN car_alt ON car.id = car_alt.id_car OR 'GET EMPTY ROW'

解释为什么当我必须将更多表连接到表 car 时我什至会问这个问题，我的查询如下所示:

SELECT id, name, color,
t1.col, t2.col, t3.col, ...
FROM car
JOIN t1
JOIN t2
JOIN t3
...
UNION
SELECT car.id, car.name, car_alt.color,
t1.col, t2.col, t3.col, ...
FROM car
JOIN t1
JOIN t2
JOIN t3
...
INNER JOIN car_alt ON car.id = car_alt.id_car

所以这里面有很多重复。我想到的唯一其他方法是移动 UNION子查询，例如

SELECT car.id, car.name, union.color,
t1.col, t2.col, t3.col, ...
FROM car
JOIN t1
JOIN t2
JOIN t3
...
INNER JOIN (
SELECT car.id, car.color
FROM car
UNION
SELECT car_alt.car_id, car_alt.color
FROM car_alt) union ON car.id = union.id

Answer 1

我认为没有 union/union all 就没有合理的方法来做到这一点。

这是一种不合理的方法，使用交叉连接和过滤:

select c.id, c.name, co.color
from (select distinct id, name from car) c cross join
     (select distinct color from car) co 
where exists (select 1 from car c2 where c2.id = c.id and c2.color = co.color) or
      exists (select 1 from car_alt ca where ca.id = c.id and ca.color = co.color);

这基本上重构了逻辑，因此 or 的功能与您版本中的 union all 的功能相同。