如何用例在表B中创建一个新列来显示表a中的数据是否也在表B中

Question

当我尝试以下代码时，它返回附加的错误代码

select  acct_key,
(case when acct_key in (
select acct_key
from table_A
) 
then 1 else 0 end) as acct_indicator
from  table_B;

作为单个语句执行。失败[804:08S01] Socket通信失败发送包Wed 5月25日12:01:18 CDT 2022 Socket origin =VTDSSBP.rw.discoverfinancial.com local=/10.2.181.125:61593 remote=VTDSSBPcop2.rw.discoverfinancial.com/170.217.201.43:1025 keepalive=false nodelay=false receive=65536 send=64512 linger=10 traffic=0 concurrent=3 contimeout=10000 conwait=1000 connecttime=38 connecttotaltime=38 connectattempts=1 connectfailures=0 reconnectattempts=0 recoverable=false redrive=false failurecache={} cid=1221d607sess=106395528 java.net.SocketException:软件导致连接中断:socket write error at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(未知来源)at java.net.SocketOutputStream.write(未知来源)at…

Answer 1

尝试1:

select  a.acct_key,
case when exists (
select acct_key
from table b
where a.acct_key = b.acct_key
) 
then 1 
else 0
end as acct_indicator
from  table a;

失败在case表达式

的When子句中返回非法表达式尝试2:

select a.acct_key,
case when b.acct_key is not null
then 'yes'
else 'no'
end as acct_ind
from table a
left join table b 
on a.acct_key = b.acct_key;

成功