Oracle外部联接无法按预期工作 [英] Oracle outer join not working as expected

问题描述

我有以下查询:

select *
from   per_all_assignments_f paaf,
       pay_all_payrolls_f    payr
where  sysdate between paaf.effective_start_date and paaf.effective_end_date
and    paaf.assignment_type in ('E', 'C')
and    paaf.primary_flag = 'Y'
and    payr.payroll_id (+) = paaf.payroll_id
and    nvl(payr.attribute1 (+), '$XXX$') in ('TINT')
and    paaf.effective_start_date between nvl(payr.effective_start_date (+), to_date('01/01/1000', 'dd/mm/yyyy')) 
                                     and nvl(payr.effective_end_date   (+), to_date('31/12/4712', 'dd/mm/yyyy'))

由于TINT工资表上有7个员工，我预计将获得7个员工分配.但是，它将返回10035行，其中7行包含工资表的详细信息，而其他行具有空白数据.因此，外部联接无法正常工作.我在做什么错了?

I'm expecting to get back 7 employee assignments due to the fact that there are 7 on the TINT payroll. However it returns 10035 rows with 7 having details for the payroll table and the other ones having blank data. So the outer join is not working as I expected. What am I doing wrong?

推荐答案

听起来您真正想要的是一个内部联接.放下(+)，看看您是否找回了想要的东西.此外，@ GordonLinoff提出了一个很好的建议-习惯使用ANSI连接语法，比老式的将WHERE子句中的所有条件都放入，然后尝试将其迷惑"风格更富有表现力和更易于理解.

It sounds like what you really want is an INNER join. Drop the (+)'s and see if you get back what you're looking for. In addition, @GordonLinoff makes an excellent suggestion - get comfortable with using the ANSI join syntax, which is both more expressive and more comprehensible than the old-style "put all the conditions in the WHERE clause and then try to puzzle it out" style.

这是我使用ANSI标准连接语法重写此查询的方式:

Here's how I'd rewrite this query using the ANSI-standard join syntax:

SELECT *
  FROM PER_ALL_ASSIGNMENTS_F paaf
  INNER JOIN PAY_ALL_PAYROLLS_F payr
    ON payr.PAYROLL_ID = paaf.PAYROLL_ID
  WHERE SYSDATE BETWEEN paaf.EFFECTIVE_START_DATE
                    AND paaf.EFFECTIVE_END_DATE AND
        paaf.ASSIGNMENT_TYPE IN ('E', 'C') AND
        paaf.PRIMARY_FLAG = 'Y' AND
        payr.ATTRIBUTE1 = 'TINT' AND
        paaf.EFFECTIVE_START_DATE BETWEEN NVL(payr.EFFECTIVE_START_DATE, TO_DATE('01/01/1000', 'DD/MM/YYYY')) 
                                      AND NVL(payr.EFFECTIVE_END_DATE, TO_DATE('31/12/4712', 'DD/MM/YYYY'))

另外一件小事.我注意到您正在将BETWEEN与日期值一起使用，这可能会出现问题.举例来说，假设您在PER_ALL_ASSIGNMENTS_F的某行上具有2013年1月1日的EFFECTIVE_START_DATE和2013年6月30日的EFFECTIVE_END_DATE，并且进一步说，当前的日期和时间是2013年6月30日在07:02:04 AM.给定这些数据值之后，将否选择PER_ALL_ASSIGNMENTS_F中的这一行，尽管您可能希望如此.问题是，当在DATE值上填写的唯一字段是日，月和年时，则小时，分钟和秒默认为零-因此结束日期为 really 30- 2013年6月30日上午0:00，这是当前日期/时间，2013年6月30日上午7:02:04，因此日期比较导致该行被忽略.我不知道如何在您的数据库中填充EFFECTIVE_END_DATE字段-希望它们被完全填充，例如30-JUN-2013 23:59:59-但如果不是这样，您可能需要进行日期比较

One other minor matter. I notice you're using BETWEEN with date values which can potentially be problematic. Let's say, for instance, the you've got an EFFECTIVE_START_DATE of 01-JAN-2013 and an EFFECTIVE_END_DATE of 30-JUN-2013 on some row in PER_ALL_ASSIGNMENTS_F, and let's further say that the current date and time are 30-JUN-2013 at 07:02:04 AM. Given these data values, this row from PER_ALL_ASSIGNMENTS_F will NOT be selected, although you might expect it to be. The issue is that when the only fields filled in on a DATE value are the day, month, and year, then the hour, minutes, and seconds default to zero - thus the end date is really 30-JUN-2013 at 00:00:00, which is prior to the current date/time of 30-JUN-2013 at 07:02:04 AM, and consequently the date comparison causes the row to be ignored. I don't know how the EFFECTIVE_END_DATE fields are populated in your database - hopefully they're completely filled in, e.g. 30-JUN-2013 23:59:59 - but if not you might need to make your date comparison something like

TRUNC(SYSDATE) BETWEEN paaf.EFFECTIVE_START_DATE
                   AND paaf.EFFECTIVE_END_DATE

或

SYSDATE BETWEEN paaf.EFFECTIVE_START_DATE
            AND paaf.EFFECTIVE_END_DATE + INTERVAL '1' DAY - INTERVAL '1' SECOND

(前者可能是一个更好的选择，因为后者的形式将阻止使用可能在(EFFECTIVE_START_DATE，EFFECTIVE_END_DATE)存在的任何非基于函数的索引.

(The former is probably a better choice, as the latter form will preclude the use of any non-function-based index which may exist on exist on (EFFECTIVE_START_DATE, EFFECTIVE_END_DATE).

分享并享受.

这篇关于Oracle外部联接无法按预期工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！