Results 1 to 6 of 6

Thread: SQL outer join

  1. #1
    Join Date
    Sep 2003
    Posts
    7

    Unanswered: SQL outer join

    TABLE A
    1, a
    2, b
    3, c

    TABLE B
    1, a
    2, b

    I am trying to select rows not in Table B but in table A

    I tried to do the following
    select A.*
    from A,B
    where A.key *= b.key
    and B.key is NULL

    Apprently it does not work Anyone can help ??

    Thanks

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    I think the outer join operator would go on the other side, e.g.
    where a.key =* b.key

    But you can do this with a NOT EXISTS as well:
    Code:
    SELECT *
      FROM a
     WHERE NOT EXISTS( SELECT *
     		     FROM b
     		    WHERE b.key = a.key )
    Thanks,

    Matt

  3. #3
    Join Date
    Dec 2002
    Posts
    104

    Re: SQL outer join

    Hi,

    select A.*
    from A,B
    where A.key !=B.key

    i guess this shud work.....

    --Pooja
    Originally posted by tiggerwave
    TABLE A
    1, a
    2, b
    3, c

    TABLE B
    1, a
    2, b

    I am trying to select rows not in Table B but in table A

    I tried to do the following
    select A.*
    from A,B
    where A.key *= b.key
    and B.key is NULL

    Apprently it does not work Anyone can help ??

    Thanks

  4. #4
    Join Date
    Sep 2003
    Posts
    7

    Cool

    Thanks for your reply.

    I was wondering if there is way to do this without a sub query.

    CREATE TABLE #a(
    number int not null)

    insert into #a values (1)
    insert into #a values (2)
    insert into #a values (3)

    CREATE TABLE #b(
    number int not null)
    insert into #b values (1)
    insert into #b values (2)
    -----------------------------------------

    select * from #a,#b
    where #a.number *= #b.number

    OUTPUT shoue be:
    number number
    1 1
    2 2
    3 NULL

    Clearly, I only want the third row. So if I append "AND #b.number is NULL" , it should work.BUT not. If I put the * on the other side of the join, it would be the same as an inner join.

    Any help?

    Thanks

    Steve

  5. #5
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Sorry about the * thing, I'm thinking of Oracle's (+) syntax.

    Can you use the ANSI outer join syntax?

    The problem with 'where b.number is null' is that it is being applied to the b relation and not the result set.

    Using the ANSI SQL JOIN syntax you can specify the result set like so:
    Code:
    SELECT *
      FROM a
      LEFT OUTER JOIN b
        ON a.NUMBER = b.NUMBER
     WHERE b.NUMBER IS NULL
    Thanks,

    Matt

  6. #6
    Join Date
    Sep 2003
    Posts
    7

    Thank you

    Thanks for your help.
    Dong

Posting Permissions

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