Results 1 to 7 of 7

Thread: Joins in Oracle

  1. #1
    Join Date
    Feb 2004
    Location
    Chennai, INDIA
    Posts
    3

    Post Unanswered: Joins in Oracle

    How to get un-matching rows from two tables. Using equal to (=) operator we can join two tables and can get the matching rows but without using not equal to (!=) operator how to get un-matching rows alone from two tables.

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    You can use NOT EXISTS. The following is an non-optimized sample code
    which explains the one you want

    PHP Code:

           SELECT DISTINCT a
    .col1b.col1
           FROM a
    b
           WHERE NOT EXISTS 
    (SELECT *
                                           
    FROM ab
                                           WHERE a
    .col2 b.col2); 
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Feb 2004
    Location
    Chennai, INDIA
    Posts
    3

    No it doesn't give the result

    Hi,
    It is not giving the result it says no rows selected. I am having sufficient records from both emp and dept tables.





    Originally posted by Hings
    You can use NOT EXISTS. The following is an non-optimized sample code
    which explains the one you want

    PHP Code:

           SELECT DISTINCT a
    .col1b.col1
           FROM a
    b
           WHERE NOT EXISTS 
    (SELECT *
                                           
    FROM ab
                                           WHERE a
    .col2 b.col2); 

  4. #4
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Can you please give me the SQL Statement you have used?
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  5. #5
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Did you mean the one like below?

    PHP Code:
             SELECT a.col1a.col2b.col3b.col4
             FROM a
    b
             WHERE a
    .col1 b.col1 AND
             
    NOT EXISTS (SELECT *
                                 
    FROM ab
                                 WHERE a
    .clo1 b.col1 AND
                                      
    a.col2 b.col2); 
    The Equivalent SQl with != opetaor is as below.

    PHP Code:

             SELECT a
    .col1a.col2b.col3b.col4
             FROM a
    b
             WHERE a
    .col1 b.col1 AND
                   
    a.col2 != b.col2
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm not sure I understood well, but ... let me try:

    Suppose you have two tables, with such data:
    PHP Code:
    SELECT FROM first;
    VAL
    ---
      
    1
      2
      3
      4

    SELECT 
    FROM second;
    VAL
    ---
      
    0
      2
      3
      5 
    We could put it down like this:
    PHP Code:
    FIRST      1 2 3 4
    SECOND   0   2 3   5 
    You want to select, as a result, unmatching values, i.e. 0, 1, 4, 5. Right?

    First thing I can think of would make a query like this one:
    PHP Code:
    (SELECT p.val FROM first p
     MINUS
     SELECT d
    .val FROM second d
    )
    UNION ALL
    (SELECT d.val FROM second d
     MINUS
     SELECT p
    .val FROM first p
    );

           
    VAL
    ----------
             
    1
             4
             0
             5 
    However, I like your query(es) better than mine, Hings Even though both of them might not be what msazeezjuly needs, hahahhahha!

  7. #7
    Join Date
    Feb 2004
    Posts
    11

    Re: Joins in Oracle

    Originally posted by msazeezjuly
    How to get un-matching rows from two tables. Using equal to (=) operator we can join two tables and can get the matching rows but without using not equal to (!=) operator how to get un-matching rows alone from two tables.

    Hi

    If you want all rows i hope the below query will work.

    select nvl(L.id,R.id) id,L.name name.R.loc location from L full outer join R on L.id=R.id

    the result may be look like this

    ID NAME LOCATION
    ---------- ---------- ----------
    1 aaa 1111
    2 bbb 22222
    3 ccc
    4 4444

    I hope this will work

    Thanks
    learning Oracle

Posting Permissions

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