带有NOT IN子句的子查询出现时，LEFT JOIN是否有可能失败? [英] Is it possible that LEFT JOIN fails while subquery with NOT IN clause suceeds?
问题描述
有一段时间我已经发布了对此问题的答案 PostgreSQL多条件语句.
A while I have posted an answer to this question PostgreSQL multiple criteria statement.
任务非常简单-如果另一个表中没有相应的值，则从一个表中选择值.假设我们有如下表:
Task was quite simple - select values from one table if there is no corresponding value in another table. Assuming we have tables like below:
CREATE TABLE first (foo numeric);
CREATE TABLE second (foo numeric);
我们希望从first.foo获取所有值，而second.foo中不会出现这些值.我提出了两种解决方案:
we would like to get all the values from first.foo which doesn’t occur in the second.foo. I've proposed two solutions:
- 使用
LEFT JOIN
SELECT first.foo
FROM first
LEFT JOIN second
ON first.foo = second.foo
WHERE second.foo IS NULL;
- 组合子查询和
IN运算符: - combining subquery and
INoperator:
SELECT first.foo
FROM first
WHERE first.foo NOT IN (
SELECT second.foo FROM second
);
由于某种原因，第一个在OP的上下文中不起作用(返回0行)，此后一直困扰着我.我尝试使用不同版本的PostgreSQL重现该问题，但到目前为止还没有运气.
For some reason the first wouldn't work (returned 0 rows) in the context of the OP and it has been bugging me since then. I've tried to reproduce that issue using different versions of PostgreSQL but no luck so far.
第一个解决方案失败而第二个解决方案按预期工作时，是否有任何特殊原因?我是否缺少明显的东西?
Is there any particular reason why the first solution would fail and the second worked as expected? Am I missing something obvious?
此处是sqlfiddle，但似乎可以在任何可用平台上使用.
Here is sqlfiddle but it seems to work on any available platform.
修改
就像@bma和@MostyMostacho在评论中指出的那样，它应该是第二个没有返回结果的(
Like @bma and @MostyMostacho pointed out in the comments it should be rather second one that returned no results (sqlfiddle).
推荐答案
select values from one table if there is no corresponding value in another table.您刚刚回答了自己的问题:
select values from one table if there is no corresponding value in another table. You just answered your own question:
SELECT o.value
FROM table_one o
WHERE NOT EXISTS (
SELECT *
FROM table_two t
WHERE t.value = o.value
);
简短演示:
A short demonstration:
CREATE TABLE first (foo numeric);
CREATE TABLE second (foo numeric);
INSERT INTO first VALUES (1);
INSERT INTO first VALUES (2);
INSERT INTO first VALUES (3);
INSERT INTO first VALUES (4);
INSERT INTO first VALUES (5);
INSERT INTO first VALUES (NULL); -- added this for completeness
INSERT INTO second VALUES (1);
INSERT INTO second VALUES (3);
INSERT INTO second VALUES (NULL);
SELECT f.foo AS ffoo, s.foo AS sfoo
-- these expressions all yield boolean values
, (f.foo = s.foo) AS is_equal
, (f.foo IN (SELECT foo FROM second)) AS is_in
, (f.foo NOT IN (SELECT foo FROM second)) AS is_not_in
, (EXISTS (SELECT * FROM second x WHERE x.foo = f.foo)) AS does_exist
, (NOT EXISTS (SELECT * FROM second x WHERE x.foo = f.foo)) AS does_not_exist
, (EXISTS (SELECT * FROM first x LEFT JOIN second y ON x.foo = y.foo
WHERE x.foo = f.foo AND y.foo IS NULL))
AS left_join_is_null
FROM first f
FULL JOIN second s ON (f.foo = s.foo AND (f.foo IS NOT NULL OR s.foo IS NOT NULL) )
;
结果:
Result:
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
ffoo | sfoo | is_equal | is_in | is_not_in | does_exist | does_not_exist | left_join_is_null
------+------+----------+-------+-----------+------------+----------------+-------------------
1 | 1 | t | t | f | t | f | f
2 | | | | | f | t | t
3 | 3 | t | t | f | t | f | f
4 | | | | | f | t | t
5 | | | | | f | t | t
| | | | | f | t | f
| | | | | f | t | f
(7 rows)
如您所见，对于IN()和等于情况，布尔值可以为NULL.
对于EXISTS()情况，不能为NULL.生存还是毁灭.
LEFT JOIN ... WHERE s.foo IS NULL(几乎)等同于NOT EXISTS情况，除了它实际上将second.*包括在查询结果中(在大多数情况下是不需要的)
As you can see, the boolean can be NULL for the IN() and equals cases.
It cannot be NULL for the EXISTS() case. To be or not to be.
The LEFT JOIN ... WHERE s.foo IS NULL is (almost) equivalent to the NOT EXISTS case, except that it actually includes second.* into the query results (which is not needed, in most cases)
这篇关于带有NOT IN子句的子查询出现时，LEFT JOIN是否有可能失败?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
