Re: SQL: views and joins

"Nicklas Karlsson" <nicklas.karlsson_at_ineo dot fi> wrote in message news:<5QIS6.97$3%.10788_at_read2.inet.fi>...
> Hello,
>
> Could someone spare a minute or two to help me out?
>
> If I have two tables like this (ID and DATA are strings)
>
> TABLE A
> ID_A
> DATA_A
>
> TABLE B
> ID_B
> DATA_B
>
> with the data
>
> TABLE A
> ID_A DATA_A
> ------------------------
> '123' 'a-data'
>
> and
>
> TABLE B
> ID_B DATA_B
> ------------------------
> '123' 'b-data'
> '124' 'b-data2'
>
> how should I create a view that would collect this data like this:
>
> VIEW V
> ID DATA_A DATA_B
> '123' 'a-data' 'b-data'
> '123' null 'b-data2'

A fellow named Frank posted the code to answer this but he apparently failed to get it hooked to the thread. What you want is an outer join which basically means "I want every row in A plus those rows in B that match the join condition". In Oracle the outer join is represented by '(+)' which you place on the table that you want to force to return a null row when it does not match the driving table it is being matched against.

select a.*, b.*
from table_a A, table_b B
where a.key = b.key(+)

will result in every row in A being returned (subject to additional where clause conditions). Nulls are returned for the B table columns where there is not matching row in B for the A join column values.

• Mark D Powell --