oracle删除左侧0,Oracle留下的外部联接返回0行，其中包含左侧数据集中的数据(Oracle left outer join return 0 rows with data in left d...-CSDN博客

Oracle留下的外部联接返回0行，其中包含左侧数据集中的数据(Oracle left outer join return 0 rows with data in left dataset)

我试图获得硬编码事件列表并加入一个包含实际事件的表格，所以我可以看到它们是否被执行。

这是我的查询：

SELECT a.Verification FROM (SELECT 'Verify ID changed' as Verification FROM DUAL

UNION ALL

SELECT 'Verify manual Valve closed' as Verification FROM DUAL

UNION ALL

SELECT 'Verify manual Valve open' as Verification FROM DUAL

UNION ALL

SELECT 'Visually verify Things' as Verification FROM DUAL

) a

LEFT OUTER JOIN JOBEVENTS c ON a.Verification = c.EVENTDESC

WHERE c.EVT_TIME BETWEEN '22-Jan-2017' AND '22-Jan-2017'

当我阅读LEFT Outer join的文档时，队列应该总是从左表返回数据，那么为什么我的查询返回0行(JOBEVENTS事件表中没有任何内容，但硬编码值始终存在)。

我误解了LEFT OUTER JOIN如何工作？

I’m trying to get a list of hard-coded events and join in a table with actual events, so I can actually see if they were performed.

Here are my query:

SELECT a.Verification FROM (SELECT 'Verify ID changed' as Verification FROM DUAL

UNION ALL

SELECT 'Verify manual Valve closed' as Verification FROM DUAL

UNION ALL

SELECT 'Verify manual Valve open' as Verification FROM DUAL

UNION ALL

SELECT 'Visually verify Things' as Verification FROM DUAL

) a

LEFT OUTER JOIN JOBEVENTS c ON a.Verification = c.EVENTDESC

WHERE c.EVT_TIME BETWEEN '22-Jan-2017' AND '22-Jan-2017'

When I read the documentation for LEFT Outer join, then queues should always return the data from the left table, so why is my query returning 0 rows (there is nothing in the JOBEVENTS events table but left hard-coded values are always there).

Am i misunderstanding how LEFT OUTER JOIN works?

原文：https://stackoverflow.com/questions/47361049

更新时间：2020-03-01 06:24

最满意答案

将过滤器放在连接的左连接表部分，而不是where子句。

SELECT a.Verification, c.*

FROM (

SELECT 'Verify ID changed' as Verification FROM DUAL UNION ALL

SELECT 'Verify manual Valve closed' as Verification FROM DUAL UNION ALL

SELECT 'Verify manual Valve open' as Verification FROM DUAL UNION ALL

SELECT 'Visually verify Things' as Verification FROM DUAL

) a

LEFT OUTER JOIN JOBEVENTS c ON a.Verification = c.EVENTDESC

AND c.EVT_TIME BETWEEN '22-Jan-2017' AND '22-Jan-2017'

;

或者，您可以添加到where子句中，以便可以从左连接的表中返回NULLS。例如：

SELECT a.Verification, c.*

FROM (

SELECT 'Verify ID changed' as Verification FROM DUAL UNION ALL

SELECT 'Verify manual Valve closed' as Verification FROM DUAL UNION ALL

SELECT 'Verify manual Valve open' as Verification FROM DUAL UNION ALL

SELECT 'Visually verify Things' as Verification FROM DUAL

) a

LEFT OUTER JOIN JOBEVENTS c ON a.Verification = c.EVENTDESC

WHERE ( c.EVT_TIME BETWEEN '22-Jan-2017' AND '22-Jan-2017'

OR c.EVENTDESC IS NULL

)

;

在允许来自该表的NULLS的where子句中引用时，请始终注意外连接表，否则您创建内连接(有时称为“隐含内连接”)的等价条件。

Make the filter on the left joined table part of the join, not the where clause.

SELECT a.Verification, c.*

FROM (

SELECT 'Verify ID changed' as Verification FROM DUAL UNION ALL

SELECT 'Verify manual Valve closed' as Verification FROM DUAL UNION ALL

SELECT 'Verify manual Valve open' as Verification FROM DUAL UNION ALL

SELECT 'Visually verify Things' as Verification FROM DUAL

) a

LEFT OUTER JOIN JOBEVENTS c ON a.Verification = c.EVENTDESC

AND c.EVT_TIME BETWEEN '22-Jan-2017' AND '22-Jan-2017'

;

Alternatively, you can to add to your where clause so that NULLS may be returned from the left joined table. e.g.:

SELECT a.Verification, c.*

FROM (

SELECT 'Verify ID changed' as Verification FROM DUAL UNION ALL

SELECT 'Verify manual Valve closed' as Verification FROM DUAL UNION ALL

SELECT 'Verify manual Valve open' as Verification FROM DUAL UNION ALL

SELECT 'Visually verify Things' as Verification FROM DUAL

) a

LEFT OUTER JOIN JOBEVENTS c ON a.Verification = c.EVENTDESC

WHERE ( c.EVT_TIME BETWEEN '22-Jan-2017' AND '22-Jan-2017'

OR c.EVENTDESC IS NULL

)

;

Always take care with outer joined tables when referenced in the where clause that you permit NULLS from that table, otherwise you create the equivalent conditions of an inner join (& sometimes called "implied inner join").

2017-11-18

相关问答

LEFT OUTER JOIN将返回与可能的RIGHT表连接的LEFT表中的所有记录。如果有匹配，它仍然会返回匹配的所有行，因此，在LEFT中匹配两行的一行在RIGHT中将返回为两个ROWS，就像INNER JOIN一样。编辑：为了回应你的编辑，我刚刚看了一下你的查询，看起来你只是从LEFT表中返回数据。因此，如果您仅希望来自LEFT表的数据，并且只需要在LEFT表中的每一行返回一行，那么您根本无需执行JOIN，只需从LEFT表直接执行SELECT。 The LEFT OUTER JOIN

...

概要我不认为你需要左，右或全连接这个特定的查询，因为基于GROUP BY / CASE的解决方案应该工作得很好并且至少快两倍。问题定义我发现这个问题相当有趣，因为它似乎来自现实生活中的情况，我相信在现实生活中，如果有必要，很少有人FULL JOIN 。所以对我来说真正的问题不是如何组合来自左右连接的数据，而是为什么首先需要完全连接？此外，简单地用FULL替换LEFT是不够的，因为这会导致同一个月的重复行; 最近一年没有月份的NULL月份。所以我决定继续解密查询。表和数据设置不幸的

...

首先，使用let来做这样的连接首先并不理想。 LINQ中有一个join子句，原因是:) LINQ中不特别支持左外连接，但您可以像这样伪造它们： var minOrders = from customer in DataSet.Customers

