Re: Strange Behavior with SQL using IN
- From: Daniel Fink <daniel.fink@xxxxxxxxxxxxxx>
- To: mdinh@xxxxxxxxx
- Date: Thu, 15 Apr 2010 15:44:46 -0600
You are not getting incorrect results, perhaps not the results you expect.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY>> SELECT *
FROM t1 c WHERE c.id1 IN (SELECT id1 FROM t2 a);
In this query, the subquery is selecting id1, which is a column from the
outer table. For every row in the t1 table, it selects t1.id1 from the
sub query (t2) and evaluates if t1.cd1 is in the result set...which it
will be. In this case all the rows are returned because t1.id1 = t1.id1.
The second query generates a result set (select t2.id2 from t2) and
compares that to t1.id1, so only the rows where id1 = 1 evaluate to true.
Daniel Fink
OptimalDBA http://www.optimaldba.com
Oracle Blog http://optimaldba.blogspot.com
Lost Data? http://www.ora600.be/
Michael Dinh wrote:
Thanks Ron. How do I prevent the transformation because of incorrect
results?
I thought it might have been optimizer_secure_view_merging from
googling, but that did not solve the issue.
Here is a better example
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY>>
truncate table t1;
Table truncated.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY>>
truncate table t2;
Table truncated.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY>> insert
into t1 values(1);
1 row created.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY>> /
1 row created.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY>> /
1 row created.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY>> insert
into t1 values(2);
1 row created.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY>> /
1 row created.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY>> /
1 row created.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY>> insert
into t2 values(1);
1 row created.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY>> commit;
Commit complete.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY>> SELECT
* FROM t1 c WHERE c.id1 IN (SELECT id1 FROM t2 a);
ID1
----------
1
1
1
2
2
2
6 rows selected.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY>> SELECT
* FROM t1 c WHERE c.id1 IN (SELECT id2 FROM t2 a);
ID1
----------
1
1
1
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY>>
NOTICE OF CONFIDENTIALITY - This material is intended for the use of
the individual or entity to which it is addressed, and may contain
information that is privileged, confidential and exempt from
disclosure under applicable laws. BE FURTHER ADVISED THAT THIS EMAIL
MAY CONTAIN PROTECTED HEALTH INFORMATION (PHI). BY ACCEPTING THIS
MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE AS FOLLOWS: YOU
AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN,
EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO
PERFORM YOUR OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE. If
the reader of this email (and attachments) is not the intended
recipient, you are hereby notified that any dissemination,
distribution or copying of this communication is strictly prohibited.
Please notify the sender of the error and delete the e-mail you
received. Thank you.
Other related posts:
