EXISTS, IN, NOT EXISTS, NOT IN

SELECT HEADER_ID, md_change_number, md_update_date FROM
JEL_SO_HEADERS_ALL jel
WHERE NOT EXISTS ( SELECT 1 FROM JELH_SO_HEADERS_ALL jelh WHERE jelh.HEADER_ID =
jel.HEADER_ID AND jelh.md_change_number = jel.md_change_number )

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=61)
NESTED LOOPS (ANTI) (Cost=2 Card=1 Bytes=61)
TABLE ACCESS (FULL) OF JEL_SO_HEADERS_ALL (TABLE) (Cost=2 Card=1 Bytes=35)
INDEX (UNIQUE SCAN) OF JELH_SO_HEADERS_ALL_PK (INDEX (UNIQUE)) (Cost=0 Card=4 Bytes=104)


SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=35)
FILTER
TABLE ACCESS (FULL) OF JEL_SO_HEADERS_ALL (TABLE) (Cost=2 Card=1 Bytes=35)
TABLE ACCESS (FULL) OF JELH_SO_HEADERS_ALL (TABLE) (Cost=2 Card=16384 Bytes=425984)


SELECT HEADER_ID, md_change_number, md_update_date FROM
JEL_SO_HEADERS_ALL jel
WHERE (HEADER_ID, md_change_number) IN ( SELECT HEADER_ID, md_change_number FROM JELH_SO_HEADERS_ALL jelh)

SELECT HEADER_ID, md_change_number, md_update_date FROM
JEL_SO_HEADERS_ALL jel
WHERE EXISTS ( SELECT 1 FROM JELH_SO_HEADERS_ALL jelh WHERE jelh.HEADER_ID =
jel.HEADER_ID AND jelh.md_change_number = jel.md_change_number )


SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=61)
NESTED LOOPS (SEMI) (Cost=2 Card=1 Bytes=61)
TABLE ACCESS (FULL) OF JEL_SO_HEADERS_ALL (TABLE) (Cost=2 Card=1 Bytes=35)
INDEX (UNIQUE SCAN) OF JELH_SO_HEADERS_ALL_PK (INDEX (UNIQUE)) (Cost=0 Card=4 Bytes=104)