Home » SQL & PL/SQL » SQL & PL/SQL » Re: What is wrong with my logic?
Re: What is wrong with my logic? [message #39054] Mon, 10 June 2002 14:25 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Generating DDLs of Existing tables
Next Topic: define view column as a PL/SQL functon
Goto Forum:
  


Current Time: Thu Mar 28 08:53:02 CDT 2024