SQL question
Alex
I've two tables and am trying to apply an OUTER JOIN on multiple fields.
Table A has some rows as:
<Table A>
--- ---
1 a
1 b
2 b
3 a
3 c
Table B is missing some of the rows.
<Table B>
--- ---
1 a
2 b
3 c
Looking for only those rows Table B is missing:
----------
1 b NULL
3 a NULL
The following is not working:
select t1.number, t1.letter, t2.number, t2.letter
from TableA t1, TableB t2
where t1.number = (+)t2.number
and t1.letter = (+)t2.letter
Rene Nyffenegger
First: the (+) is on the right side of the expression,
so your statement should read .... t2.number(+) instead
of (+)ts.number.
Then: number is a reserved word in Oracle and should not be used
for column names, at least not in my opinion.
Here's a working example:
drop table t_a;
drop table t_b;
create table t_a (
u number,
v varchar2(5)
);
create table t_b (
s number,
t varchar2(5)
);
insert into t_a values(1,'a');
insert into t_a values(1,'b');
insert into t_a values(1,'b');
insert into t_a values(3,'a');
insert into t_a values(3,'c');
insert into t_b values(1,'a');
insert into t_b values(2,'b');
insert into t_b values(3,'c');
select
t_a.u,
t_a.v,
t_b.s,
t_b.t
from
t_a,
t_b
where
t_a.u = t_b.s (+) and
t_a.v = t_b.t (+) and
t_b.s is null;
Maybe, you want to modify the statement to return
distinct values. If so, use
select distinct
instead of
select
A more elegant solution (imho) is here:
select u, v from t_a
minus
select s,t from t_b;
hth
Rene
--
Rene Nyffenegger
http://www.adp-gmbh.ch
Howard J. Rogers
"Alex" <ale...@eudoramail.com> wrote in message
news:29ff93bc.0311...@posting.google.com...
Daft column names don't help, but this is standard behaviour: an outer join
shows all the rows matched with one table AND the rows that are unique to
it. So if you want to see only the rows that aren't matched off, you have to
get rid of the ones that are matched.
So in new 9i language, this will work (with apologies for picking equally
daft column names, but at least mine aren't reserved keywords):
select * from A left outer join B on
(A.col1 = B.col1 and A.col2 = B.col2)
minus
select * from A join B on
(A.col1 = B.col1 and A.col2 = B.col2);
Using the older-style syntax you have, you might try this:
select * from A, B
where (A.col1=B.col1(+) and A.col2=B.col2(+))
minus
select * from A,B
where (A.col1=B.col1 and A.col2=B.col2)
Works for me, at any rate, but the execution plan isn't pretty.
Regards
HJR
Wolfgang Breitling
select A.col1, A.col2 from tableA A
minus
select B.col1, B.col2 from tableB B
or
select A.col1, A.col2 from tableA A
where (A.col1, A.col2) not in (
select /*+ hash_aj */ B.col1, B.col2 from tableB B
)
or
select A.col1, A.col2 from tableA A
where not exists (
select /*+ hash_aj */ null
from tableA A, tableB B
where A.col1 = B.col1
and A.col2 = b.col2
)
Which of these performs better depends on the sizes of
the tables involved and the number of matches vs mismatches.
"Howard J. Rogers" <h...@dizwell.com> wrote in
news:3fa8270c$0$3504$afc3...@news.optusnet.com.au:
--
What lies behind us and what lies before us are small matters when
compared to what lies within us.
Wolfgang Breitling
Oracle 7, 8, 8i, 9i OCP
Howard J. Rogers
"Wolfgang Breitling" <john...@aol.com> wrote in message
news:Xns9429BEADFACE3br...@198.161.157.145...
> Why not simply
>
> select A.col1, A.col2 from tableA A
> minus
> select B.col1, B.col2 from tableB B
>
> or
>
> select A.col1, A.col2 from tableA A
> where (A.col1, A.col2) not in (
> select /*+ hash_aj */ B.col1, B.col2 from tableB B
> )
>
> or
>
> select A.col1, A.col2 from tableA A
> where not exists (
> select /*+ hash_aj */ null
> from tableA A, tableB B
> where A.col1 = B.col1
> and A.col2 = b.col2
> )
>
> Which of these performs better depends on the sizes of
> the tables involved and the number of matches vs mismatches.
Agreed. All I was trying to do was use the OP's original query and show
where it was lacking. I wasn't trying to suggest that mine was a
particularly good way of doing it. The reference to the appalling execution
plan was intended to be the clue!
I think the fundamental issue here was the assumption (or desire) that an
outer join would show only the non-matched rows.
Regards
HJR
Wolfgang Breitling
> Agreed. All I was trying to do was use the OP's original query and
> show where it was lacking. I wasn't trying to suggest that mine was a
> particularly good way of doing it. The reference to the appalling
> execution plan was intended to be the clue!
>
> I think the fundamental issue here was the assumption (or desire) that
> an outer join would show only the non-matched rows.
>
Sorry for not recognizing the hint of sarcasm. I knew that you know better.