使用外连接时where子句中的'OR'的SQL替代方法

I have the following query:

我有以下查询:

select * from
from assignments dah, employees emp 
where 
    dah.person_id=emp.person_id(+)
and 
(dah.effective_end_date between emp.date_from(+) and emp.date_to(+)
and dah.effective_end_date between emp.valid_from(+) and emp.valid_to(+))
or   
(dah.effective_start_date between emp.date_from(+) and emp.date_to(+)
and dah.effective_start_date between emp.valid_from(+) and emp.valid_to(+))

I get the following message: 'outer join operator (+) not allowed in operand of OR or IN'. I know about using 2 unions with inner joins is a solution, but I can't use it because I actually have a lot of code (code I provided is just an example).

我得到以下消息:'外部连接运算符(+)不允许在OR或IN'的操作数中。我知道使用2个带有内连接的联合是一个解决方案,但我不能使用它因为我实际上有很多代码(我提供的代码只是一个例子)。

Edit: i need this done via oracle syntax, because i work with data warehousing and our ETL doesn't fully support explicit syntax. Maybe there is something i am not seeing and this can be written differently?

编辑:我需要通过oracle语法完成此操作,因为我使用数据仓库,我们的ETL不完全支持显式语法。也许有一些我没有看到的东西,这可以用不同的方式写出来?

Edit nr.2 : Maybe date overlapping logic can somehow be implemented without using OR and with oracle syntax?

编辑nr.2:可能以某种方式实现日期重叠逻辑而不使用OR和oracle语法?

5 个解决方案

#1

Since you have to use the old-style outer join syntax, here's one way (simplified, since you didn't supply us with sample data and/or table creation scripts):

由于您必须使用旧式外连接语法,因此这是一种方式(简化,因为您没有向我们提供示例数据和/或表创建脚本):

