Home » SQL & PL/SQL » SQL & PL/SQL » Outer Joins & Not Null check (Oracle 11g)
Outer Joins & Not Null check [message #573137] Fri, 21 December 2012 11:38 Go to next message
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.

Re: Outer Joins & Not Null check [message #573138 is a reply to message #573137] Fri, 21 December 2012 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 65149
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))
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.

Re: Outer Joins & Not Null check [message #573139 is a reply to message #573137] Fri, 21 December 2012 12:33 Go to previous message
Solomon Yakobson
Messages: 2627
Registered: January 2010
Location: Connecticut, USA
Senior Member
Oracle native outer joins are very limited comparing to ANSI outer joins:

select  t1.*
  from      t1
        left join
          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.


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

 select  t1.*
   from  t1,
   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.



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

Report message to a moderator

Previous Topic: full outer join performance
Next Topic: Oracle database to Mysql Conversion
Goto Forum:

Current Time: Mon Aug 21 14:27:54 CDT 2017

Total time taken to generate the page: 0.07824 seconds