在SQL中，'distinct'将结果行的数量从1减少到零(In SQL, 'distinct' reduces the number of result ro

在SQL中，'distinct'将结果行的数量从1减少到零(In SQL, 'distinct' reduces the number of result rows from one to zero)

我有一个以下结构的SQL语句：

select distinct ... from table1, (select from table2, table3, table4 where ...) where ... order by ...

对于where子句中的某些值，该语句在结果集中返回零行。 当我删除'distinct'关键字时，它会返回一行。 我希望在这两种情况下都能看到一个结果行。 是否有一些'distinct'关键字的属性我不知道并导致这种行为？

该数据库是Oracle 11g。

I have a SQL statement of the following structure:

select distinct ... from table1, (select from table2, table3, table4 where ...) where ... order by ...

With certain values in the where clauses, the statement returns zero rows in the result set. When I remove the 'distinct' keyword, it returns a single row. I would expect to see a single result row in both cases. Is there some property of the 'distinct' keyword that I am not aware of and that causes this behavior?

The database is Oracle 11g.

最满意答案

您描述的不是DISTINCT的预期行为。 这是：

SQL> select * from dual 2 / D - X 1 row selected. SQL> select distinct * from dual 2 / D - X 1 row selected. SQL>

所以，如果你说的话真的发生了，那么这就是一个错误。 但是，您也说这种情况很少见，这意味着您的数据和/或环境中的瞬态条件很有可能存在一些特性，而不是错误。

您需要创建可重现的测试用例，原因有两个。 没有人，没有人能够调查你的问题。 但主要是因为构建测试用例本身就是一项调查：尝试隔离数据和/或环境因素的精确组合通常会产生洞察力，从而产生解决方案。

It turned out that one of the sub-selects resulted in a data set that contained, among others, a row where every column was NULL. It seems that this row influenced the evaluation of the DISTINCT in a non-obvious way (at least to me). Maybe this is due to some under-the-hood SQL optimizations. After I removed the cause of this NULL-filled row, the problem is gone and the statement evaluates to one row in the result as it should.