# 选择不存在于另一表的数据几种写法

## 看看以下三种写法:

WHERE A.key NOT IN (SELECT key FROM B);

LEFT JOIN B ON A.key = B.key

WHERE B.key is null;

WHERE NOT EXISTS

(SELECT 'x' FROM B WHERE A.key = B.key);

SELECT A.*

from EDS.TW_BCUST_200409 A LEFT OUTER JOIN KF2.TW_BCUST B ON

A.TM_INTRVL_CD =B.TM_INTRVL_CD and A.CUST_ID =B.CUST_ID and

A.USR_ID =B.USR_ID and A.BCUST_EFF_MO =B.BCUST_EFF_MO

WHERE B.TM_INTRVL_CD is null

SELECT *

from EDS.TW_BCUST_200409 A

where NOT EXISTS

(select 'x'

from KF2.TW_BCUST B

WHERE A.TM_INTRVL_CD =B.TM_INTRVL_CD and A.CUST_ID =B.CUST_ID

and A.USR_ID =B.USR_ID and A.BCUST_EFF_MO =

B.BCUST_EFF_MO)

RETURN                            RETURN

(   1)                            (   1)

|                                 |

BTQ                               BTQ

(   2)                            (   2)

|                                 |

FILTER                            HSJOIN

(   3)                            (   3)

|                              /      \

HSJOIN                     TBSCAN       TBSCAN

(   4)                     (   4)       (   5)

/      \                      |            |

TBSCAN       TBSCAN          Table:           Table:

(   5)       (   6)          EDS              KF2

|            |             TW_BCUST_200409  TW_BCUST

Table:           Table:

EDS              KF2

TW_BCUST_200409  TW_BCUST

• 0

开心

• 0

板砖

• 0

感动

• 0

有用

• 0

疑问

• 0

难过

• 0

无聊

• 0

震惊