OraFAQ Forum: SQL & PL/SQL » Outer Joins & Not Null check

Home » SQL & PL/SQL » SQL & PL/SQL » Outer Joins & Not Null check (Oracle 11g)

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

Outer Joins & Not Null check [message #573137]

Fri, 21 December 2012 11:38

arung_in
Messages: 1
Registered: December 2012

Junior Member

Have got two sample tables t1 & t2 with the following structure

create table t1
(type varchar2(10),
num number) ;

insert into t1 values ( 'A', 1);
insert into t1 values ( 'B', 2);
insert into t1 values ( 'C', 3);
insert into t1 values ( 'D', 4);
insert into t1 values ( 'E', 5);
insert into t1 values ( 'F', 6);
insert into t1 values ( 'G', 7);
insert into t1 values ( 'H', 8);
insert into t1 values ( 'I', 9);
insert into t1 values ( 'J', 10);
insert into t1 values ( 'K', 11);
insert into t1 values ( 'L', 12);
insert into t1 values ( 'M', 13);
insert into t1 values ( 'N', 14);
insert into t1 values ( 'O', 15);

create table t2
(num number
ref number
reg varchar2(10))

insert into t2 values (1, 100, 'X');
insert into t2 values (2, 200, 'Y');
insert into t2 values (3, 300, 'Z');
insert into t2 values (4, null, 'X');
insert into t2 values (5, 500, 'X');
insert into t2 values (6, 600, 'X');
insert into t2 values (7, 700, 'Y');
insert into t2 values (8, 800, 'P');
insert into t2 values (9, 900, null);
insert into t2 values (10, 1000, 'Q');

Basically require records from the main table t1 which would have more records of which some are related to table t2. However, if the corresponding records in t2 do not have value for ref (null) and reg is not a value in a list of values these records are to be ignored.

select t1.*
from t1,t2
where t1.num=t2.num(+)
and t2.ref is not null
and nvl(t2.reg, '-') not in ('Y', 'Z')

Expected values

type num
---------- -----
A 1
E 5
F 6
H 8
I 9
J 10
K 11
L 12
M 13
N 14
O 15

However, I am not getting any rows as output. Am I missing anything here ?

Alternatively would putting t2 as a subquery with the t2 conditions (not tried) work

select t1.*
from t1, (select * from t2 where and t2.ref is not null and nvl(t2.reg, '-') not in ('Y', 'Z')) t
where t1.num = t.num (+)

Even if the alternative works, I am curious to why the original is not working and would like some help on clarifying it.

Report message to a moderator

Re: Outer Joins & Not Null check [message #573138 is a reply to message #573137]

Fri, 21 December 2012 12:03

Michel Cadot
Messages: 54193
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.

Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Also post a tested and valid test case:

SQL> create table t2
  2  (num number
  3  ref number
  4  reg varchar2(10))
  5  
SQL> /
ref number
*
ERROR at line 3:
ORA-00907: missing right parenthesis

With an outer join you cannot have less row than in the outer table.
Actually what you want is not an ouoter join but, indeed, a subquery.

Note that if in one condition your remove the "(+)" sign on t2 column then it is an inner join.

Regards
Michel

Report message to a moderator

Re: Outer Joins & Not Null check [message #573139 is a reply to message #573137]

Fri, 21 December 2012 12:33

Solomon Yakobson
Messages: 1398
Registered: January 2010

Senior Member

Oracle native outer joins are very limited comparing to ANSI outer joins:

select  t1.*
  from      t1
        left join
            t2
          on (
                  t1.num = t2.num
             )
  where not (t2.num is not null and t2.ref is null)
    and not (t2.num is not null and nvl(t2.reg,'-') in ('Y','Z'))
  order by t1.type
/

TYPE              NUM
---------- ----------
A                   1
E                   5
F                   6
H                   8
I                   9
J                  10
K                  11
L                  12
M                  13
N                  14
O                  15

11 rows selected.

SQL>

But even then it is not quite readable. So it is better to use something like:

 select  t1.*
   from  t1,
         t2
   where t1.num = t2.num
     and t2.ref is not null
     and nvl(t2.reg,'-') not in ('Y','Z')
union all
 select  t1.*
   from  t1
   where t1.num not in (
                        select  t2.num
                          from  t2
                       )
/

TYPE              NUM
---------- ----------
A                   1
E                   5
F                   6
H                   8
I                   9
J                  10
O                  15
L                  12
N                  14
M                  13
K                  11

11 rows selected.

SQL>

SY.

[Updated on: Fri, 21 December 2012 12:36]

Report message to a moderator

Previous Topic:	FirstName LastName Swap
Next Topic:	Query with horizontal running totals

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 22 11:11:28 CDT 2013

Total time taken to generate the page: 0.06936 seconds