SQL各种JOIN

JOIN（= INNER JOIN）：返回匹配的结果，没有匹配则没结果；

LEFT JOIN（= LEFT OUTER JOIN）：返回匹配的与左表的所有数据；

RIGHT JOIN（= RIGHT OUTER JOIN）：返回匹配的与右表的所有数据；

FULL JOIN（= FULL OUTER JOIN）：返回两个表的所有数据，匹配的在同一行。

在oracle中的测试结果：

AAA表数据　　BBB表数据　　CCC表数据

SELECT * FROM AAA A JOIN BBB B ON A.ID = B.ID;

SELECT * FROM AAA A JOIN CCC B ON A.ID = B.ID;

SELECT * FROM AAA A LEFT JOIN BBB B ON A.ID = B.ID;

SELECT * FROM AAA A LEFT JOIN CCC B ON A.ID = B.ID;

SELECT * FROM AAA A RIGHT JOIN BBB B ON A.ID = B.ID;

SELECT * FROM AAA A RIGHT JOIN CCC B ON A.ID = B.ID;

SELECT * FROM AAA A FULL JOIN BBB B ON A.ID = B.ID;

 

SELECT * FROM AAA A FULL JOIN CCC B ON A.ID = B.ID;