完全外部联接缺少值为null

Question

我试图在postgresql数据库上使用full outer join来获取缺失行具有空值的表的联合。但是，它不适合我。

这里是例子：

create temp table nutrient_names (
    name text 
); 

insert into nutrient_names values 
('fat'), 
('sugar'), 
('sodium'), 
('total fat'); 

create temp table nutrients (
    food_id int, 
    name text, 
    quantity float8 
); 

insert into nutrients values 
(1, 'fat', 0.3), 
(1, 'sugar', 15), 
(1, 'sodium', 10), 
(1, 'total fat', 25), 
(2, 'sugar', 10), 
(2, 'sodium', 4); 

这里是输出：

select n.name, n.food_id, n.quantity from nutrient_names nn 
full outer join nutrients n 
on nn.name = n.name 
order by n.food_id, n.name; 

+---------------------------------+ 
|name   |food_id |quantity| 
+---------------------------------+ 
| 'fat'  |1  |'0.3' | 
| 'sodium' |1  |'10' | 
| 'sugar' |1  |'15' | 
| 'total fat'|1  |'25' | 
| 'sodium' |2  |'4'  | 
| 'sugar' |2  |'10' | 
+---------------------------------+ 

我想要什么：

+---------------------------------+ 
|name   |food_id |quantity| 
+---------------------------------+ 
| 'fat'  |1  |'0.3' | 
| 'sodium' |1  |'10' | 
| 'sugar' |1  |'15' | 
| 'total fat'|1  |'25' | 
| 'fat'  |2  |null | <---- 
| 'sodium' |2  |'4'  | 
| 'sugar' |2  |'10' | 
| 'total fat'|2  |null | <---- 
+---------------------------------+ 

Answer 1

它看起来不像FULL JOIN适合在这里。根据您的示例，您希望列出来自nutrient_names的所有行的次数为您有食物ID的次数。这通常通过CROSS JOIN完成。

如果您还没有与食品ID列表中一个单独的表，你可以建立它在飞行中，然后加入到它：

WITH 
CTE_IDs 
AS 
(
    SELECT DISTINCT 
     food_id 
    FROM nutrients 
) 
SELECT 
    nutrient_names.name 
    ,CTE_IDs.food_id 
    ,nutrients.quantity 
FROM 
    CTE_IDs 
    CROSS JOIN nutrient_names 
    LEFT JOIN nutrients 
     ON nutrients.name = nutrient_names.name 
     AND nutrients.food_id = CTE_IDs.food_id 
; 

Answer 2

我建议具有表示营养种类和类型的两个表餐饮。然后有一张额外的表格来说明营养价值。

您可以对食物和营养做一个交叉连接，然后做一个营养价值的左连接，以获得您的结果。

我做了一个小提琴为你在这里看http://sqlfiddle.com/#!17/d974b/3

，你不能做一个完整的外部原因联接是，如果你有一个排的数量，你有ID以及。他们在相同的数据集中。