Oracle 减去查询。如果顶部 SQL 和底部 SQL 不包含 NULL，我如何获得带有 NULL 的结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2959130/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Minus query. How can I get a result with NULLS if the top SQL and the bottom SQL don't contain NULLs?
提问by Chad
How could this SQL...
这个 SQL 怎么会...
CREATE TABLE NewTable AS
SELECT A,B,C FROM Table1
minus
SELECT A, B, C From Table2
...create a new table with NULL values in column A when neither Table1 or Table2 had NULL values for in column A?
...当 Table1 或 Table2 在 A 列中都没有 NULL 值时，在 A 列中创建一个具有 NULL 值的新表？
But on the other hand, this SQL...
但另一方面，这个 SQL ...
SELECT * FROM
(
SELECT A,B,C FROM Table1
minus
SELECT A, B, C From Table2
)
WHERE A IS NULL
return no rows!
不返回任何行！
It seems inconsistent!
好像不一致！
I think it is a bug in Oracle.
我认为这是 Oracle 中的一个错误。
Of course the real SQL is much more complex but I believe this accurately illustrates the nature of the problem.
当然，真正的 SQL 复杂得多，但我相信这准确地说明了问题的本质。
UPDATE
更新
Here's the ACTUAL SQL:
这是实际的 SQL：
I executed this statement:
我执行了这个语句：
CREATE TABLE MyMinus
AS
select
*
FROM
---begin main query
(
SELECT expenditure_item_date, expenditure_org, expenditure_type,
f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
f_company_code, f_cost_center_num, f_cuic, f_currency_code,
f_destination_type_code, f_distribution_id, f_distribution_num,
f_exchange_rate, f_extract_date, f_gl_account,
f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
f_project, f_project_num, f_promised_date, f_quantity_billed,
f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
FROM dw_mgr.po_distributions_curr_fct a
WHERE EXISTS (
SELECT 1
FROM dw_mgr.po_distributions_curr_fct b,
dw_mgr.po_lines_curr_fct,
dw_mgr.po_header_curr_fct
WHERE a.ROWID = b.ROWID
AND b.f_cuic = dw_mgr.po_lines_curr_fct.f_cuic
AND b.f_line_id = dw_mgr.po_lines_curr_fct.f_line_id
AND dw_mgr.po_lines_curr_fct.f_cuic =
dw_mgr.po_header_curr_fct.f_cuic
AND dw_mgr.po_lines_curr_fct.f_header_id =
dw_mgr.po_header_curr_fct.f_header_id
AND dw_mgr.po_header_curr_fct.f_header_creation_date <
ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),
-48)
AND dw_mgr.po_header_curr_fct.f_po_status IN
('CLOSED', 'FINALLY CLOSED'))
MINUS
SELECT expenditure_item_date, expenditure_org, expenditure_type,
f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
f_company_code, f_cost_center_num, f_cuic, f_currency_code,
f_destination_type_code, f_distribution_id, f_distribution_num,
f_exchange_rate, f_extract_date, f_gl_account,
f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
f_project, f_project_num, f_promised_date, f_quantity_billed,
f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
FROM arch_fct.po_distributions_curr_fct a
WHERE EXISTS (
SELECT 1
FROM arch_fct.po_distributions_curr_fct b,
arch_fct.po_lines_curr_fct,
arch_fct.po_header_curr_fct
WHERE a.ROWID = b.ROWID
AND b.f_cuic = arch_fct.po_lines_curr_fct.f_cuic
AND b.f_line_id = arch_fct.po_lines_curr_fct.f_line_id
AND arch_fct.po_lines_curr_fct.f_cuic =
arch_fct.po_header_curr_fct.f_cuic
AND arch_fct.po_lines_curr_fct.f_header_id =
arch_fct.po_header_curr_fct.f_header_id
AND arch_fct.po_header_curr_fct.f_header_creation_date <
ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),
-48)
AND arch_fct.po_header_curr_fct.f_po_status IN
('CLOSED', 'FINALLY CLOSED'))
)
And then this. Note that rows with NULL values of F_DISTRIBUTION_ID were inserted into the created table.
然后这个。请注意，将 F_DISTRIBUTION_ID 值为 NULL 的行插入到创建的表中。
SELECT COUNT(*) from MyMinus WHERE F_DISTRIBUTION_ID IS NULL
--17 rows
--17 行
Yet when I execute this:
然而，当我执行这个：
select
*
FROM
---begin main query
(
SELECT expenditure_item_date, expenditure_org, expenditure_type,
f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
f_company_code, f_cost_center_num, f_cuic, f_currency_code,
f_destination_type_code, f_distribution_id, f_distribution_num,
f_exchange_rate, f_extract_date, f_gl_account,
f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
f_project, f_project_num, f_promised_date, f_quantity_billed,
f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
FROM dw_mgr.po_distributions_curr_fct a
WHERE EXISTS (
SELECT 1
FROM dw_mgr.po_distributions_curr_fct b,
dw_mgr.po_lines_curr_fct,
dw_mgr.po_header_curr_fct
WHERE a.ROWID = b.ROWID
AND b.f_cuic = dw_mgr.po_lines_curr_fct.f_cuic
AND b.f_line_id = dw_mgr.po_lines_curr_fct.f_line_id
AND dw_mgr.po_lines_curr_fct.f_cuic =
dw_mgr.po_header_curr_fct.f_cuic
AND dw_mgr.po_lines_curr_fct.f_header_id =
dw_mgr.po_header_curr_fct.f_header_id
AND dw_mgr.po_header_curr_fct.f_header_creation_date <
ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),
-48)
AND dw_mgr.po_header_curr_fct.f_po_status IN
('CLOSED', 'FINALLY CLOSED'))
MINUS
SELECT expenditure_item_date, expenditure_org, expenditure_type,
f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
f_company_code, f_cost_center_num, f_cuic, f_currency_code,
f_destination_type_code, f_distribution_id, f_distribution_num,
f_exchange_rate, f_extract_date, f_gl_account,
f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
f_project, f_project_num, f_promised_date, f_quantity_billed,
f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
FROM arch_fct.po_distributions_curr_fct a
WHERE EXISTS (
SELECT 1
FROM arch_fct.po_distributions_curr_fct b,
arch_fct.po_lines_curr_fct,
arch_fct.po_header_curr_fct
WHERE a.ROWID = b.ROWID
AND b.f_cuic = arch_fct.po_lines_curr_fct.f_cuic
AND b.f_line_id = arch_fct.po_lines_curr_fct.f_line_id
AND arch_fct.po_lines_curr_fct.f_cuic =
arch_fct.po_header_curr_fct.f_cuic
AND arch_fct.po_lines_curr_fct.f_header_id =
arch_fct.po_header_curr_fct.f_header_id
AND arch_fct.po_header_curr_fct.f_header_creation_date <
ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),
-48)
AND arch_fct.po_header_curr_fct.f_po_status IN
('CLOSED', 'FINALLY CLOSED'))
)
WHERE
f_distribution_id is null
I get 0 rows.
我得到 0 行。
Why does insert the records into a temp table appear to introduce rows with NULL DIST IDs?
为什么将记录插入临时表似乎会引入带有 NULL DIST ID 的行？
This minus query SQL, which was generated dynamically by a custom data archival program, attempts to verify that the data which SHOULD be archived in the DW_MGR schema was in fact copied to the ARCH_FCT (archive) schema. It is returning differences which included 17 records where the F_DISTRIBUTION_ID in the MyMinus temp table do not match those in the source DW_MG.PO_DISTRIBUTIONS_CURR_FCT table because they are are NULL. Hence, the archive process is design when differences are found. The question is why are there differences, i.e., how did NULL values get into the MyMinus table when they are not in the SOURCE PO_DISTRIBUTIONS_CURR_FCT table?
这个减去查询 SQL 是由自定义数据归档程序动态生成的，它试图验证应该归档在 DW_MGR 模式中的数据实际上已复制到 ARCH_FCT（归档）模式。它返回的差异包括 17 条记录，其中 MyMinus 临时表中的 F_DISTRIBUTION_ID 与源 DW_MG.PO_DISTRIBUTIONS_CURR_FCT 表中的记录不匹配，因为它们是 NULL。因此，归档过程是在发现差异时设计的。问题是为什么会存在差异，即当 NULL 值不在 SOURCE PO_DISTRIBUTIONS_CURR_FCT 表中时，它们是如何进入 MyMinus 表的？
EDIT:
编辑：
Can someone with Oracle META access please post info on thd following Oracle bugs. I was referred to them but I contract located someone in my co who can tell me what our support ID # is. I will find out eventually, but it would be nice to know sooner. If you would rather not post it, consider the following bug references as potentially related info on my question:
有 Oracle META 访问权限的人可以在 thd 上发布关于 Oracle 错误的信息。我被转介给他们，但我与公司中的某个人签订了合同，他可以告诉我我们的支持 ID # 是什么。我最终会知道的，但如果早点知道就好了。如果您不想发布它，请考虑将以下错误参考作为我的问题的潜在相关信息：
Bug 8209309: MINUS IS SHOWING DIFFERENCES WITH CTAS + INSERT
Bug 7834950: WRONG RESULTS WITH MINUS OPERATOR
采纳答案by Chad
Quit breaking your chops. It's an Oracle bug. I'll prove it to ya:
别折腾了。这是一个 Oracle 错误。我会证明给你看：
First of all, it has to be the first SQL that is returning NULLS for DISTRIBUTION ID, so isolate that SQL and let's call it "SQL1."
首先，它必须是第一个为 DISTRIBUTION ID 返回 NULL 的 SQL，因此隔离该 SQL，我们称其为“SQL1”。
OK, Let's simplify SQL1 for discussion sake and say that it is of this format:
OK，为了讨论，我们简化一下SQL1，说它是这样的格式：
CREATE TABLE TempTable AS
SELECT
F_DISTRIBUTION_ID,
FIELD2,
FIELD3,...FIELD99
FROM WHATEVER
WHERE WHATEVER
Then, you are finding that when you execute this, you are finding rows that have a NULL DIST ID:
然后，您会发现当您执行此操作时，您会发现具有 NULL DIST ID 的行：
SELECT COUNT(*) FROM TempTable WHERE F_DISTRIBUTION_ID IS NULL
--Some positive number of rows returned.
If Oracle wasn't a piece of crap, you could change the number of selected fields so that only F_DISTRIBUTION_ID was selected and you would get the same result when you counted the number of rows with a NULL value of F_DISTRIBUTION_ID, right? Right! But that ain't the case, 'cause Oracle is an unreliable dinosaur.
如果 Oracle 不是废话，您可以更改所选字段的数量，以便仅选择 F_DISTRIBUTION_ID，并且当您计算 F_DISTRIBUTION_ID 为 NULL 值的行数时，您将获得相同的结果，对吧？对！但事实并非如此，因为甲骨文是一种不可靠的恐龙。
Try this:
尝试这个：
CREATE TABLE TempTable AS
SELECT
F_DISTRIBUTION_ID
FROM WHATEVER
WHERE WHATEVER
SELECT COUNT(*) FROM TempTable WHERE F_DISTRIBUTION_ID IS NULL
I betcha dollars to donuts that you get 0 rows returned.
我敢打赌你得到 0 行返回的甜甜圈。
Now, go call up Microsoft and tell them you want to upgrade to SQL Server 2008 R2.
现在，请致电 Microsoft 并告诉他们您要升级到 SQL Server 2008 R2。
回答by Gary Myers
Firstly, I'd get rid of the ROWID to ROWID join. Then I'd get make the table aiases unique (not reusing 'a' and 'b' in the query above the MINUS and the query below the MINUS).
首先，我会摆脱 ROWID 到 ROWID 的加入。然后我会让表 aiases 唯一（不在 MINUS 上方的查询和 MINUS 下方的查询中重用“a”和“b”）。
Finally, I'd look at those 17 rows and try to find the matching records in "dw_mgr.po_distributions_curr_fct" and see, using DUMP(F_DISTRIBUTION_ID) where there is anything odd about the column values.
最后，我会查看这 17 行并尝试在“dw_mgr.po_distributions_curr_fct”中找到匹配的记录，然后使用 DUMP(F_DISTRIBUTION_ID) 来查看列值有什么奇怪的地方。
回答by Mark Bowytz
The only way I can think that F_DISTRIBUTION_ID could be NULL when inserted into MyMinus would be if it's returning NULL somehow, someway in the first query.
我认为 F_DISTRIBUTION_ID 在插入 MyMinus 时可能为 NULL 的唯一方法是，如果它在第一个查询中以某种方式返回 NULL。
To reproduce this (on both 9i and 10g):
要重现这一点（在 9i 和 10g 上）：
SQL> INSERT INTO table1 VALUES (NULL, 2, 3);
1 row created.
SQL> INSERT INTO table2 VALUES (1, 2, 3);
1 row created.
SQL> SELECT *
2 FROM (
3 SELECT a, b, c FROM table1
4 MINUS
5 SELECT a, b, c FROM table2);
A B C
---------- ---------- ----------
2 3
However, with regards to the query returning no rows when run by itself...that's something else. A bug wouldn't surprise me...but have you tried taking out those EXISTS? Of course, there's many different approaches, but perhaps all those sub-queries are causing something funny to happen in memory.
但是，关于查询在单独运行时不返回任何行......那是另一回事。一个错误不会让我感到惊讶......但是你有没有尝试过删除那些存在？当然，有许多不同的方法，但也许所有这些子查询都会导致内存中发生一些有趣的事情。
For example:
例如：
SELECT expenditure_item_date, expenditure_org, expenditure_type,
f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
f_company_code, f_cost_center_num, f_cuic, f_currency_code,
f_destination_type_code, f_distribution_id, f_distribution_num,
f_exchange_rate, f_extract_date, f_gl_account,
f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
f_project, f_project_num, f_promised_date, f_quantity_billed,
f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
FROM dw_mgr.po_distributions_curr_fct a
dw_mgr.po_lines_curr_fct,
dw_mgr.po_header_curr_fct
WHERE a.f_cuic = dw_mgr.po_lines_curr_fct.f_cuic
AND a.f_line_id = dw_mgr.po_lines_curr_fct.f_line_id
AND dw_mgr.po_lines_curr_fct.f_cuic = dw_mgr.po_header_curr_fct.f_cuic
AND dw_mgr.po_lines_curr_fct.f_header_id = dw_mgr.po_header_curr_fct.f_header_id
AND dw_mgr.po_header_curr_fct.f_header_creation_date < ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), -48)
AND dw_mgr.po_header_curr_fct.f_po_status IN ('CLOSED', 'FINALLY CLOSED')
MINUS
SELECT expenditure_item_date, expenditure_org, expenditure_type,
f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
f_company_code, f_cost_center_num, f_cuic, f_currency_code,
f_destination_type_code, f_distribution_id, f_distribution_num,
f_exchange_rate, f_extract_date, f_gl_account,
f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
f_project, f_project_num, f_promised_date, f_quantity_billed,
f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
FROM arch_fct.po_distributions_curr_fct a,
arch_fct.po_lines_curr_fct,
arch_fct.po_header_curr_fct
WHERE a.f_cuic = arch_fct.po_lines_curr_fct.f_cuic
AND a.f_line_id = arch_fct.po_lines_curr_fct.f_line_id
AND arch_fct.po_lines_curr_fct.f_cuic = arch_fct.po_header_curr_fct.f_cuic
AND arch_fct.po_lines_curr_fct.f_header_id = arch_fct.po_header_curr_fct.f_header_id
AND arch_fct.po_header_curr_fct.f_header_creation_date < ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), -48)
AND arch_fct.po_header_curr_fct.f_po_status IN ('CLOSED', 'FINALLY CLOSED')
回答by Gary Myers
It generally shouldn't.
一般不应该。
The only time it might is if you've some advanced security features (fine grained access control) whereby the optimizer can see that A cannot be null in table1/table2 so returns zero rows, but the FGAC kicks in to stop you seeing the actual values in the column by returning null.
唯一可能是如果您有一些高级安全功能（细粒度访问控制），优化器可以看到 A 不能在 table1/table2 中为空，因此返回零行，但 FGAC 开始阻止您看到实际列中的值通过返回空值。
EDIT. "With [Virtual Private Database] column-masking behavior, all rows display, even those that reference sensitive columns. However, the sensitive columns display as NULL values. "
编辑。“使用 [Virtual Private Database] 列屏蔽行为，所有行都会显示，即使是那些引用敏感列的行。但是，敏感列显示为 NULL 值。”
http://download.oracle.com/docs/cd/E11882_01/network.112/e10574/vpd.htm#i1014682
http://download.oracle.com/docs/cd/E11882_01/network.112/e10574/vpd.htm#i1014682
回答by Mark
I'm having a possibly related issue which started out as an issue with MINUS when using a bunch of complex views and exists. I narrowed it down to a probable optimiser issue - you can work around it by stopping the optimiser from messing around with things based on "F_DISTRIBUTION_ID IS NULL" by using something like "upper(F_DISTRIBUTION_ID) IS NULL".
我有一个可能相关的问题，当使用一堆复杂的视图并存在时，它开始是 MINUS 的问题。我把它缩小到一个可能的优化器问题——你可以通过使用“upper(F_DISTRIBUTION_ID) IS NULL”之类的东西来阻止优化器乱搞基于“F_DISTRIBUTION_ID IS NULL”的东西来解决它。
Its next to impossible to create a simplified test case for a bug report in these cases - it likely only occurs in very specific scenarios (its not like MINUS is going to be completely broken after all). With my issue I couldnt reduce the query at all and have it still occur.
在这些情况下，几乎不可能为错误报告创建一个简化的测试用例——它可能只发生在非常特定的场景中（毕竟它不像 MINUS 会被完全破坏）。对于我的问题，我根本无法减少查询并且它仍然发生。
FYI my issue was a query which basically joined a bunch of stuff onto a source table, call it Employee. I had a where clause on the primary key of Employee - if I did a where EmployeeId = foo, it would return an extra row with nulls where they shouldn't be (columns from a table which was inner joined on) - if I did a where upper(EmployeeId) = foo then I would get the correct result. Obviously the EmployeeId value was sourced from the same cell in all rows matching the predicate - so it was clearly a bug.
仅供参考，我的问题是一个查询，它基本上将一堆东西加入了一个源表，称之为 Employee。我在 Employee 的主键上有一个 where 子句 - 如果我做了一个 where EmployeeId = foo，它会返回一个额外的行，其中不应该是空值（来自内部连接的表中的列） - 如果我做了a where upper(EmployeeId) = foo 那么我会得到正确的结果。显然，EmployeeId 值来自与谓词匹配的所有行中的同一单元格 - 所以这显然是一个错误。
回答by Aeroll
I Just encountered same error. and build an if else condition to get those values :D
我刚刚遇到了同样的错误。并建立一个 if else 条件来获取这些值：D
table 1
表格1
A ! B
null 35
‘if else' condition :P
“如果其他”条件：P
select
Case
WHEN a."Add" >= '0' THEN to_number(a."Add" - b."Cease" )
ELSE (b."Cease")*-1
End as "X"
from table 1
Answer
回答
-35
-35
