RE: SQL question

From: Clark, Tommy R <TOMMY.R.CLARK_at_saic.com>
Date: Thu, 6 May 2004 14:22:01 -0700
Message-Id: <88536557A737204B89D6B6B19FF5708F013FA207@us-oak-ridge-tss.mail.saic.com>

No. Since there was no join condition, you ended up with all rows from test1 paired with the row from test2.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mike Schmitt Sent: Thursday, May 06, 2004 5:16 PM
To: oracle-l_at_freelists.org
Subject: SQL question

I was messing around with SQL, something that I am not very good with, when I ran into something I felt was strange. The SQL from both statements can be seen below, along with their execution plans. I do not quit understand why the 2nd query came back with 3 rows. I know its from user error, but shouldn't Oracle just ignore the test1 table in the second query?

SQL> select a.first_name from test2 a where a.id_number = 2; FIRST_NAME

tom

SQL> select a.first_name from test2 a , test1 c where a.id_number = 2; FIRST_NAME

tom
tom
tom

Execution Plan (1st statement)

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'TEST2' Execution Plan (2nd statement)

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS

    2    1     TABLE ACCESS (FULL) OF 'TEST1'
    3    1     TABLE ACCESS (FULL) OF 'TEST2'







----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Thu May 06 2004 - 16:20:13 CDT