Re: Strange Behavior with SQL using IN
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: mdinh@xxxxxxxxx
- Date: Thu, 15 Apr 2010 14:44:15 -0700
Where are there wrong results?
(from mysql)
--------------
create table t1(id1 int not null)
--------------
Query OK, 0 rows affected (0.02 sec)
--------------
create table t2(id2 int not null)
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
insert into t1 values(1)
--------------
Query OK, 1 row affected (0.00 sec)
--------------
insert into t1 values(1)
--------------
Query OK, 1 row affected (0.01 sec)
--------------
insert into t1 values(1)
--------------
Query OK, 1 row affected (0.00 sec)
--------------
insert into t1 values(2)
--------------
Query OK, 1 row affected (0.01 sec)
--------------
insert into t1 values(2)
--------------
Query OK, 1 row affected (0.00 sec)
--------------
insert into t1 values(2)
--------------
Query OK, 1 row affected (0.00 sec)
--------------
insert into t2 values(1)
--------------
Query OK, 1 row affected (0.00 sec)
--------------
commit
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
select * from t1
--------------
+-----+
| id1 |
+-----+
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 2 |
+-----+
6 rows in set (0.00 sec)
--------------
select * from t2
--------------
+-----+
| id2 |
+-----+
| 1 |
+-----+
1 row in set (0.01 sec)
--------------
SELECT * FROM t1 c WHERE c.id1 IN (SELECT id1 FROM t2 a)
--------------
+-----+
| id1 |
+-----+
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 2 |
+-----+
6 rows in set (0.00 sec)
--------------
SELECT * FROM t1 c WHERE c.id1 IN (SELECT id2 FROM t2 a)
--------------
+-----+
| id1 |
+-----+
| 1 |
| 1 |
| 1 |
+-----+
3 rows in set (0.00 sec)
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
