Re: Outer Join with an extra condition

From: <fitzjarrell_at_cox.net>
Date: Thu, 5 Jun 2008 11:22:10 -0700 (PDT)
Message-ID: <b94ec710-85f8-4537-9f18-309eadbeef34@b1g2000hsg.googlegroups.com>


On Jun 5, 5:22 am, "dn.p..._at_gmail.com" <dn.p..._at_gmail.com> wrote:
> I am taking a left outer join to which I would like to add a
> condition.
>
> create table tmp_nani_student(stid number) ;
>
> insert into tmp_nani_student values(1) ;
> insert into tmp_nani_student values(2) ;
> insert into tmp_nani_student values(3) ;
> insert into tmp_nani_student values(4) ;
>
> create table tmp_nani_marks(stid number, marks number, grade number) ;
>
> insert into tmp_nani_marks values(1, 51, 3) ;
> insert into tmp_nani_marks values(2, 71, 2) ;
> insert into tmp_nani_marks values(4, 81, 1) ;
> insert into tmp_nani_marks values(5, 55, 3) ;
> commit ;
>
> Then I run :
> select a.stid, b.marks, b.grade
> from tmp_nani_student a, tmp_nani_marks b
> where   a.stid = b.stid (+)   and  (b.grade < 3 or not exists (select
> c.stid from             tmp_nani_marks c where a.stid = c.stid) )
>
> So far so good.
>
> But then I add one more row to the second table:
>     insert into tmp_nani_marks values(2, 17, null) ;
>
> Now I want to run a query which would exclude this new row where
> grade=null. If the outer join fetches rows with grade=null, that is
> fine. But I don't want to fetch records where grade is explicitly set
> to null.
>
> Run again:
> select a.stid, b.marks, b.grade
> from tmp_nani_student a, tmp_nani_marks b
> where   a.stid = b.stid (+)   and  (b.grade < 3 or not exists (select
> c.stid from             tmp_nani_marks c where a.stid = c.stid) )
>
> But the record (2,17, null) shows up. I don't want it to show up. What
> would be the query for it?
>
> TIA.
I don't observe the same behaviour with your example:

SQL> create table tmp_nani_student(stid number) ;

Table created.

SQL>
SQL>
SQL> insert into tmp_nani_student values(1) ;

1 row created.

SQL> insert into tmp_nani_student values(2) ;

1 row created.

SQL> insert into tmp_nani_student values(3) ;

1 row created.

SQL> insert into tmp_nani_student values(4) ;

1 row created.

SQL>
SQL>
SQL> create table tmp_nani_marks(stid number, marks number, grade
number) ;

Table created.

SQL>
SQL>
SQL> insert into tmp_nani_marks values(1, 51, 3) ;

1 row created.

SQL> insert into tmp_nani_marks values(2, 71, 2) ;

1 row created.

SQL> insert into tmp_nani_marks values(4, 81, 1) ;

1 row created.

SQL> insert into tmp_nani_marks values(5, 55, 3) ;

1 row created.

SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select a.stid, b.marks, b.grade
  2  from tmp_nani_student a, tmp_nani_marks b
  3  where   a.stid = b.stid (+)   and	(b.grade < 3 or not exists
(select
  4  	     c.stid from	     tmp_nani_marks c where a.stid =
c.stid) ) ;

      STID MARKS
GRADE
---------- ----------


         2 71
2

         4 81
1  

3

SQL>
SQL>
SQL> insert into tmp_nani_marks values(2, 17, null) ;

1 row created.

SQL> commit ;

Commit complete.

SQL>
SQL> select a.stid, b.marks, b.grade
  2 from tmp_nani_student a, tmp_nani_marks b   3 where a.stid = b.stid (+) and (b.grade < 3 or not exists (select
  4 c.stid from tmp_nani_marks c where a.stid = c.stid) ) ;

      STID MARKS
GRADE
---------- ----------


         2 71
2

         4 81
1  

3

SQL> Oracle version 10.2.0.3.0. So which version are you using??

David Fitzjarrell Received on Thu Jun 05 2008 - 13:22:10 CDT

Original text of this message