Results 1 to 4 of 4

Thread: Oracle joins

  1. #1
    Join Date
    Jun 2005
    Posts
    85

    Unanswered: Oracle joins

    Hi,

    Is there any difference in the below queries :

    SELECT * from test left outer join test1 on test.col1 = test1.col1 and test.col1 = 2;

    and

    SELECT * from test left outer join test1 on test.col1 = test1.col1 where test.col1 = 2;

    Please advise,

    Thanks,
    MiraJ

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    No, there's no difference. Result will be the same

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Actually, they are different.

    The first says: "outer join to test1 rows that have test1.col1 and test.col1 = 2. Show test row whether a test1 row matches or not".

    The second says: "outer join to test1 rows that have test1.col1. Then filter to show only those test rows where a matching test was found that had test.col1 = 2"

    The result is different:
    Code:
    SQL> create table test (col1 number);
    
    Table created.
    
    SQL> create table test1 (col1 number, col2 number);
    
    Table created.
    
    SQL> insert into test values (1);
    
    1 row created.
    
    SQL> insert into test1 values (1,1);
    
    1 row created.
    
    SQL> SELECT * from test left outer join test1 on test.col1 = test1.col1 and test.col1 = 2;
    
          COL1       COL1       COL2
    ---------- ---------- ----------
             1
    
    SQL> SELECT * from test left outer join test1 on test.col1 = test1.col1 where test.col1 = 2;
    
    no rows selected

  4. #4
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    sorry,
    andrewst is right. I didn't realise it's OUTER JOIN

Posting Permissions

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