Finding missing number between tables in SQL

Question

Hello members,

I'm working on the following 2 tables on Oracle 10g. I'm attempting a rather simple task, but can't get my query right, perhaps due to my lack of understanding of the basics. I want to query out the account_no from TEMP which is not present in BMF.

Two Tables: Table 1: BMF: 1372 rows

account_no  |  trans_amount   | tracking_id

8149817     | 8100       |     72422912

8197743     | 9100       |  72422913

7165129     | 8100       |  72422914

8625861     | 8100       |  72422915

8463378     | 2100       |  72422916

8213330     | 3100       |  72422917

Table 2: temp : 1373 rows -- There is only ONE account_no in TEMP that's missing from BMF

account_no

8149817

8197743

7165129

8625861

8463378

8213330

84633

48

Expected result: 8463348 -- As this number is not present in the BMF table.

My Query:

 select a1.account_no from TEMP a1, bmf a2 
 where a2.tracking_id between 72422912 and 72424283
 and a1.account_no != a2.account_no

Any pointers, with a correct query will be helpful

Regards, novice

Answer 1

SELECT account_no 
FROM TEMP
WHERE acount_no NOT IN (SELECT account_no FROM BMF)

Answer 2

Try

 select a1.account_no from TEMP a1 left join bmf a2 
 where a2.tracking_id between 72422912 and 72424283
 and a2.account_no = NULL

use a left join and then only take the entries that don't have a corresponding entry

Answer 3

Your join syntax is discouraged by Oracle for outer joins.

You can do a left outer join and exclude the unjoined columns; this should be the most efficient approach. Something like this:

SELECT a1.account_no FROM TEMP a1
 LEFT JOIN bmf a2 ON a1.account_no = a2.account_no
 WHERE (a2.tracking_id between 72422912 and 72424283)
 AND a2.account_no IS NULL

Answer 4

SELECT account_no FROM temp 
WHERE NOT EXISTS (SELECT account_no FROM bmf 
                  WHERE bmf.account_no = temp.account_no)

This will have same execution plan as SQL in other answers here, but it states intention more clearly (at least to me).

Answer 5

You want all column values, which are in one set of data (temp) but not in another (bmf)? That is what the "Minus" operator does.

select account_no from TEMP
minus
select account_no from bmf

EDIT: added doc link