CODEWITHSUNDEEP
sqlouter-join
royskatt
royskatt

Reputation: 1210

Oracle - MINUS-operator with different results than OUTER JOIN

In Oracle the following MINUS SQL statement returns results, while the allegedly equivalent OUTER JOIN statement doesn't return any.

Results:

SELECT
  /*+parallel (8)*/
  pd.item_id
FROM MY_TABLE@DB_LINK_PROD_ENV
WHERE pd.valid_to='09.09.9999'
MINUS
SELECT
  /*+parallel (8)*/
  it.item_id
FROM MY_TABLE@DB_LINK_TEST_ENV
WHERE it.valid_to='09.09.9999' ;

No results:

SELECT
  /*+parallel (8)*/
  pd.item_id,
  it.item_id
FROM MY_TABLE@DB_LINK_PROD_ENV
LEFT OUTER JOIN MY_TABLE@DB_LINK_TEST_ENV
ON pd.item_id     = it.item_id
WHERE it.valid_to ='09.09.9999'
AND pd.valid_to   ='09.09.9999'
AND it.item_id   IS NULL;

Without knowing the data, what could be the reason?

Upvotes: 0

Views: 207

Answers (1)

Keyur Panchal
Keyur Panchal

Reputation: 1402

In first query it is MINUS. Means it will show all item_id present in DB_LINK_PROD_ENV having valid_to='09.09.9999' but not present in DB_LINK_TEST_ENV having valid_to='09.09.9999'.
In second one it is LEFT JOIN with AND condition.

it.valid_to ='09.09.9999'
AND pd.valid_to   ='09.09.9999'


So it is possible that there are records in DB_LINK_PROD_ENV having valid_to='09.09.9999' but NO any record in DB_LINK_TEST_ENV with valid_to='09.09.9999'.
So when you will perform MINUS in first query, it will show record present in DB_LINK_PROD_ENV. But in second query AND condition will fail to give you any record.

Upvotes: 1

Related Questions