IN改写关联注意事项！

最新推荐文章于 2024-04-24 17:32:33 发布

weixin_30412577

最新推荐文章于 2024-04-24 17:32:33 发布

阅读量126

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/zhaoyangjian724/p/3797963.html

SQL> select * from a1;

	ID NAME
---------- ----------
	 1 a
	 2 a

SQL> select * from a2;

	ID NAME
---------- ----------
	 1 a
	 3 a

SQL> select * from a1 where id in (select id from a2);

	ID NAME
---------- ----------
	 1 a

改写成关联:
SQL> select * from a1,a2
  2  where a1.id= a2.id;

	ID NAME 	      ID NAME
---------- ---------- ---------- ----------
	 1 a		       1 a

此时是因为(select id from a2)子查询只返回一条记录,那么子查询返回多条呢?
SQL> select * from a2;

	ID NAME
---------- ----------
	 1 a
	 3 a
	 1 a

SQL> select * from a1 where id in (select id from a2);

	ID NAME
---------- ----------
	 1 a

SQL> select a1.* from a1,a2
  2  where a1.id= a2.id;

	ID NAME
---------- ----------
	 1 a
	 1 a

此时是不是出现重复数据了?
继续测试:
SQL> select * from a1;

	ID NAME
---------- ----------
	 1 a
	 2 a
	 1 a

SQL> select * from a2;

	ID NAME
---------- ----------
	 3 a
	 1 a

SQL> select * from a1 where id in (select id from a2);

	ID NAME
---------- ----------
	 1 a
	 1 a

SQL>  select a1.* from a1,a2
  2  where a1.id=a2.id;

	ID NAME
---------- ----------
	 1 a
	 1 a


结论:IN改写关联时,如果子查询返回多条记录,需要去从。

测试2：
SQL> select * from a1;

	ID NAME
---------- ----------
	 1 a
	 2 a
	 1 a

SQL> select * from a2;

	ID NAME
---------- ----------
	 2 a
	 3 a
	 3 a
	 1 a
	 1 a

SQL> select * from a1 where a1.id in (select id from a2);

	ID NAME
---------- ----------
	 2 a
	 1 a
	 1 a

SQL> select a1.* from a1 ,a2
  2  where a1.id=a2.id;

	ID NAME
---------- ----------
	 2 a
	 1 a
	 1 a
	 1 a
	 1 a

正确写法:
SQL>   select a1.* from a1 ,(select id from a2 group by id) a2
  2   where  a1.id=a2.id;

	ID NAME
---------- ----------
	 1 a
	 2 a
	 1 a

子查询里面的关联列没有重复数据可以直接改In，如果关联列有重复数据需要去从

转载于:https://www.cnblogs.com/zhaoyangjian724/p/3797963.html