logo标签列表

我可以为左外连接提供一个默认值吗？

postgresqljoin
假设我有表a（具有列a1）和b（具有列b1和b2），然后执行左外连接。
SELECT *
FROM a LEFT OUTER JOIN b
ON a.a1 = b.b1

当a的值没有与b1匹配的值时，b1和b2将为NULL。

我能够提供一个默认值给b2吗？而不是NULL。注意，这里COALESCE不能使用，因为我不希望默认值覆盖b2中可能存在的NULL，只有在b1存在与a1匹配的值时才会使用默认值。

也就是说，对于a和b：

CREATE TABLE a (a1)
  AS VALUES (1),
            (2),
            (3) ;

CREATE TABLE b (b1,b2)
  AS VALUES (1, 10),
            (3, null) ;


a1     b1 | b2
---    --------
 1      1 | 10
 2      3 | NULL
 3

假设b2的默认值为100，我想得到结果。
a1 | b1   | b2
---------------
1  |  1   | 10
2  | NULL | 100
3  |  3   | NULL

在这种简单的情况下，我可以通过查看输出中b1是否为空来手动进行。一般来说，这是最好的选择，还是有更标准和更简洁的方法？
5个回答
SELECT a.a1,b.b1,  
    CASE WHEN b.b1 is NULL THEN 5 ELSE b.b2 END AS b2  
FROM a LEFT OUTER JOIN b  
ON a.a1 = b.b1
我已经添加了一个标签，表示我愿意接受一个针对Postgres的特定答案。不过，如果可能的话，还是更倾向于标准SQL。 - Tom Ellis
@Kin：根据我的问题，我知道可以通过查看输出中的b1是否为空来手动完成。这是一般情况下最好的选择吗？还是有更标准和更简洁的方法？ - Tom Ellis
4由于您想要区分由于连接操作而产生的NULL和“自然”存在的NULL，所以不可避免地需要检查b1。如果您所说的“我可以手动完成”是指这种方式，那么是的，这是唯一的方法。 - Mordechai
@MorDeror：好的，我想可能有一种语法，类似于"LEFT OUTER JOIN ... ON ... DEFAULT b2 = ..."。 - Tom Ellis
你可以将其重写为内连接和反连接的并集，但我不认为有必要让代码变得更复杂。 - ypercubeᵀᴹ
好的，如果这确实是常规的方法，那么谢谢你，我接受！ - Tom Ellis
3没有解释的情况下，我很想给这个回答点个踩。这个回答不符合DBA.SE的质量要求。 - Evan Carroll
答案非常明确地是“不”。这个答案被明确指出为OP寻求替代方案。他们的问题在于当默认值在多个地方使用时会变得冗长和不清晰，使用case/coalesce/ifnull语句会变得麻烦和不清楚。无法指定一个替换“null”的“左连接的默认值”，因此唯一正确的答案是“不”，而这是错误的。 - Chris.Caldwell
@Chris.Caldwell 对我来说，答案很明确是“是”。除了将5作为默认值而不是100之外，输出与初始问题所要求的相匹配。在我的看法中，唯一不是“是”的方式是，“默认”值对于进一步的JOIN条件或WHERE条件不是立即可见的，但这并不是提出的初始问题的一部分——就生成的输出而言，它符合OP的要求，并避免了在b中有一行与a中的行匹配，但目标列中有一个NULL时填充NULL的可能性。 - mpag
我觉得在这种情况下，COALESCE非常有用。它会从列表中返回第一个非NULL值。
SELECT
 a.a1,
 b.b1,
 COALESCE (b.b2, 100) AS b2
FROM a
LEFT OUTER JOIN b
  ON (a.a1 = b.b1);

在@ypercube准确地指出了我的答案与问题不符的问题后，这是他的更正：
SELECT 
  a.a1, 
  b.b1, 
  COALESCE(b.b2, d.b2) AS b2   
FROM a LEFT JOIN b ON a.a1 = b.b1 
LEFT JOIN (SELECT 100 AS b2) AS d ON b.b1 IS NULL;
5到目前为止，这是最好的答案。 - Luke
@ypercubeᵀᴹ 真的吗？哪个部分？重点是要通过第一个查询和被接受的答案来说明COALESCE函数与CASE WHEN语法的对比。 - Rob
是的，我重新阅读了问题，我明白你的观点了。但是就像我上面说的，我只是想更多地说明COALESCE函数而不是给出完整的答案。我会进行修正的。谢谢你指出这一点。 - Rob
没问题，那可以。 - Rob
@ypercubeᵀᴹ 你不必 ;). 富有建设性的评论总是一件好事。这是团队的努力。(即使有时候会有点尴尬哈哈) - Rob
1是的，我同意。虽然你将它们纳入了回答中，但这只是噪音罢了。从一个有20多个评论的问题或回答中获取信息真的很困难。 - ypercubeᵀᴹ
原始回答对这个问题没有解释，所以让我们再试一次。
使用CASE表达式
使用这种方法，我们利用了在不同列中有另一个值不为空的特点，在这种情况下，如果该值为空，则说明连接失败。
SELECT
  a.a1,
  b.b1,  
  CASE WHEN b.b1 is NULL THEN 100 ELSE b.b2 END AS b2  
