ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 表连接测试

表连接测试

原创 Linux操作系统 作者:ziling2011 时间:2012-11-08 15:58:03 0 删除 编辑
select level from dual connect by level<1000;
DROP TABLE a;
DROP TABLE b;
CREATE TABLE a(ID NUMBER,NAME VARCHAR2(10));
CREATE TABLE b(ID NUMBER,NAME VARCHAR2(10));
INSERT INTO a VALUES(1,'a');
INSERT INTO a VALUES(2,'b');
INSERT INTO a VALUES(3,'c');
INSERT INTO b VALUES(1,'a');
INSERT INTO b VALUES(2,'b');
INSERT INTO b VALUES(4,'d');
COMMIT;
select * from a;
select * from b;
SELECT * FROM a,b WHERE a.ID=b.ID;
SELECT * FROM a JOIN b ON a.ID=b.ID;

--第2组--
SELECT * FROM a LEFT JOIN b ON a.ID=b.ID AND a.NAME='b';
SELECT * FROM a,b WHERE a.ID=b.ID(+) AND a.NAME='a';
SELECT * FROM a,b WHERE a.ID=decode(a.NAME,'a',b.ID(+));
--第3组--
SELECT * FROM
(SELECT * FROM a WHERE a.NAME='a') a LEFT JOIN b ON  a.ID=b.ID;
SELECT * FROM a RIGHT JOIN b ON a.ID=b.ID AND a.NAME='a';
==>
SELECT * FROM a,b WHERE a.ID(+)=b.ID AND a.NAME(+)='a';
SELECT * FROM a,b WHERE a.ID(+)=b.ID AND a.NAME='a';   --先按id join然后选出a.id='a'的肯定是匹配上的,所以肯定是内连接
==>
SELECT * FROM a RIGHT JOIN b ON a.ID=b.ID WHERE a.NAME='a';
SELECT * FROM a,b WHERE b.id=decode(a.NAME(+),'a',a.ID(+));
SELECT * FROM
(SELECT * FROM a WHERE a.NAME='a') a RIGHT JOIN b ON  a.ID=b.ID;
--第4组--
SELECT * FROM  a LEFT JOIN b ON a.ID=b.ID AND b.ID IS NULL;  ===>先外连接找b.ID IS NULL的肯定是没有匹配的,和具体常量的不同
===>
SELECT * FROM  a,b WHERE a.ID=b.ID(+) AND b.ID IS NULL;
不等价于
SELECT * FROM  a,b WHERE a.ID=b.ID(+) AND b.ID(+) IS NULL;
--第5组--
DROP TABLE c;
CREATE TABLE c
AS
SELECT 1 ID,'a' NAME FROM dual UNION ALL
SELECT 5 ID,'x' NAME FROM dual;
SELECT * FROM a,b,c WHERE a.ID(+)=b.ID AND a.ID(+)=c.ID;
SELECT * FROM a RIGHT JOIN b ON a.ID=b.ID
RIGHT JOIN c ON a.ID=c.ID;
SELECT * FROM a,b WHERE a.ID(+)=b.ID OR a.NAME=b.NAME;
SELECT * FROM a,b WHERE a.ID(+)=b.ID OR a.NAME(+)=b.NAME;
SELECT * FROM a RIGHT JOIN b ON a.ID=b.ID OR a.NAME=b.NAME;
SELECT * FROM a,b WHERE a.ID=b.ID(+) AND a.NAME IN (SELECT 'a' FROM dual);
SELECT * FROM a,b WHERE a.ID=b.ID(+) AND b.NAME(+) IN (SELECT 'a' FROM dual);
SELECT * FROM a LEFT JOIN b ON a.ID=b.ID AND b.NAME IN (SELECT 'a' FROM dual);
 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25989950/viewspace-748709/,如需转载,请注明出处,否则将追究法律责任。

上一篇: show_space
下一篇: trace全过程
请登录后发表评论 登录
全部评论

注册时间:2011-07-18

  • 博文量
    52
  • 访问量
    115172