如何使这个子查询不检查null?

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

How to make this subquery not check for null?

问题

我需要一个返回唯一人员的脚本。

"CPF"标识是唯一的值,但这些数据并不总是被记录。所以我希望这个子查询不要比较空值。稍后我会通过姓名进行验证。

我已经有这个查询,但我希望它不比较空值。

SELECT*
	FROM TAB.ORIGEM
	WHERE NOT EXISTS
	(
	SELECT 1 FROM TAB.DESTINO
	WHERE TAB.DESTINO.CPF = TAB.ORIGEM.CPF
	)

下面是表格和我期望的结果的示例。

       TAB.ORIGEM   
  
| NAME     |   CPF    |
| -------- | -------- |
| JOAO     | 123      |
| MARIA    | 321      |
| PAULO    |          |

     TAB.DESTINO

| NAME     |   CPF    |
| -------- | -------- |
| JOAO     | 123      |
| JOANA    | 456      |
| JOSE     |          |

        RESULT

| NAME     |   CPF    |
| -------- | -------- |
| MARIA    | 321      |
| PAULO    |          |
英文:

I need a script that returns me unique people.

"CPF" identifiers are unique values, but these data are not always registered. so I would like this subquery not to compare null values. And later I will do the verification by name.

I already have this query, but I need it not to compare null values.

SELECT*
FROM TAB.ORIGEM
WHERE NOT EXISTS
(
SELECT 1 FROM TAB.DESTINO
WHERE TAB.DESTINO.CPF = TAB.ORIGEM.CPF
)

Below is an example of the tables and the result I expect.

   TAB.ORIGEM   
NAME CPF
JOAO 123
MARIA 321
PAULO
 TAB.DESTINO
NAME CPF
JOAO 123
JOANA 456
JOSE
    RESULT
NAME CPF
MARIA 321
PAULO

答案1

得分: 1

以下是已翻译的内容:

不确定我是否正确理解了您的要求,但我认为您需要类似以下的内容:

select d.*
from destino d
where not exists (
  select 1
  from origem o
  where d.cpf=o.cpf
);
英文:

Not sur whether I got your requirements right, yet I think you need something like below:

select d.*
from destino d
where not exists (
  select 1
  from origem o
  where d.cpf=o.cpf
);

答案2

得分: 0

尝试连接。您想要的结果是:

SELECT ORIGEM.* FROM ORIGEM
   LEFT JOIN DESTINO 
   ON DESTINO.CPF = ORIGEM.CPF
   WHERE DESTINO.CPF IS NULL
英文:

Try join. The result you want:

SELECT ORIGEM.* FROM ORIGEM
   LEFT JOIN DESTINO 
   ON DESTINO.CPF = ORIGEM.CPF
   WHERE DESTINO.CPF IS NULL

huangapple
  • 本文由 发表于 2023年3月7日 21:13:16
  • 转载请务必保留本文链接:https://go.coder-hub.com/75662439.html
匿名

发表评论

匿名网友

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

确定