0

I have two tables 'test1' and 'test2' with the single column 'id'. 'test1' table contains the 5 rows with values like 1,2,3,4,5 and 'test2' table contains the 3 rows with values like 1,2,3 then if i want to get the just 4,5 from 'test1' I am using two below queries

select a.id 
  from test1 a,test2 b 
  where a.id=b.id(+) and b.id is null;

output:4,5

select a.id 
  from test1 a left outer join test2 b 
                on     a.id=b.id 
                   and b.id is null;

output:1,2,3,4,5

Both the queries are similar logically but these are giving different outputs...

Please explain me is there any difference....

Thanks in advance

Arun.Baswapuram
  • 11
  • 2
  • 1
    The `(+)` is deprecated by Oracle. Don't use it any more –  Jul 15 '14 at 14:35
  • The `b.id is null` test has to be in the `WHERE` clause because it's an assertion about whether the join succeeded - so it can't be part of the `JOIN` conditions. – Damien_The_Unbeliever Jul 15 '14 at 14:36
  • if you want 4,5 that is on the two tables, why not use a join ? – Jorge Campos Jul 15 '14 at 14:37
  • @a_horse_with_no_name can you provide a link with this information about `(+)` sign please? – Jorge Campos Jul 15 '14 at 14:38
  • "these are giving different outputs" how are they different? Is one of them correct? – D Stanley Jul 15 '14 at 14:38
  • In the context of your two queries, the difference is that the `(+)` join evaluates the `b.id is null` condition in the context of the filter expression, while ANSI join evaluates the same condition in the context of the join criterion. – Sergey Kalinichenko Jul 15 '14 at 14:39
  • @JorgeCampos See the accepted answer in the duplicate question. It quotes the manual –  Jul 15 '14 at 14:39
  • Change your second query to "where b.id is null" rather than "and b.id is null" to make both queries have the same intent, and hence the same answer – mc110 Jul 15 '14 at 14:39
  • @a_horse_with_no_name It is not deprecated at all. The docs recommends to not use it because of the restrictions that came along. I mean Deprecated as the meaning of the word. I'm just saying that because I have lots of oracle magazine (since 2001) and never saw such warning about deprecation. Thats why I asked for a link. I was very curious. :) but thanks anyway. – Jorge Campos Jul 15 '14 at 14:49

0 Answers0