DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Inner Join

Hybrid View

  1. #1
    Join Date
    Aug 2000
    Posts
    143

    Question

    How do I create a select statement to show values in one table that are not in another table?

  2. #2
    Join Date
    Oct 2000
    Posts
    90
    You could do something like

    select *
    from table1
    where not exists (select 'x' from table2
    where table2.field = table1.field)



  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Just an FYI:

    SELECT
    T1.*
    FROM
    TABLE1 T1,
    TABLE2 T2
    WHERE
    T2.FIELD (+)= T1.FIELD AND
    T2.FIELD IS NULL

    Will generally work faster.

    The final test on T2.FIELD IS NULL needs to be on a field in T2 that is not nullable. The idea is that the outer join is done first. Rows in T1 that are not in T2 will have NULL values in all the T2 fields. You then target just those rows. This query will use a more efficient plan the a NOT EXISTS will.

    Hope this helps,

    - Chris

Posting Permissions

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


Click Here to Expand Forum to Full Width