with assignments as (select 1 assignment_id, 1 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 2 assignment_id, 1 person_id, to_date('02/08/2015', 'dd/mm/yyyy') start_date, to_date('04/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 3 assignment_id, 1 person_id, to_date('06/08/2015', 'dd/mm/yyyy') start_date, to_date('10/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 4 assignment_id, 2 person_id, to_date('02/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual),
       employees as (select 1 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 3 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual)
select *
from   assignments dah,
       employees emp
where  dah.person_id = emp.person_id (+)
and    dah.start_date <= emp.end_date (+)
and    dah.end_date >= emp.start_date (+);

ASSIGNMENT_ID  PERSON_ID START_DATE END_DATE   PERSON_ID_1 START_DATE_1 END_DATE_1
------------- ---------- ---------- ---------- ----------- ------------ ----------
            2          1 02/08/2015 04/08/2015           1 01/08/2015   03/08/2015
            1          1 01/08/2015 03/08/2015           1 01/08/2015   03/08/2015
            3          1 06/08/2015 10/08/2015                                    
            4          2 02/08/2015 03/08/2015          

Are you sure you got your outer joins the right way round? Are you sure you're not actually after the following instead?:

你确定你的外部连接是正确的吗?你确定你实际上不是在追求以下事项吗?:

with assignments as (select 1 assignment_id, 1 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 2 assignment_id, 1 person_id, to_date('02/08/2015', 'dd/mm/yyyy') start_date, to_date('04/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 3 assignment_id, 1 person_id, to_date('06/08/2015', 'dd/mm/yyyy') start_date, to_date('10/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 4 assignment_id, 2 person_id, to_date('02/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual),
       employees as (select 1 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 3 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual)
select *
from   assignments dah,
       employees emp
where  dah.person_id (+) = emp.person_id
and    dah.start_date (+) <= emp.end_date
and    dah.end_date (+) >= emp.start_date;

ASSIGNMENT_ID  PERSON_ID START_DATE END_DATE   PERSON_ID_1 START_DATE_1 END_DATE_1
------------- ---------- ---------- ---------- ----------- ------------ ----------
            1          1 01/08/2015 03/08/2015           1 01/08/2015   03/08/2015
            2          1 02/08/2015 04/08/2015           1 01/08/2015   03/08/2015
                                                         3 01/08/2015   03/08/2015

#2

Use explicit left join syntax:

使用显式左连接语法:

select *
from employees emp left join
     assignments dah 
     on dah.person_id = emp.person_id and
        ((dah.effective_end_date between emp.date_from and emp.date_to and
          dah.effective_end_date between emp.valid_from and emp.valid_to
         ) or
         (dah.effective_start_date between emp.date_from and emp.date_to and
          dah.effective_start_date between emp.valid_from and emp.valid_to
         )
        );

A simple rule is never to use a comma in the from clause. Always use explicit join syntax.

一个简单的规则是永远不要在from子句中使用逗号。始终使用显式连接语法。

Note: Technically, your outer join syntax would have the tables in the other order:

注意:从技术上讲,您的外连接语法将具有其他顺序的表:

from assignments dah left join
     employees emp 
     on . . .

I swapped them on purpose. The left join keeps all rows in the first table, even those with no matches. The + syntax is harder to follow. The + goes on the side that would get the NULL values. However, to me, this seems less likely that the unmatched rows are in the assignments table.

我是故意交换它们的。左连接保留第一个表中的所有行,即使那些没有匹配的行也是如此。 +语法更难以遵循。 +会在获得NULL值的一侧。但是,对我来说,不匹配的行似乎不太可能在赋值表中。

If you have proper foreign key relationships, then all the assignments should have a correct person. I may not understand you data, however, and you might want to reverse your tables for what you are really trying to do.

如果您有适当的外键关系,那么所有作业都应该有一个正确的人。但是,我可能不了解您的数据,您可能想要反转表格以了解您真正想要做的事情。

EDIT:

As for overlaps, I would be inclined to use the simpler:

至于重叠,我倾向于使用更简单的方法:

     on dah.person_id = emp.person_id and
        (dah.effective_end_date >= emp.date_from and
         dah.effective_start_date <= emp.date_to 
        )

You can even write this using the archaic + notation, if you like. Also note: these do not do exactly the same things. This will detect overlaps where one period is entirely embedded in another period.

如果你愿意,你甚至可以使用古老的+符号来写这个。另请注意:这些并不完全相同。这将检测一个周期完全嵌入另一个周期的重叠。

#3

It should work if you translate the deprecated outer join operator ((+)) to an explicit outer join:

如果将已弃用的外连接运算符((+))转换为显式外连接,它应该有效:

SELECT          *
FROM            assignments dah
LEFT OUTER JOIN employees emp ON
                dah.person_id = emp.person_id AND
                ((dah.effective_end_date BETWEEN emp.date_from AND 
                                                 emp.date_to AND 
                  dah.effective_end_date BETWEEN emp.valid_from AND 
                                                 emp.valid_to) OR
                (dah.effective_start_date BETWEEN emp.date_from AND 
                                                  emp.date_to AND
                 dah.effective_start_date BETWEEN emp.valid_from AND 
                                                  emp.valid_to)
                )

#4

Please only use this, if you cannot use ANSI LEFT OUTER JOIN Syntax:

如果您不能使用ANSI LEFT OUTER JOIN语法,请仅使用此语法:

First: You are missing parenthesis in your query - second aside from the initial JOIN you can rewrite x between min(+) AND max(+) as (min is NULL OR x >= min) AND (max is NULL OR x <= max)

第一:您在查询中缺少括号 - 除了初始JOIN之外,您可以在min(+)和max(+)之间重写x,因为(min是NULL或x> = min)AND(max是NULL或x <=最大)

SELECT *
FROM assignments dah, employees emp 
WHERE
    dah.person_id = emp.person_id(+)
AND 
(
      (emp.date_from IS NULL OR dah.effective_start_date >= emp.date_from)
  AND (emp.date_to IS NULL OR dah.effective_start_date <= emp.date_to)
  AND (emp.valid_from IS NULL OR dah.effective_start_date >= emp.valid_from)
  AND (emp.valid_to IS NULL OR dah.effective_start_date <= emp.valid_to)

  OR

      (emp.date_from IS NULL OR dah.effective_end_date >= emp.date_from)
  AND (emp.date_to IS NULL OR dah.effective_end_date <= emp.date_to)
  AND (emp.valid_from IS NULL OR dah.effective_end_date >= emp.valid_from)
  AND (emp.valid_to IS NULL OR dah.effective_end_date <= emp.valid_to)
)

I think this selects what you want - a left join with all rows, where start_date or end_date is between the two dates.

我认为这会选择你想要的 - 一个包含所有行的左连接,其中start_date或end_date在两个日期之间。

You want all rows which either result from a LEFT JOIN with id and right start-date OR rows with the end-date only, without any id to join on... Your query was essentially this: WHERE ( id1=id2(+) AND ...) OR ( ... ) because AND is stronger binding than OR.

您希望所有行都来自LEFT JOIN的id和右开始日期OR行只有end-date,没有任何id加入...你的查询本质上是这样的:WHERE(id1 = id2(+) AND ...)OR(...)因为AND比OR更强。

If you know that emp.date_from and emp.date_to are both valid or NULL

So if there is never a case where only date_from is NULL, but date_to is valid you can shorten the statement considerably:

因此,如果永远不会出现只有date_from为NULL,但date_to有效的情况,则可以大大缩短语句:

SELECT *
FROM assignments dah, employees emp 
WHERE
    dah.person_id = emp.person_id(+)
AND 
(
     emp.date_from IS NULL
  OR dah.effective_start_date BETWEEN emp.date_from AND emp.date_to
     AND dah.effective_start_date BETWEEN emp.valid_from AND emp.valid_to
  OR dah.effective_end_date BETWEEN emp.date_from AND emp.date_to
     AND dah.effective_end_date BETWEEN emp.valid_from AND emp.valid_to
)

#5

You need to use explicit LEFT JOIN like this:

你需要像这样使用显式的LEFT JOIN:

SELECT * FROM
    assignments dah LEFT JOIN 
    employees emp ON dah.person_id=emp.person_id
AND 
(dah.effective_end_date between emp.date_from and emp.date_to
and dah.effective_end_date between emp.valid_from and emp.valid_to)
or   
(dah.effective_start_date between emp.date_from and emp.date_to
and dah.effective_start_date between emp.valid_from and emp.valid_to)