FROM a
LEFT OUTER JOIN b  
  ON (a.a1 = b.b1);

这个完全可行，并且会生成你想要的东西。
使用子查询
不要使用这种方法，这只是一个构建的想法。继续阅读。
如果我们没有任何非空列可以像那样利用，我们需要找到一种方法来创建一个能够为我们发挥同样作用的列...
SELECT
  a.a1,
  b.b1,  
  CASE WHEN b.cond IS NULL THEN 100 ELSE b.b2 END AS b2  
FROM a
LEFT OUTER JOIN (
  SELECT true AS cond, b.*
  FROM b
) AS b
  ON (a.a1 = b.b1);

使用行比较

比起强制设置一个我们可以进行比较的假值，更简单的方法是比较行。在PostgreSQL中，行有一个名为表的值。例如，SELECT foo FROM foo返回一个类型为foo（即行类型）的行，来自表foo。在这里，我们测试是否该ROW为空。只要每个列IS NOT NULL，这将起作用。如果您的表中每个列IS NULL，那么您只是在捣乱。

SELECT
  a.a1,
  b.b1,  
  CASE WHEN b IS NULL THEN 100 ELSE b.b2 END AS b2  
FROM a
LEFT OUTER JOIN b
  ON (a.a1 = b.b1);
1在CASE解决方案中使用的列b1不需要是非空的。这个构造在任何情况下都有效。 - ypercubeᵀᴹ
作为合并列值的替代方案，有时候我们可能希望在不匹配的行上使用默认行。
根据评论所述，使用基于tsql方言：我已经添加了一个标签来表示我愿意接受Postgres特定的答案。然而，如果可能的话，标准SQL将更受欢迎。

测试数据

SELECT *
INTO #a
FROM (VALUES (1)
           , (2)
           , (3)) AS x(a1);

SELECT *
INTO #b
FROM (VALUES (1, 10)
           , (3, NULL)
     ) AS x(b1, b2);

基本查询

SELECT *
FROM #a JOIN #b ON #a.a1 = #b.b1 -- all matches
UNION
SELECT #a.a1, NULL, 100 -- default row
FROM #a LEFT JOIN #b ON #a.a1 = #b.b1 WHERE b1 IS NULL -- where a1 <> b1;

基本结果

|a1|b1|b2 |
-----------
| 1| 1| 10|
| 2|  |100|
| 3| 3|   |

你可以使用这种方法来更加基于集合的方式处理，将默认值视为两个集合之间差异的替代。

下面是一个更高级的示例，其中偶数/奇数的a id具有不同的默认b行。

高级查询

INSERT INTO #a
SELECT *
FROM (VALUES (5)) AS x(a1);

SELECT *
FROM #a
     JOIN #b ON #a.a1 = #b.b1 -- all matches
UNION
SELECT a1, NULL, val
FROM #a
     LEFT JOIN #b ON #a.a1 = #b.b1
     JOIN (
    SELECT *
    FROM (VALUES (0, 100) -- even default
               , (1, 99) -- odd default
         ) AS x(rem, val)
  ) t ON a1 % 2 = t.rem
WHERE b1 IS NULL -- where a1 <> b1;

高级结果

|a1|b1|b2 |
-----------
| 1| 1| 10|
| 2|  |100|
| 3| 3|   |
| 5|  | 99|
1不错，但“#”在SQL Server中仅用于临时表，而在Postgres中不使用。 - ypercubeᵀᴹ
@ypercubeᵀᴹ 的问题并不是针对特定的Postgres数据库，但我在答案中添加了一条注释，说明答案使用了TSQL。 - CervEd
@JohnK.N. 作者将问题标记为“postgresql”，是因为第一个答案使用了“postgres”方言，但问题本身与PostgreSQL无关。我不想改变临时表的语法，因为这与答案本身无关。 - CervEd
在一个回答的评论中看到：「我已经添加了一个标签，表示我愿意接受一个针对Postgres的特定答案。不过，如果可能的话，标准SQL会更好。」所以我猜社区可能会采纳你的答案。 :-) - John K. N.
1@JohnK.N. 接受的答案是标准的SQL。这个也是（但我不确定在表名中是否允许使用花哨的#符号 ;)）。 - ypercubeᵀᴹ
@ypercubeᵀᴹ 在标准SQL中可能不被允许/定义。 - John K. N.
我欢迎对标准SQL的任何编辑，连接本身是标准SQL，这就是答案。表的定义似乎并不重要。我不得不更改原始的创建表语句，因为它们在SQL Server中无法工作，然后它们变成了T-SQL临时表。 - CervEd
OP要求使用替代性的方法来替换null值，而不是通常的case / coalesce / ifnull方法，通过指定左连接的不同“默认值”来实现。这是一个合理的要求，因为如果字段在查询中被重复使用或者在复杂计算中使用，或者您想区分“连接表中行不存在”的信号和“行存在但字段为空”的结果，那么使用传统方法可能会很麻烦。正确的答案是“不，该功能不存在”。