主页 / dba / 问题 / 311321
Accepted
AndreKR
Asked: 2022-04-26 18:12:06 +0800 CST

我可以写一个没有 OR IS NULL 的 FULL OUTER JOIN 吗？

  • 772

这里有一些数据可以玩：

CREATE TABLE a (
    a_id   int  NOT NULL,
    a_prop text NOT NULL
);

CREATE TABLE b (
    b_id   int  NOT NULL,
    b_prop text NOT NULL
);

INSERT INTO a VALUES (1, 'blah'), (2, 'blah'), (4, 'not this one');
INSERT INTO b VALUES (1, 'blah'), (3, 'blah'), (5, 'not this one');

现在我想写一个返回的查询：

结果

一种可能性是：

SELECT *
FROM a
FULL OUTER JOIN b ON a_id = b_id
WHERE (a_prop = 'blah' OR a_prop IS NULL)
AND (b_prop = 'blah' OR b_prop IS NULL);

这要求我为OR ... IS NULL我有条件的每个领域都写。如果某些条件是日期范围等，这将变得更加冗长。

如果这是左连接：

SELECT *
FROM a
LEFT JOIN b ON a_id = b_id
WHERE a_prop = 'blah'
AND (b_prop = 'blah' OR b_prop IS NULL);

我可以将条件移至ON子句以避免这种情况：

SELECT *
FROM a
LEFT JOIN b ON a_id = b_id AND b_prop = 'blah'
WHERE a_prop = 'blah';

有没有办法用完整的外部连接来做到这一点？

小提琴

postgresql join

2 个回答

  1. Best Answer

    没有OR IS NULL？

    col = 'x' OR col IS NULL

    原来的：

    SELECT *
FROM   a
FULL   JOIN b ON a_id = b_id
WHERE (a_prop = 'blah' OR a_prop IS NULL)
AND   (b_prop = 'blah' OR b_prop IS NULL);

    使用col <> x IS NOT TRUE：

    SELECT *
FROM   a
FULL   JOIN b ON a_id = b_id
WHERE  a_prop <> 'blah' IS NOT TRUE
AND    b_prop <> 'blah' IS NOT TRUE;

    或加入前过滤：

    SELECT *
FROM      (SELECT * FROM a WHERE a_prop = 'blah') a
FULL JOIN (SELECT * FROM b WHERE b_prop = 'blah') b ON a_id = b_id;

    col <> 'x' OR col IS NULL

    问题的第一个版本要求这个谓词。

    原来的：

    SELECT *
FROM a
FULL OUTER JOIN b ON a_id = b_id
WHERE (a_prop <> 'not this one' OR a_prop IS NULL)
AND   (b_prop <> 'not this one' OR b_prop IS NULL);

    使用col IS DISTINCT FROM 'x'：

    SELECT *
FROM   a
FULL   JOIN b ON a_id = b_id
WHERE  a_prop IS DISTINCT FROM 'not this one'
AND    b_prop IS DISTINCT FROM 'not this one';

    或加入前过滤：

    SELECT *
FROM      (SELECT * FROM a WHERE a_prop <> 'not this one') a
FULL JOIN (SELECT * FROM b WHERE b_prop <> 'not this one') b ON a_id = b_id;

    db<>fiddle here - 显示所有

    旁白：而不是!=我使用<>，这是 SQL 中的标准运算符。（!=是 Postgres 中公认的别名。）

    • 5

  2. 另外的选择：

    SELECT *
FROM a 
FULL JOIN b
   ON a.a_id = b.b_id
WHERE EXISTS
(
    SELECT 'blah'
    INTERSECT
    VALUES (a.a_prop), (b.b_prop)
);
    援助 a_prop 出价 b_prop
    1 废话 1 废话
    2 废话 无效的 无效的
    无效的 无效的 3 废话

    db<>小提琴

    • 3

相关问题