Oracle in VS Exists Difference

Using IN or JOIN or EXISTS CLAUSE

Your IN clause query is perfect.

 SELECT * FROM TABLE1
  WHERE id IN
   (
    SELECT id FROM TABLE2
    WHERE my_date = (Select max(my_date) from TABLE2)
   ) 
 ORDER BY id;

If you want an EXISTS

 WITH MY_TABLE2 AS
 (
    SELECT id FROM TABLE2
    WHERE my_date = (Select max(my_date) from TABLE2)
 )
 SELECT * FROM TABLE1 T1
 WHERE EXISTS
 ( 
    SELECT 'X' FROM MY_TABLE2  T2
    WHERE T1.id = T2.id
 )
 ORDER BY id;

If every id in table1 has only one id in table2, you can opt for JOIN,

Because EXISTS and IN would supress the duplicate ids.

 SELECT * FROM TABLE1 T1
 INNER JOIN ( 
               SELECT id FROM TABLE2
               WHERE my_date = (Select max(my_date) from TABLE2)
            ) T2
    ON (T1.id = T2.id)
 ORDER BY id;

Intersect and exists performance Oracle11g

A quick test would suggest the EXISTS option...

SELECT STUDENT_ID FROM Student_Master INTERSECT SELECT STUDENT_ID FROM
Student_Status

Plan hash value: 416197223

--------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                    |       |       |     6 (100)|          |
|   1 |  INTERSECTION         |                  |       |       |              |            |
|   2 |   SORT UNIQUE         |                  |     3 |    36 |     3  (34)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| STUDENT_MASTER |     3 |    36 |     2   (0)| 00:00:01 |
|   4 |   SORT UNIQUE         |                  |     3 |    36 |     3  (34)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| STUDENT_STATUS |     3 |    36 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

-

SELECT STUDENT_ID FROM Student_Master M WHERE EXISTS (SELECT STUDENT_ID
FROM Student_Status S WHERE M.STUDENT_ID=S.STUDENT_ID)

Plan hash value: 361045672

-------------------------------------------------------------------------------------
| Id  | Operation      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |       |       |     4 (100)|      |
|*  1 |  HASH JOIN SEMI    |            |     3 |    72 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| STUDENT_MASTER |     3 |    36 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| STUDENT_STATUS |     3 |    36 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Related Topics