Oracle-带where子句的左外部联接 [英] Oracle - Left outer join with where clause

问题描述

我在定义为左外部联接的两个表上有一个联接，以便所有记录都从左表返回，即使它们在右表中没有记录也是如此.但是，我还需要包含一个where子句，但是....我仍然希望为左侧表中的每个记录返回左侧表中的一行，即使不满足where子句中的条件.有办法吗?

I have a join on two tables defined as a left outer join so that all records are returned from the left hand table even if they don't have a record in the right hand table. However I also need to include a where clause but.... I still want a row from the left-hand table to be returned for each record in the left-hand table even if the condition in the where clause isn't met. Is there a way of doing this?

我正在使用

SELECT A.*, B.* 
FROM A 
LEFT OUTER JOIN B ON A.VIN = B.VIN AND 
TRUNC(a.REP_OPEN_DATE) BETWEEN TRUNC(b.CHECK_IN_DATE)+1 AND TRUNC(b.CHECK_IN_DATE)-1

以上条件不返回任何行.符合以下条件的地方...

above condition not returning any rows. where as below condition returns...

SELECT A.*, B.* 
FROM A 
LEFT OUTER JOIN B ON A.VIN = B.VIN

即使不满足以下条件，我也需要左表中的数据...

I need the data from the left table even if the below condition doesn't met...

TRUNC(a.REP_OPEN_DATE) BETWEEN TRUNC(b.CHECK_IN_DATE)+1 AND TRUNC(b.CHECK_IN_DATE)-1

有人可以帮忙吗?

表:-

VIN    | RO_OPEN_DATE
1234   | 04-NOV-13
6789   | 09-NOV-13

B表

VIN    | CHECK_IN_DATE
1234   | 09-NOV-13
1234   | 05-NOV-13
6789   | 20-OCT-14
6789   | 29-OCT-14

应该输出

   VIN     | RO_OPEN_DATE  | CHECK_IN_DATE
   1234    | 04-NOV-13     | 05-NOV-13
   6789    | 09-NOV-13     | NULL

Condition :-For EACH RO_OPEN_DATE for a VIN, We need to check if we have +/- 1 day of CHECK_IN_DATE from RO_OPEN_DATE.

推荐答案

您的解释与您的查询不相关.您已经提到

Your explanation does not correlate with your query. You have mentioned

"但是我还需要包括一个where子句，但是....我仍然希望为左表中的每个记录返回左表中的一行，即使不符合where子句."

所以我相信您的查询看起来像这样

So I believe your query looks something like this

SELECT a.*, 
       b.* 
FROM   a 
       LEFT OUTER JOIN b 
                    ON a.vin = b.vin 
WHERE  Trunc(a.rep_open_date) BETWEEN Trunc(b.check_in_date) + 1 AND 
                                      Trunc(b.check_in_date) - 1 

在上面的LEFT OUTER JOIN由于Where子句中右表的过滤，将转换为INNER JOIN

In the above the LEFT OUTER JOIN will be converted into INNER JOIN due to the filtration of right table in Where clause

因此，如您在第一次查询中所使用的那样，正确的表过滤器应该是JOIN条件的一部分，即使RIGHT边表中没有匹配的记录，它也会从LEFT表返回行.

So as you have used in first query the right table filters should be part of JOIN condition, Which will return rows from LEFT table even though there is no matching records in RIGHT side table.

SELECT a.*, 
       b.* 
FROM   a 
       left outer join b 
                    ON a.vin = b.vin 
                       AND Trunc(a.rep_open_date) BETWEEN 
                           Trunc(b.check_in_date) + 1 AND 
                           Trunc(b.check_in_date) - 1 

更新:

您已经在像10 between 11 and 9这样的运算符之间使用过，但是它应该是10 between 9 and 11

You have used between operator like 10 between 11 and 9 but it should be 10 between 9 and 11

SELECT a.*, 
       b.* 
FROM   a 
       left outer join b 
                    ON a.vin = b.vin 
                       AND CAST(a.rep_open_date as date) BETWEEN 
                           CAST(b.check_in_date as date) - 1 AND 
                           CAST(b.check_in_date as date) + 1 

这篇关于Oracle-带where子句的左外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！