Oracle-多个表上的左外部联接未返回所需的空值 [英] Oracle- Left outer join on multiple tables not returning desired null values
问题描述
我有3张桌子，CAL，SOURCE，HISTORY.
I have 3 tables, CAL,SOURCE,HISTORY.
CAL TABLE CAL_DATE 01/05/16 02/05/16 03/05/16 04/05/16 05/05/16 06/05/16 07/05/16
CAL TABLE CAL_DATE 01/05/16 02/05/16 03/05/16 04/05/16 05/05/16 06/05/16 07/05/16
SOURCE TABLE
TABLE_ID GROUP
1210 Sales
1211 Reference
1230 Marketing
1245 Sales
1650 Reference
1784 Sales
SOURCE TABLE
TABLE_ID GROUP
1210 Sales
1211 Reference
1230 Marketing
1245 Sales
1650 Reference
1784 Sales
HISTORY
RUN_DATE TABLE_ID STATUS
01/05/16 1210 COMPLETED
02/05/16 1210 COMPLETED
02/05/16 1211 COMPLETED
03/05/16 1211 COMPLETED
01/05/16 1230 COMPLETED
03/05/16 1230 COMPLETED
HISTORY
RUN_DATE TABLE_ID STATUS
01/05/16 1210 COMPLETED
02/05/16 1210 COMPLETED
02/05/16 1211 COMPLETED
03/05/16 1211 COMPLETED
01/05/16 1230 COMPLETED
03/05/16 1230 COMPLETED
我使用的查询.
SELECT TO_CHAR(C.CAL_DATE,'mm/dd/yyyy') AS CAL_DATE,TO_CHAR(C.CAL_DATE,'day') AS WDAY,X.* FROM CAL C LEFT OUTER JOIN
(
SELECT S.GROUP,S.TABLE_ID,H.RUN_DATE,H.STATUS FROM TABLE S
LEFT JOIN HISTORY H
ON S.TABLE_ID=H.TABLE_ID
WHERE 1=1
AND STATUS='COMPLETED'
) X
ON TO_CHAR(C.CAL_DATE,'dd/mm/yyyy')=TO_CHAR(RUN_DATE,'dd/mm/yyyy')
AND X.TABLE_ID IN (1210,1211,1230)
WHERE TO_CHAR(C.CAL_DATE,'mm/dd/yyyy') <= TO_CHAR('03/05/2016','mm/dd/yyyy')
ORDER BY SOURCE_TABLE_ID ASC
下面给出了预期的输出，但是我得到了不同的输出.当我传递多个表ID时，inodation空值会被滤除.请帮助我更正查询.
Expected output is given below but i am getting different output. When I pass more than 1 table id incodition nulls are filtered out. Please help me to correct the query.
CAL_DATE TABLEID Status 01/05/16 1210 Completed 02/05/16 1210 Completed 03/05/16 null null 01/05/16 null null 02/05/16 1211 Completed 03/05/16 1211 Completed 01/05/16 1230 Completed 02/05/16 null null 03/05/16 1230 Completed
CAL_DATE TABLEID Status 01/05/16 1210 Completed 02/05/16 1210 Completed 03/05/16 null null 01/05/16 null null 02/05/16 1211 Completed 03/05/16 1211 Completed 01/05/16 1230 Completed 02/05/16 null null 03/05/16 1230 Completed
推荐答案
您需要更好地了解LEFT JOIN的工作原理(通常是外部联接-左/右和完整[外部]联接)
左联接总是分两个步骤执行:
You need to gain a better understanding how a LEFT JOIN works (outer joins in general - left/right and full [outer] join)
The LEFT JOIN is always performed in two steps:
SELECT ....
FROM table1
LEFT JOIN table1 ON join_conditions
WHERE where_conditions
步骤1-首先执行LEFT JOIN(使用ON子句中指定的条件来连接两个表)
步骤2-将WHERE条件应用于步骤1中的联接所生成的结果集
LEFT JOIN的工作方式-快速提醒:LEFT JOIN总是返回左表中的所有行，即使这些表在右表中不匹配.如果没有匹配项(ON条件的结果为false)，则LEFT JOIN将为右表返回NULL.
RIGHT JOIN以相同的方式工作，但是它返回RIGHT表中的所有行，而不是左行作为LEFT JOIN.
Step 1 - the LEFT JOIN is performed first (using conditions specified in ON clause to join two tables)
Step 2 - the WHERE conditions is applied to a resulset generated by the join in step 1
How the LEFT JOIN works - a quick reminder: LEFT JOIN returns always ALL rows from the left table, even these rows for which there is no match in the right table. When there is no match (the ON condition evaluates to false), LEFT JOIN returns NULLs for the right table.
The RIGHT JOIN works in the same way, but it returns all rows from the RIGHT table, not the left one as LEFT JOIN.
因此，如果您有以下查询:
SO if you have this query:
SELECT S.GROUP,S.TABLE_ID,H.RUN_DATE,H.STATUS
FROM source_table S
LEFT JOIN HISTORY H
ON S.TABLE_ID=H.TABLE_ID
WHERE H.STATUS='COMPLETED'
数据库首先执行LEFT JOIN，即:
the database first performs the LEFT JOIN, that is:
SELECT S.GROUP,S.TABLE_ID,H.RUN_DATE,H.STATUS
FROM source_table S
LEFT JOIN HISTORY H
ON S.TABLE_ID=H.TABLE_ID
上面的查询给出以下结果(注意在最右端的最后3行中为NULL):
The above query gives the following result (notice NULLs in the last 3 rows on the rigth side):
| S.GROUP | S.TABLE_ID | H.RUN_DATE | H.STATUS |
|-----------|------------|----------------------------|-----------|
| Sales | 1210 | January, 05 2016 00:00:00 | COMPLETED |
| Sales | 1210 | February, 05 2016 00:00:00 | COMPLETED |
| Reference | 1211 | February, 05 2016 00:00:00 | COMPLETED |
| Reference | 1211 | March, 05 2016 00:00:00 | COMPLETED |
| Marketing | 1230 | January, 05 2016 00:00:00 | COMPLETED |
| Marketing | 1230 | March, 05 2016 00:00:00 | COMPLETED |
| Sales | 1245 | (null) | (null) |
| Reference | 1650 | (null) | (null) |
| Sales | 1784 | (null) | (null) |
然后数据库对以上结果集执行WHERE条件:
And then the database performs the WHERE condition on the above resultset:
WHERE H.STATUS='COMPLETED'
由于NULL='COMPLETED'的计算结果为FALSE，因此查询的最终结果为:
Since NULL='COMPLETED' evaluates to FALSE, then the final result of the query is:
| GROUP | TABLE_ID | RUN_DATE | STATUS |
|-----------|----------|----------------------------|-----------|
| Sales | 1210 | January, 05 2016 00:00:00 | COMPLETED |
| Sales | 1210 | February, 05 2016 00:00:00 | COMPLETED |
| Reference | 1211 | February, 05 2016 00:00:00 | COMPLETED |
| Reference | 1211 | March, 05 2016 00:00:00 | COMPLETED |
| Marketing | 1230 | January, 05 2016 00:00:00 | COMPLETED |
| Marketing | 1230 | March, 05 2016 00:00:00 | COMPLETED |
即:所有NULL都被跳过.
观看此演示: http://sqlfiddle.com/#!9/e2ed0/3
that is: all NULLs were skipped.
See this demo: http://sqlfiddle.com/#!9/e2ed0/3
如果您还想获取具有NULL值的记录，则需要将此条件更改为:
If you want to get also records with NULL values, you need to change this condition to:
WHERE ( H.STATUS='COMPLETED' OR H.STATUS IS NULL )
您还可以从WHERE子句中删除该条件，并将其添加到LEFT JOIN的ON条件，即:
you can also remove ths condition from the WHERE clause, and add it to the ON condition of the LEFT JOIN, that is:
SELECT S.GROUP,S.TABLE_ID,H.RUN_DATE,H.STATUS
FROM source_table S
LEFT JOIN HISTORY H
ON ( S.TABLE_ID=H.TABLE_ID AND H.STATUS='COMPLETED' )
请参阅此演示中的最后一个查询: http://sqlfiddle.com/#!9/e2ed0/3
see the last query in this demo: http://sqlfiddle.com/#!9/e2ed0/3
这篇关于Oracle-多个表上的左外部联接未返回所需的空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
