Re: What is wrong with my logic? [message #39054] |
Mon, 10 June 2002 14:25 |
sverch
Messages: 582 Registered: December 2000
|
Senior Member |
|
|
Sorry if I was not clear again
You asked: Does that mean "a" is on tbl1, "b" is on tbl2, "c" is on tbl3?
No it does not. I wanted
SELECT
x.a,
x.b,
x.c
FROM
tbl x,
tbl y,
tbl r
WHERE x.key=y.key
AND r is empty.
That did not work.
Since I excluded table#3 (r) from the FROM clause, everything has been working fine.
But my question about Oracle's intelligence remains: If I need records from table x (not from table r!)that match records from table y while table r is empty why I cannot include table r in the FROM clause. It is empty anyway and I do not select any records from it and I do not join it with my other tables!
|
|
|
Re: What is wrong with my logic? [message #39061 is a reply to message #39054] |
Tue, 11 June 2002 05:17 |
Jon
Messages: 483 Registered: May 2001
|
Senior Member |
|
|
Exactly! You don't get anything FROM r so why do you want to include it in your FROM statement? Oracle (quite logically, in my opinion, but I've been working with it 12 years) will attempt to join every row in every table in the FROM clause, subject to the rules in the WHERE clause. If there are no rows, there is no join, and therefore no row to return. The following demonstrates that with a table I create and insert 3 rows into, the DUAL table (one row), and a null table. When I join the 3 row table and dual, I get three rows, but when I join those to the null table, I get no rows. Seems intelligent enough to me!
09:02:49 ==> drop table table1;
Table dropped.
09:03:01 ==> drop table empid;
Table dropped.
09:03:05 ==> drop table tbl1;
Table dropped.
09:03:09 ==> create table tbl1 (col1 NUMBER);
Table created.
09:03:10 ==> drop table tbl2;
drop table tbl2
*
ERROR at line 1:
ORA-00942: table or view does not exist
09:03:10 ==> create table tbl2 (col1 NUMBER);
Table created.
09:03:10 ==> insert into tbl1 values(1);
1 row created.
09:03:10 ==> insert into tbl1 values(2);
1 row created.
09:03:10 ==> insert into tbl1 values(3);
1 row created.
09:03:10 ==> select * from tbl1, dual;
COL1 D
--------- -
1 X
2 X
3 X
09:03:10 ==> select * from tbl1, dual, tbl2;
no rows selected
09:03:11 ==>
|
|
|
Re: What is wrong with my logic? [message #39063 is a reply to message #39054] |
Tue, 11 June 2002 10:45 |
Jon
Messages: 483 Registered: May 2001
|
Senior Member |
|
|
Yes, relational is definitely the operative word. Glad you're not mad at Oracle any more. Many things (not all, but many) they do make sense once you get into their mode of thinking.
|
|
|