MySQL左连接和联合

Question

具有如下的数据库结构：

小汽车

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的情况下有一种合理的方法来做这件事。

下面是一种不合理的方法，使用cross join和过滤：

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在您的版本中所做的工作。

Answer 2

如果您不想使用UNION，那么临时表如何？

    SELECT *
    into #Temp
    FROM car

    INSERT #Temp
    SELECT a.car_id, c.name, a.color
    FROM car_alt a INNER JOIN
         car c ON c.id = a.car_id

    SELECT *
    FROM #Temp
    ORDER BY id, name

请不要忘记在完成后删除TABLE #Temp --这是很好的清理方法。