Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    7

    Unanswered: about sql outer join

    SELECT _

    FROM EMP

    WHERE DEPT_NO NOT IN (SELECT DEPT_NO

    FROM DEPT

    WHERE DEPT_CAT=A);

    ---------------------------------------------------------------------------------

    SELECT _.

    FROM EMP A,DEPT B

    WHERE A.DEPT_NO = B.DEPT(+)

    AND B.DEPT_NO IS NULL

    AND B.DEPT_CAT(+) = A

    i----------------------------------------------------------------------------------

    i read two above statements from internet.
    it's said the results of them are the- same.
    i don't know why.


    any reply will be appreciated !!

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Well apart from the fact it should be WHERE A.DEPT_NO = B.DEPT_NO(+), what the outer join is doing is joining all records in A to there corresponding records in B but if no record exist in B for that dept_no then it joins to a blank b record (all nulls). So since there is the condition B.DEPT_NO IS NULL the result set will only show those records in A which have no record in B (as b.dept_no is null).

    By the way it is generally much better to go the join route rather than using the subquery in terms of performance.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •