Informix 查询异常
我正在对 Informix 数据库运行 2 个查询。查询命中 4 个具有内连接和外连接的不同表。我认为它们应该返回相同的结果集，但我得到了 3 条记录的差异。这两个查询如下所示：
查询 1（返回 65 行）-
select ...
from table1, table2, outer table3, outer table4
where table1.id = table2.id
and table1.id = table3.id
and table1.id = table4.id
and .....
查询 2（返回 62 行）-
select ....
from table1 inner join table2 on table1.id = table2.id
left outer join table3 on table1.id = table3.id
left outer join table4 on table1.id = table4.id
where .....
有谁知道为什么这两个查询返回不同的结果集？我假设通过更改为使用内连接/左外连接而不是仅使用外连接（并且内连接没有任何内容）将返回相同的结果。
I am running 2 queries against an Informix database. The queries are hitting 4 different tables with both inner and outer joins. I thought they should both return the same result set, but I am getting a 3 record difference. The two queries are shown below:
query 1 (returns 65 rows) -
select ...
from table1, table2, outer table3, outer table4
where table1.id = table2.id
and table1.id = table3.id
and table1.id = table4.id
and .....
query 2 (returns 62 rows) -
select ....
from table1 inner join table2 on table1.id = table2.id
left outer join table3 on table1.id = table3.id
left outer join table4 on table1.id = table4.id
where .....
Does anyone have any idea why these 2 return different result sets? I assumed that by changing to use inner join / left outer join instead of just using outer (and nothing for the inner join) would return the same results.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
WHERE 子句的内容可能是其中的一个因素。第一个表示法，即 Informix 风格的 OUTER 连接表示法，是非标准的，并且具有相当特殊的语义（这是礼貌的）。第二种表示法是 ISO 标准 OUTER JOIN，它具有规定的语义。
使用 Informix 样式联接，内联接表对中仅在其列上的 WHERE 子句中的任何过滤器中幸存下来的所有行都将在结果集中返回。如果外连接表中的列上或内表和外表之间的 WHERE 子句中有过滤器，那么这些过滤器可能会从结果集中消除一些可能的行，但结果的内连接部分将仍然显示为空扩展。解释起来极其复杂。它或多或少是有道理的，但是结果不仅仅是基本连接集的限制的投影。
您能否确认 WHERE 子句包含 table3 和 table4 中列的某些条件？
没有一种简单的方法可以使用 ISO 标准 OUTER JOIN 表示法来模拟 Informix 样式的 OUTER 连接。
The contents of the WHERE clauses are probably a factor in this. The first notation, the Informix-style OUTER join notation, is non-standard, and has rather peculiar semantics (that's being polite). The second notation is the ISO standard OUTER JOIN which has prescribed semantics.
With the Informix-style join, all the rows from the inner-joined pair of tables that survive any filters in the WHERE clause that are on their columns only will be returned in the result set. If there are filters in the WHERE clause on columns in the outer-joined tables, or between the inner tables and the outer tables, then those filters may eliminate some possible rows from the result set, but the innner-joined part of the result will still appear extended with nulls. It is excruciatingly complex to explain. It sort of more or less makes sense, but the result isn't simply a projection of a restriction of the basic set of joins.
Can you confirm that the WHERE clauses contain some conditions on columns in table3 and table4?
There isn't a trivial way to simulate the Informix-style OUTER join with ISO standard OUTER JOIN notation.