join order in DataSet.Orders.OrderBy(o => o.OrderTimestamp)

on customer.Cu

...

根据这些结果，您的calendar表似乎包含重复的行。您可以通过运行来检查： SELECT datefield, COUNT(*) FROM calendar

WHERE datefield LIKE '2012-11-%'

GROUP BY datefield

HAVING COUNT(*) > 1

如果返回任何行，那么在查询按预期工作之前，您需要解决重复项。 Based on these results, it seems that your calendar table contains

...

你必须使用GROUP BY SELECT SQL_CALC_FOUND_ROWS `p`.`id`,

`p`.`title`,

`p`.`live`,

`p`.`startDate`,

`p`.`endDate`,

COUNT(`part`.`id`) AS `participants`

FROM `projects` `p`

LEFT OUTER JOIN `participants` `part`

ON `p`

...

你很近。只需添加一个where子句： SELECT A.*

FROM A LEFT OUTER JOIN

ON A.abc = B.abc

WHERE B.abc IS NULL;

更传统的方法使用NOT EXISTS ： select a.*

from a

where not exists (select 1 from b where b.abc = a.abc);

这是一个SQL小提琴，说明第一个工作。 You are very close. Just add a

...

这应该可以解决问题。 SELECT c.id,

student_id

FROM class c

LEFT JOIN class_has_student chs

INNER JOIN student s

ON chs.student_id = s.id

AND s.id = 1001

...

左外连接将获得表a中的所有行/数据，无论它们在表b中是匹配还是不匹配。但是你再次通过在where子句中添加条件来过滤掉数据。因为，b中没有b.active = 1的条目，所以没有输出。从查询中删除b.active = 1，如下所示： SELECT a.user_id,

a.user_name,

b.first_name

FROM users a

LEFT OUTER JOIN members b

ON a.member_uid = b.uid

WHERE a.user_nam

...

将过滤器放在连接的左连接表部分，而不是where子句。 SELECT a.Verification, c.*

FROM (

SELECT 'Verify ID changed' as Verification FROM DUAL UNION ALL

SELECT 'Verify manual Valve closed' as Verification FROM DUAL UNION ALL

SELECT 'Verify manual Valve open' a

...

如果ORG_NO在RF_MERCHANT_ORG表内(一直使用别名可以帮助那里)，那么就像一个内部连接一样，将会是正在使用的SQL的正确结果。连接应该是这样，使它像一个适当的左连接： LEFT OUTER JOIN RF_MERCHANT_ORG MO ON ROW_SEG.DEPT_NO = MO.DEPT_NO AND MO.ORG_NO = 100

If ORG_NO is within the RF_MERCHANT_ORG table (using aliases consisten

...