我有一个非常基本的LEFT OUTER JOIN来返回来自左表的所有结果和来自一个大得多的表的一些附加信息。左表包含4935条记录,但当我left OUTER JOIN到另一个表时,记录计数明显更大。

据我所知,这是绝对的福音,一个LEFT OUTER JOIN将返回所有记录从左表与匹配的记录从右表和空值的任何行不能匹配,因此,这是我的理解,它应该不可能返回更多的行比存在于左表,但它的发生都一样!

SQL查询如下:

SELECT     SUSP.Susp_Visits.SuspReason, SUSP.Susp_Visits.SiteID
FROM         SUSP.Susp_Visits LEFT OUTER JOIN
                      DATA.Dim_Member ON SUSP.Susp_Visits.MemID = DATA.Dim_Member.MembershipNum

也许我在语法上犯了一个错误,或者我对LEFT OUTER JOIN的理解是不完整的,希望有人能解释这是如何发生的?


当前回答

简单地说,LEFT OUTER JOIN是每个连接键内的笛卡尔积,以及左表中不匹配的行 (例如,对于每个key_x,在table_L中有N条记录,在table_R中有M条记录,如果M>0,结果将有N*M条记录,或者如果M=0,结果将有N条记录)

其他回答

Table1                Table2
_______               _________
1                      2
2                      2
3                      5
4                      6

SELECT Table1.Id, 
       Table2.Id 
FROM Table1 
LEFT OUTER JOIN Table2 ON Table1.Id=Table2.Id

结果:

1,null
2,2
2,2
3,null
4,null

您的查询将返回比左侧表(SUSP)更多行的唯一方法。在你的例子中是sus_visitors),是条件(sus_sus_visitors。MemID = DATA.Dim_Member. membershipnum)匹配右边表DATA.Dim_Member中的多行。因此,DATA中有多行。其中DATA.Dim_Member.MembershipNum的值相同。你可以通过执行下面的查询来验证:

选择DATA.Dim_Member。MembershipNum, count(DATA. dim_member .MembershipNum) from DATA。根据DATA.Dim_Member.MembershipNum指定成员组

DATA中似乎有多行。每个SUSP的Dim_Member表。Susp_Visits行。

如果你只需要右边的任意一行

SELECT SuspReason, SiteID FROM(
    SELECT SUSP.Susp_Visits.SuspReason, SUSP.Susp_Visits.SiteID, ROW_NUMBER()
    OVER(PARTITION BY SUSP.Susp_Visits.SiteID) AS rn
    FROM SUSP.Susp_Visits
    LEFT OUTER JOIN DATA.Dim_Member ON SUSP.Susp_Visits.MemID = DATA.Dim_Member.MembershipNum
) AS t
WHERE rn=1

或者只是

SELECT SUSP.Susp_Visits.SuspReason, SUSP.Susp_Visits.SiteID
FROM SUSP.Susp_Visits WHERE EXISTS(
    SELECT DATA.Dim_Member WHERE SUSP.Susp_Visits.MemID = DATA.Dim_Member.MembershipNum
)

由于左边的表包含4935条记录,我怀疑您希望结果返回4935条记录。试试这个:

create table table1
(siteID int, 
SuspReason int)

create table table2
(siteID int, 
SuspReason int)

insert into table1(siteID, SuspReason) values 
(1, 678), 
(1, 186), 
(1, 723)
    
insert into table2(siteID, SuspReason) values 
(1, 678),
(1, 965)
   
select distinct t1.siteID, t1.SuspReason
from table1 t1 left join table2 t2 on t1.siteID = t2.siteID and t1.SuspReason = t2.SuspReason

union 

select distinct t2.siteID, t2.SuspReason 
from table1 t1 right join table2 t2 on t1.siteID = t2.siteID and t1.SuspReason = t2.SuspReason