Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010
    Posts
    8

    Exclamation Unanswered: check records that does not exists in any of the two tables.

    Hi,

    I would just like to know what sql statement should I use to check if records Table A does not exists in any of the tables , Table B and Table C.
    My idea is to use this

    Select books
    from table a
    where a.books not exists = ((select b.books
    from table b
    where b.books = a.books)
    or (select c.books
    from table c
    where c.books = a.books))

    Thanks,

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    It depends ...
    if tableB and tableC are small, try:

    SELECT books
    FROM tableA a
    WHERE a.books NOT IN (SELECT books FROM tableB)
    OR a.books NOT IN (SELECT books FROM tableC)


    if tableB and tableC are medium or large, try:
    SELECT books
    FROM tableA a
    WHERE NOT EXISTS (SELECT * FROM tableB B WHERE a.books = b.books)
    OR NOT EXISTS (SELECT * FROM tableC C WHERE a.books = c.books)


    you might also use a JOIN:

    SELECT a.books
    FROM tableA a
    LEFT JOIN tableb B
    ON a.books = b.books
    LEFT JOIN tablec C
    ON a.books = c.books
    WHERE b.books IS NULL OR c.books IS NULL
    Last edited by umayer; 09-22-11 at 16:18.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Don't forget the UNION ALL option.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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