用于理解join的例子

用于理解join的例子
 
--第1组--       这个相当于全关联，只显示两个都存在的
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='a';
 
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(+));
 
SELECT * FROM 
(SELECT * FROM a WHERE a.NAME='a') a LEFT JOIN b ON  a.ID=b.ID;
 
--第3组--
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';
 
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组--  2，3 (+)出现在and条件之后，是在关联前取它为空，然后关联，没有(+)是关联完了之后取b.id为空的情况
SELECT * FROM  a LEFT JOIN b ON a.ID=b.ID AND 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);

上一个：Oracle常用调优手段
下一个：oracle pl/sql编程ddl语句分支循环判断