CODEWITHSUNDEEP
sqloracle-databaseoracle11g
PRAJ
PRAJ

Reputation: 11

JOIN instead of NOT EXISTS query

I have two tables,

One table which updates the postings to a Platform (It has all the status) The other one which has only current status posted to ERP system.

I need to report the list of ID which are posted in platformbut not in ERP.

TABLE 1 : PLATFORM_TABLE

    ID  DOC_STATUS
10001   POSTED
10001   AVAIL TO POST
10002   AVAIL TO POST
10003   POSTED
10003   AVAIL TO POST

TABLE 2 : ERP

ID  DOC_STATUS
10001   AVAIL TO POST
10002   AVAIL TO POST
10003   POSTED

EXPECTED OUTPUT : Only 10001 because that is the only id posted in Table 1 but not in Table 2.

ID
10001

I tried using the below query and it works fine but due to performance i need to use only joins . Can you please guide on how to convert this query to join.

SELECT a.ID
  FROM ERP_TABLE a 
 WHERE NOT EXISTS 
  (SELECT ID FROM PLATFORM_TABLE b
    WHERE a.ID = b.ID
      AND a.DOC_STATUS  = 'POSTED');

Upvotes: 1

Views: 41

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220902

I think you got the anti join wrong. Your English phrasing says it:

Only 10001 because that is the only id posted in Table 1 but not in Table 2.

So that translates to "all in table 1 (platform_table) for which there does not exist anything in table 2 (erp_table)"

SELECT a.id
FROM platform_table a
WHERE a.doc_status = 'POSTED'
AND NOT EXISTS (
  SELECT *
  FROM erp_table b
  WHERE b.id = a.id
  AND b.doc_status = 'POSTED'
);

If you have to do this anti join with an actual join (I don't think you have to, Oracle should produce the same anti join execution plan), then you could do it like this:

SELECT a.id
FROM platform_table a
LEFT JOIN erp_table b
  ON b.id = a.id
  AND b.doc_status = 'POSTED'
WHERE a.doc_status = 'POSTED'
AND b.id IS NULL

A third option is to use MINUS

SELECT a.id
FROM platform_table a
WHERE a.doc_status = 'POSTED'
MINUS
SELECT b.id
FROM erp_table b
WHERE b.doc_status = 'POSTED'

Since this is about performance, I'm positive that indexes on (id, doc_status) will be beneficial on both tables.

Upvotes: 3

Related Questions