在Oracle 11数据库中,我有2个表(CAR和CAR_BRAND):
CAR具有以下特点
car_id |brand
1 |Audi
2 |BMW
3 |VW
CAR_BRAND具有以下功能
brand
Audi
Bmw
使用内部连接在两个表上连接很简单
select c.* from car c
inner join car_brand cb on cb.brand = c.brand;
我有一个要求,当CAR_BRAND表为空时,要选择 CAR 中的所有记录,否则从与CAR_BRAND表连接的 CAR 中选择 *。
这可以按如下方式完成:
SELECT c.* FROM car c JOIN car_brand cb ON cb.brand = c.brand
UNION ALL
SELECT c.*
FROM car c
WHERE NOT EXISTS
(SELECT c.* FROM car c JOIN car_brand cb ON cb.brand = c.brand
);
但是,这工作正常,是否有更好,更高效的SQL来获得最终结果,而不是使用UNION ALL?
您可以在检索汽车时使用变量来计算car_brand记录,然后使用or
测试这两种情况:
select car_id, brand
from (
select c.*,
cb.brand as match_brand,
@r := @r + if(cb.brand is null,0,1)
from car c
cross join (select @r := 0) init
left join car_brand cb
on cb.brand = c.brand
) base
where @r = 0 or match_brand is not null