sql - 在 where 子句中使用同一组 ID 的 3 个查询

Question

假设我有 3 个查询：

select * from customers
where account_id in (select id from accounts where nrb in ('123', '234', '345', '456'));

select * from cards
where account_id in (select id from accounts where nrb in ('123', '234', '345', '456'));

select * from users
where account_id in (select id from accounts where nrb in ('123', '234', '345', '456'));

他们都使用相同的子查询来根据 nrb 获取帐户的 id。替换 3 个相同子查询的最佳方法是什么。我希望它看起来平滑清晰。

我们正在谈论 OracleDB 案例。

标签： sqloracle

Answer 1

为什么不直接加入他们..并为您需要的列使用别名..

就像是

select id, 
customers.id as customers_id,
cards.id as cards_id,
users.id as users_id,
customers.*, cards.*, users.*
from accounts 
left outer join customers
on customers.account_id = accounts.id
left outer join cards
on cards.account_id = accounts.id
left outer join users
on users.account_id = accounts.id
where nrb in ('123', '234', '345', '456'))
and (customers.id is not null or cards.id is not null or users.id is not null);