OraFAQ Forum: SQL & PL/SQL » Replace not in/not exists with outer join

Home » SQL & PL/SQL » SQL & PL/SQL » Replace not in/not exists with outer join (Oracle 11)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Replace not in/not exists with outer join [message #473621]

Mon, 30 August 2010 12:50

prashas_d
Messages: 66
Registered: February 2007

Member

Hi All,

I have a basic requirement to report rows from one table where the second table do not have the same record (basically same key).

I understand that we could achieve this using not in or not exists clause. But I think using outer join probably be the simplest one. However, I could not achieve what I actually wanted using outer join Sad

Test Case:

create table tab_1 (a number(1), b varchar2(20));
insert into tab_1 values (1,'one');
insert into tab_1 values (2,'two');
insert into tab_1 values (3,'three');

create table tab_2 (a number(1), b varchar2(20));
insert into tab_2 values (1,'one');
insert into tab_2 values (2,'two');
commit;

Now, I framed the query as

select y.a ya, x.a xa, x.b xb
from tab_1 x, tab_2 y
where x.a = y.a (+);
/

YA   XA   XB
---- ---- ----
1    1    one
2    2    two
     3    three

In this case, as there is no corresponding record in second table, the value of YA is showing as null as shown above.

Hence, I changed the query as

select y.a ya, x.a xa, x.b xb
from tab_1 x, tab_2 y
where x.a = y.a (+)
and y.a(+) is null;
/
YA   XA   XB
---- ---- ----
     1    one
     2    two
     3    three

I dont understand why it is behaving in that way. I am rather expecting the output to come as:

YA   XA   XB
---- ---- ----
     3    three

Could someone let me know where I am doing wrong?

Thanks in advance,
prashas_d.

Report message to a moderator

Re: Replace not in/not exists with outer join [message #473623 is a reply to message #473621]

Mon, 30 August 2010 13:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> select a, b
  2  from tab_1
  3  minus
  4  select a, b
  5  from tab_2;

	 A B
---------- --------------------
	 3 three

Report message to a moderator

Re: Replace not in/not exists with outer join [message #473624 is a reply to message #473621]

Mon, 30 August 2010 13:05

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> l
  1  select y.a ya, x.a xa, x.b xb
  2  from tab_1 x, tab_2 y
  3  where x.a = y.a (+)
  4* and y.a(+) is not null
SQL> /
        YA         XA XB
---------- ---------- --------------------
         1          1 one
         2          2 two
                    3 three

SQL> l
  1  select y.a ya, x.a xa, x.b xb
  2  from tab_1 x, tab_2 y
  3  where x.a = y.a (+)
  4* and y.a(+) = 9999
SQL> /
        YA         XA XB
---------- ---------- --------------------
                    3 three
                    1 one
                    2 two
SQL> l
  1  select y.a ya, x.a xa, x.b xb
  2  from tab_1 x, tab_2 y
  3  where x.a = y.a (+)
  4* and y.a is null
SQL> /
        YA         XA XB
---------- ---------- --------------------
                    3 three

If you put an outer condition on y.a then you include this one in the outer join condition and so, as it is a constant, it means null if the condition is not satisfy, and the value itself if it is.

Maybe the difference is clearer with thE ANSI syntax:

SQL> select y.a ya, x.a xa, x.b xb
  2  from tab_1 x left outer join tab_2 y on (x.a = y.a)
  3  where y.a is null
  4  /
        YA         XA XB
---------- ---------- --------------------
                    3 three

SQL> select y.a ya, x.a xa, x.b xb
  2  from tab_1 x left outer join tab_2 y 
  3  on (x.a = y.a and y.a is null)
  4  /
        YA         XA XB
---------- ---------- --------------------
                    3 three
                    1 one
                    2 two

Regards
Michel

[Updated on: Mon, 30 August 2010 13:06]

Report message to a moderator

Re: Replace not in/not exists with outer join [message #473633 is a reply to message #473624]

Mon, 30 August 2010 15:03

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

SQL> select y.a ya, x.a xa, x.b xb
  2  from tab_1 x, tab_2 y
  3  where x.a = y.a (+)
  4  and y.a(+) is null
  5  /

        YA         XA XB
---------- ---------- --------------------
                    3 three
                    1 one
                    2 two

SQL> select y.a ya, x.a xa, x.b xb
  2  from tab_1 x, tab_2 y
  3  where x.a = y.a (+)
  4  and y.a is null
  5  /

        YA         XA XB
---------- ---------- --------------------
                    3 three

SQL>

SY.

[Updated on: Mon, 30 August 2010 15:05]

Report message to a moderator