SQL不返回正确的值

Question

因此，我有一个简单的SQL查询，但是它似乎被窃听了(或者我的where-clause写错了)，因为如果我在一个特定字段(matflag)上选择一个特定值(50)，它不会返回一个值。

该查询基本上是一个select from table1，在table2上有一个子查询，其中where-clause只检查来自子查询的返回字段是否存在于table1中。

Select distinct  
    t1.matnum as matnum, t1.matflag as matflag, t1.factory as factory 
from 
    table1 t1, 
    (select matnum from table2 where technical_value = 'XX') t2 
where 
    t1.matnum = t2.matnum and t1.matnum = '60000000';

这将返回以下输出：

+----------+---------+---------+
| MATNUM   | MATFLAG | FACTORY |
+----------+---------+---------+
| 60000000 |         | 001000  |
| 60000000 |         | 002000  |
| 60000000 |         | 003000  |
| 60000000 |         | 004000  |
| 60000000 |         | 005000  |
+----------+---------+---------+

但是，如果我将and t1.matflag != '50'添加到where-clause的末尾，则整个输出将消失。

Select distinct 
    t1.matnum as matnum, t1.matflag as matflag, t1.factory as factory 
from 
    table1 t1, 
    (select matnum from table2 where technical_value = 'XX') t2 
where 
    t1.matnum = t2.matnum 
    and t1.matnum = '60000000' 
    and t1.matflag != '50';

输出：

+----------+---------+---------+
| MATNUM   | MATFLAG | FACTORY |
+----------+---------+---------+

列matflag的附加信息:它是一个varchar2(2 Char)列，它要么没有填充，要么填充值'50‘或值'10’或'20‘。现在，如果我将where子句从and t1.matflag != '50'更改为and t1.matflag is null，则输出再次正确：

Select distinct 
    t1.matnum as matnum, t1.matflag as matflag, t1.factory as factory 
from 
    table1 t1, 
    (select matnum from table2 where technical_value = 'XX') t2 
where 
    t1.matnum = t2.matnum 
    and t1.matnum = '60000000' 
    and t1.matflag is null;

因此，这将返回以下输出：

+----------+---------+---------+
| MATNUM   | MATFLAG | FACTORY |
+----------+---------+---------+
| 60000000 |         | 001000  |
+----------+---------+---------+
.... and so on, have a look at the first table above

那么，如果我在is null上选择，而在!= '50'上选择时，它如何返回？(Sidenote:将!=改为<>也于事无补)

matflag is null是如何应用的，但matflag != '50'不是吗？

我们运行Oracle数据库11g版本11.2.0.3.0-64位的生产。

Answer 1

了解如何使用正确的显式JOIN语法：

Select distinct t1.matnum as matnum, t1.matflag as matflag, t1.factory as factory 
from table1 t1 join
     table2
     on t1.matnum = t2.matnum
where t2.technical_value = 'XX' and t1.matnum = '60000000';

然后了解NULL值以及它们如何失败几乎所有的比较，包括<>。

你想要的逻辑是：

where t2.technical_value = 'XX' and t1.matnum = '60000000' and
      (matflag <> '50' or matflag is null)

Answer 2

在SQL NULL中，“未知”值的意思是“未知”值，因此使用它的任何操作都将导致NULL。试试COALESCE(t1.matflag, 0) <> 50..。