左外连接未将第二个表显示为null

Table structre table 1

表结构表1

account 
123
1234
12345
123456

table 2
account
123
1234
12345

I want to return table a record 123456 on account for table1 and null for column 2 when it doesnt match table 2

我想在table1上为表返回一个记录123456，当它与表2不匹配时返回第二列的null

SQL
SELECT  table1.account, table2.account
from table1 
left outer join table2
on (table1.account= table2.account)

2 个解决方案

#1

Your where statement explicitly asked for non-null rows with table2.dates = '19-jul-17'

你的where语句明确要求table2.dates = '19 -jul-17'的非空行

You should modify your query to check for nulls:

您应该修改查询以检查空值：

SELECT  
    table1.account, table2.account
from table1 
left outer join table2
     on (table1.account= table2.account)
where 
    t1.dates='20170719' 
    and ( table2.account is NULL 
          or 
          table2.dates = '20170719'
        )

This matches rows that have a specific date in the first table, and either null or a specific date on the second.

这匹配第一个表中具有特定日期的行，以及第二个表中的null或特定日期。

Note the date literal. The original query used a locale-specific format. This can fail easily faile in locales that don't use that format. Never mind the two digit year.

请注意日期文字。原始查询使用特定于语言环境的格式。在不使用该格式的语言环境中，这很容易失败。别介意两位数的年份。

YYYYMMDD on the other hand is unambiguous.

另一方面，YYYYMMDD是明确的。

UPDATE

UPDATE

Once the where clause is removed, NULLs are returned as expected :

删除where子句后，将按预期返回NULL：

declare @table1 table (id int)

declare @table2 table (id int)

insert into @table1 
values
(123   ),
(1234  ),
(12345 ),
(123456)

insert into @table2 
values
(123  ),
(1234 ),
(12345)

SELECT t1.id, t2.id
from @table1 t1
left outer join @table2 t2
on (t1.id= t2.id)

Returns

id     id
123    123
1234   1234
12345  12345
123456 NULL

If the question is "how do I get the non-matching row" the answer is use WHERE tabl2.ID IS NULL

如果问题是“我如何获得不匹配的行”，则答案是使用WHERE tabl2.ID IS NULL

#2

Everything is OK in your query, If you are using any where clause, please remove and check, BTW i am not able to reproduce your issue. PFB attempt, The query gives expected result

在您的查询中一切正常，如果您使用任何where子句，请删除并检查，BTW我无法重现您的问题。 PFB尝试，查询给出预期结果

create table #tmp1( ID int)
create table #tmp2( ID int)

Insert into #tmp1 values('123')
Insert into #tmp1 values ('1234')
Insert into #tmp1 values ('12345')
Insert into #tmp1 values ('123456')

Insert into #tmp2 values('123')
Insert into #tmp2 values ('1234')
Insert into #tmp2 values ('12345')

select * from #tmp1
select * from #tmp2

SELECT #tmp1.ID, #tmp2.ID from #tmp1 left outer join #tmp2 on (#tmp1.ID=#tmp2.ID)

drop table #tmp1
drop table #tmp2

The result is:

结果是：

ID  ID
123 123
1234    1234
12345   12345
123456  NULL

#1