用于理解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);