RE: SQL question

From: Michael Milligan <Michael.Milligan_at_ingenix.com>
Date: Thu, 6 May 2004 16:55:41 -0500
Message-ID: <BEB4912D93B5D24694F5231821555306014BA029@slc7-sstmsx1.pcl.ingenix.com>

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_at_uchicago.edu] Sent: Thursday, May 06, 2004 3: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

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

Received on Thu May 06 2004 - 16:54:00 CDT