Oracle Semi-Joins and Anti-Joins

Using EXISTS and IN.

Example 1:

SELECT   D.deptno, D.dname
        FROM     dept D, emp E
        WHERE    E.deptno = D.deptno
        ORDER BY D.deptno;

Change to:

SELECT   D.deptno, D.dname
        FROM     dept D
        WHERE    EXISTS
                 (
                 SELECT 1
                 FROM   emp E
                 WHERE  E.deptno = D.deptno
                 )
        ORDER BY D.deptno; 

Example 2:

SELECT   D1.deptno, D1.dname
        FROM     dept D1
        MINUS
        SELECT   D2.deptno, D2.dname
        FROM     dept D2, emp E2
        WHERE    D2.deptno = E2.deptno
        ORDER BY 1;

Change to: 

SELECT   D.deptno, D.dname
        FROM     dept D
        WHERE    NOT EXISTS
                 (
                 SELECT 1
                 FROM   emp E
                 WHERE  E.deptno = D.deptno
                 )
        ORDER BY D.deptno;

References:

http://www.dbspecialists.com/files/presentations/semijoins.html