在 Oracle 子查询中未返回任何行。

huangapple go评论64阅读模式
英文:

Not in oracle subquery returning 0 rows

问题

这返回了0条记录。

SELECT
    p.phone_nbr AS phone_nbr,
    p.caller_id AS caller_id
FROM phonebook p
WHERE p.identification = '123456789'
AND p.phone_nbr NOT IN (SELECT s.phone_nbr
                        FROM status s
                        WHERE p.phone_nbr = s.phone_nbr
                        AND s.blocked = 'Y'
                        AND s.phone_nbr IS NOT NULL);

但是,如果我删除子查询记录或者仅运行子查询,那么会返回记录。另外,如果我将子查询的结果硬编码到NOT IN中,像这样 NOT IN ('9403459384', '800230493'),也会返回结果。

所以我对为什么没有返回任何内容感到困惑。我已经添加了 "is not null" 条件以确保不会选择空的电话号码,但这并没有解决问题。

编辑:是因为两个表之间没有共享的主键约束或唯一约束吗?

英文:

This is returning 0 records.

         SELECT
               p.phone_nbr                             AS phone_nbr,
               p.caller_id                             AS caller_id
          FROM phonebook p
          WHERE     p.identification = '123456789'
          AND p.phone_nbr NOT IN (SELECT s.phone_nbr
                                  FROM status s
                                  WHERE p.phone_nbr = s.phone_nbr
                                  AND s.blocked = 'Y'
                                  AND s.phone_nbr IS NOT NULL);

However, if I remove the subquery records or if I run just the subquery then records are returned. Also if I hardcode the results of the subquery into the NOT IN like NOT IN ('9403459384', '800230493'), results are returned.

So i'm at a loss for why it's not returning anything. I've added the "is not null" to make sure no null phone numbers are getting selected but that didn't fix the issue.

Edit: Is it because there's no shared primary key constraint / unique between the two tables?

答案1

得分: 2

子查询产生了空值,而您的查询没有正确处理它们。SQL标准定义了三值布尔逻辑(真、假、未知),您正在处理第三种情况,却不知道它。

此外,您需要深刻理解关系数据库中的“null”是什么意思。Null意味着值确实存在,但它只是缺失NOT IN运算符在处理此问题时特别棘手。例如,3 NOT IN (1, 2, 4, null)的计算结果是unknown,因为无法确定最后一个元素是否可能是 3;由于WHERE谓词(与约束不同)要求谓词仅计算为true,因此将丢弃这些行。

避免处理这种精细粒度的三值逻辑的最简单方法是改用NOT EXISTS (<子查询>)NOT EXISTS很容易理解。

例如,您的查询可以重写为:

SELECT
  p.phone_nbr AS phone_nbr,
  p.caller_id AS caller_id
FROM phonebook p
WHERE p.identification = '123456789'
  AND NOT EXISTS (
    SELECT 1 
    FROM status s
    WHERE s.phone_nbr = p.phone_nbr
      AND s.blocked = 'Y'
  )
英文:

The subquery produces nulls and your query is not dealing with them correctly. The SQL Standard defines trivalent boolean logic (true, false, unknown) and you are dealing with the third case without knowing it.

Also, you need to understand well what "null" means in relational databases. A null means that the value does exist but, it's just missing. The NOT IN operator is particularly tricky on this. For example 3 NOT IN (1, 2, 4, null) evaluates to unknown since it's not possible to find out if the last element could be 3; since a WHERE predicate (unlike constraints) requires the predicate to evaluate only to true, the rows are discarded.

The easiest way of avoiding to deal with this fine grained trivalent logic, is to use NOT EXISTS (&lt;subquery&gt;) instead; NOT EXISTS is straightforward to understand.

For example, your query could be rewritten as:

SELECT
  p.phone_nbr AS phone_nbr,
  p.caller_id AS caller_id
FROM phonebook p
WHERE p.identification = &#39;123456789&#39;
  AND NOT EXISTS (
    SELECT 1 
    FROM status s
    WHERE s.phone_nbr = p.phone_nbr
      AND s.blocked = &#39;Y&#39;
  )

答案2

得分: 0

你的子查询中的语法有点混乱。在选择子查询时,你使用了别名为"s"的status表,但在Select子句中使用了表名,但在Where子句中没有使用别名。

这可能是问题的原因,但不能确定。如果你将子查询与主查询进行左连接,然后使用Where子句过滤结果以仅包括未匹配的行,可能会避免混淆。

例如(你应该根据你的实际情况进行调整):

Select
    p.phone_nbr     AS phone_nbr,
    p.caller_id     AS caller_id
From 		
    phonebook p
Left Join	
    (   Select  phone_nbr
        From    status 
    ) s ON(s.phone_nbr = p.phone_nbr And s.blocked = 'Y' And s.phone_nbr IS NOT NULL)
Where 
    p.identification = '123456789' And s.phone_nbr Is Null;
英文:

It is hard to tell without the sample data, but you have a bit confusing sintax in your subquery. You are selecting from status table aliased as "s" and you use table name in Select clause but not in the Where clause - there is an alias used.
This might be causing the problem, not for sure, though. You could avoid the confusion if you left join your subquery to the main one and filter the result (using where clause) to just rows not matched.
Something like here (you should adjust it to your actual context):

Select
		p.phone_nbr     AS phone_nbr,
		p.caller_id     AS caller_id
From 		
		phonebook p
Left Join	
		(	Select 	phone_nbr
			From 	status 
		) s ON(s.phone_nbr = p.phone_nbr And s.blocked = &#39;Y&#39; And s.phone_nbr IS NOT NULL)
Where 
		p.identification = &#39;123456789&#39; And s.phone_nbr Is Null;

答案3

得分: 0

以下是翻译好的部分:

"您写的备注不太清楚,因为您写道您正在独立运行子查询,而记录却显示出来了。
在下面的查询中,它将无法编译 - 如果我只运行子查询,那么SQL编译器将会失败,并要求提供 p.phone_nbr。

要回答您的问题 - 查询将在以下重写下工作:
SELECT
p.phone_nbr AS phone_nbr,
p.caller_id AS caller_id
FROM phonebook p
WHERE p.identification = '123456789'
AND p.phone_nbr NOT IN (SELECT s.phone_nbr
FROM status s
WHERE
s.blocked = 'Y'
AND s.phone_nbr IS NOT NULL);"

英文:
The note written is not clear cause you have written that you are running the subquery independently and the records were displayed 
In the below query it will not be compiled - as if i just run the subquery then sql compiler will fail the query asking for p.phone_nbr

AND p.phone_nbr NOT IN (SELECT s.phone_nbr
                                  FROM status s
                                  WHERE p.phone_nbr = s.phone_nbr
                                  AND s.blocked = &#39;Y&#39;
                                  AND s.phone_nbr IS NOT NULL);

To answer your question - the query will work with below rewriting
 SELECT
               p.phone_nbr                             AS phone_nbr,
               p.caller_id                             AS caller_id
          FROM phonebook p
          WHERE     p.identification = &#39;123456789&#39;
          AND p.phone_nbr NOT IN (SELECT s.phone_nbr
                                  FROM status s
                                  WHERE 
                                  s.blocked = &#39;Y&#39;
                                  AND s.phone_nbr IS NOT NULL);

huangapple
  • 本文由 发表于 2023年6月29日 10:07:31
  • 转载请务必保留本文链接:https://go.coder-hub.com/76577664.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定