RE: SQL question

Mike,

Welcome to the infamous cartesian join - a partial one anyway. You get every
combination of the chosen row in a with all rows from c. I believe you have
3 rows in table c. Just a guess.

Your second query should be like this:

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

That way you tell it that you only want the row in c where the id matches
the selected one in a (i.e. 2).

HTH,

Mike

-----Original Message-----
From: Mike Schmitt [mailto:mschmitt@xxxxxxxxxxxx]
Sent: Thursday, May 06, 2004 3:16 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------


This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity to
which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified that
any dissemination, distribution or copying of this e-mail is prohibited. If
you have received this e-mail in error, please notify the sender by replying
to this message and delete this e-mail immediately.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: