左连接到具有所有可能值的表一直？

Let's say I have 5 tables: [AllStates], [FerrariStates], [ToyotaStates], [FordStates], [TelsaStates].

假设我有5个表：[AllStates]，[FerrariStates]，[ToyotaStates]，[FordStates]，[TelsaStates]。

Each table is composed of just one column: state. [AllStates] has all 50 states, the rest may, or may not, have all states.

每个表只由一列组成：状态。 [AllStates]拥有全部50个州，其余可能或可能不拥有所有州。

My result will have exactly 50 rows, and each column will show if there's a Toyota, Ford, Ferrari, Telsa. So the result will have 5 columns.

我的结果将有50行，每列将显示是否有丰田，福特，法拉利，Telsa。所以结果将有5列。

So if I wanted to show all 50 states, with a 2nd column showing which have Ferraris, then my query would look something like this:

因此，如果我想要显示所有50个州，第二列显示哪个有法拉利，那么我的查询看起来像这样：

select [AllStates].state, [FerrariStates].state from
[AllStates] left join [FerrariStates] on
[AllStates].state = [FerrariStates].state

But now I want to include [TelsaStates]. Should I left join with [AllStates] or [FerrariStates]? Or should it be an inner join?

但现在我想包括[TelsaStates]。我是否应该加入[AllStates]或[FerrariStates]？或者它应该是内部联接？

The same question would apply with [ToyotaStates] and [FordStates]. Should it be a left join or an inner join? And with which table should I do the join?

同样的问题适用于[ToyotaStates]和[FordStates]。它应该是左连接还是内连接？我应该用哪个表加入？

1 个解决方案

#1

Assuming there is at most one row per state, you want left joins to the first table:

假设每个状态最多有一行，则需要左连接到第一个表：

select a.state,
       (case when f.state is not null then 1 else 0 end) as is_ferrari,
       (case when t.state is not null then 1 else 0 end) as is_tesla,
       . . .
from AllStates a left join
     FerrariStates f
     on f.state = a.state left join
     TeslaStates t
     on t.state = a.state left join
     . . .;

The first table is AllStates. That is the population. The subsequent joins should all be left joins back to AllStates, to bring in the values from each of the tables.

第一个表是AllStates。那就是人口。后续连接应该全部保持连接回AllStates，以从每个表中引入值。

Notice:

注意：

The use of table aliases so the queries are easier to write and to read.
使用表别名使查询更容易编写和读取。
The elimination of square braces, which are not necessary.
消除方括号，这是不必要的。
The use of a case expression to get 0/1 values.
使用case表达式获取0/1值。

#1