Select several methods for writing data that does not exist in another table

SELECT the data that does not exist in another table. Let's look at the following three methods: Write 1: SELECT... from a where. key not in (SELECT key from B); Statement 2: SELECT... from a left join B ON. key = B. key where B. key is null; Statement 3: SELECT... from a where not exists (SELECT 'x' from B WHERE. key = B. key); Method 1 adopts the not in method. Unfortunately, DB2 usually uses TBSCAN (Table scan) for not in, which is a very inefficient method. The best writing method is the third writing method. If B. key has an index, it can complete the query without the data in fetch B table. The second method filters out the is null judgment of External table B, which is less efficient. Note: In fact, under the role of the DB2 optimizer, the second write method has little to do with the Access Solution of the third write method, but the second write method has one more filter operation than the third write method. We recommend that you use 3rd writing methods, and you do not need to modify the code that has already used 2nd writing methods, because the efficiency is similar to that of the 3rd writing methods. Example: select. * from EDS. TW_BCUST_200409 a left outer join KF2.TW _ bcust B ON. TM_INTRVL_CD = B. TM_INTRVL_CD and. CUST_ID = B. CUST_ID and. USR_ID = B. USR_ID and. 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. TM_INTRVL_CD = B. TM_INTRVL_CD and. CUST_ID = B. CUST_ID and. USR_ID = B. USR_ID and. BCUST_EFF_MO = B. BCUST_EFF_MO): RETURN (1) (1) | BTQ (2) (2) | filter hsjoin (3) (3) | // hsjoin tbscan (4) (4) (5) // | TBSCAN Table: (5) (6) EDS KF2 | TW_BCUST_200409 TW_BCUST Table: table: EDS KF2 TW_BCUST_200409 TW_BCUST