Re: outer joins and criteria

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 14 Oct 1999 16:25:50 -0400
Message-ID: <sjgGOKRCaqUBeQNKuKKc5Uqf5P0e@4ax.com>

A copy of this was sent to "Phil R Lawrence" <prlawrence_at_lehigh.edu> (if that email address didn't require changing) On Thu, 14 Oct 1999 14:40:05 -0400, you wrote:

>EXAMPLE TABLES:
> Table NAME fields:
> NAME_PERSON_ID
> NAME_NAME
>
> Table ADDR fields:
> ADDR_PERSON_ID
> ADDR_TYPE
> ADDR_STREET_LINE_1
>
> Assume there are two ADDR_TYPEs (A and B)
> Assume only one address of each type can exist
>
>GOAL:
> I want to return all names. In addition, I want to return an address of
>type A, if it exists for the name, or else NULLs.
>

tkyte_at_8.0> create table name ( name_person_id int, name_name varchar2(25) ); Table created.

tkyte_at_8.0> create table addr ( addr_person_id int, addr_type char(1), addr_street_line_1 varchar2(25) );
Table created.

tkyte_at_8.0> insert into name values ( 1, 'a' );
tkyte_at_8.0> insert into name values ( 2, 'b' );
tkyte_at_8.0> insert into name values ( 3, 'c' );
tkyte_at_8.0> insert into addr values ( 1, 'A', 'addr a' );
tkyte_at_8.0> insert into addr values ( 2, 'B', 'addr b' );

SOLUTION 1: this works by outer joining name not to ADDR but some view of ADDR that only gets the ones you want.

tkyte_at_8.0> select name_name, addr_type, addr_street_line_1   2 from name, ( select * from addr where addr_type = 'A' )   3 where name_person_id = addr_person_id (+)   4 /

NAME_NAME                 A ADDR_STREET_LINE_1
------------------------- - -------------------------
a                         A addr a

b
c

SOLUTION 2: this works by getting a function to get the address you want. This will work well if the above runs too slow (making that view on addr BEFORE doing the outer join) AND you have a 'few' names you are getting back (if you call plsql from SQL a couple of times -- ok. If you are going to call plsql from SQL 1,000,000 times -- not so good)

tkyte_at_8.0> create or replace function get_addr( person_id in int ) return varchar2
  2 as
  3 l_string addr.addr_street_line_1%type;   4 begin

  5          select addr_street_line_1 into l_string
  6            from addr
  7           where addr_person_id = person_id
  8             and addr_type = 'A';
  9  
  9          return l_string;
 10  exception
 11          when no_data_found then
 12                  return NULL;

 13 end;
 14 /

Function created.

tkyte_at_8.0>
tkyte_at_8.0> select name_name, get_addr(name_person_id) addr_street_line_1   2 from name
  3 /

NAME_NAME                 ADDR_STREET_LINE_1
------------------------- --------------------
a                         addr a

b
c

SOLUTION 3: This works best if you can do it (fastest) but as you said your where clause might be too hard to put into a decode....

tkyte_at_8.0> select name_name, addr_type, decode( addr_type, 'A', addr_street_line_1, NULL )
  2 from name, addr
  3 where name_person_id = addr_person_id (+)   4 /

NAME_NAME                 A DECODE(ADDR_TYPE,'A',ADDR
------------------------- - -------------------------
a                         A addr a
b                         B

c

>ATTEMPTED SOLUTION:
> SELECT NAME_NAME, ADDR_STREET_LINE_1
> FROM ADDR, NAME
> WHERE ADDR_PERSON_ID(+) = NAME_PERSON_ID
> AND
>
> ADDR_TYPE IS NULL
> OR
> ADDR_TYPE = 'A'
> )
>
>PROBLEM:
> While the outer join includes those names with no address whatsoever, it
>still excludes names that have only addresses of type 'B'.
>
>ATTEMPTED SOLUTION:
> SELECT NAME_NAME,
> DECODE( ADDR_TYPE,
> 'A', ADDR_STREET_LINE_1,
> NULL
> ) ADDR_STREET_LINE_1
> FROM ADDR, NAME
> WHERE ADDR_PERSON_ID(+) = NAME_PERSON_ID
> AND
>
> ADDR_TYPE IS NULL
> OR
> ADDR_TYPE = 'A'
> OR NOT EXISTS
> (
> SELECT *
> FROM ADDR B
> WHERE B.ADDR_PERSON_ID = NAME_PERSON_ID
> AND B.ADDR_TYPE = 'A'
> )
> )
>
>PROBLEM:
> However, this strategy of mirroring the WHERE clause criteria in a
>SELECT DECODE statement is impractical for me as the real WHERE clause
>criteria I am dealing with is *exstensive*.
>
>SUMMARY QUESTION:
> Is there any other way to achieve my goal?
>
>TIA,
>Phil R Lawrence
>prlawrence_at_lehigh.edu
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Oct 14 1999 - 15:25:50 CDT