full join 与 left join 和 right join

full join 与 left join 和 right join

初学sql的常对left join 和 right join 感到头痛，其实不用死记硬背，写个sql验正下就完了。

每次想用时用sql验正下结果，然后再用。时间长了就记住了。

如下：

1.建立环境

DROP TABLE a PURGE
/
DROP TABLE b PURGE
/
CREATE TABLE a AS
SELECT 'left_1' AS str,'1' AS v FROM dual UNION ALL
SELECT 'left_2','2' AS v FROM dual UNION ALL
SELECT 'left_3','3' AS v FROM dual UNION ALL
SELECT 'left_4','4' AS v FROM dual
/
CREATE TABLE b AS
SELECT 'right_3' AS str,'3' AS v FROM dual UNION ALL
SELECT 'right_4','4' AS v FROM dual UNION ALL
SELECT 'right_5','5' AS v FROM dual UNION ALL
SELECT 'right_6','6' AS v FROM dual
/

full join

SQL> SELECT a.str AS leftv,b.str AS right_v FROM a FULL JOIN b ON b.v = a.v ORDER BY 1,2
  2  /
LEFTV  RIGHT_V
------ -------
left_1 
left_2 
left_3 right_3
left_4 right_4
       right_5
       right_6
6 rows selected

有些网文full join也写了(+)的例子，不知作者试验过没有

SQL> SELECT a.str AS leftv,b.str AS right_v FROM a , b WHERE b.v(+) = a.v(+) ORDER BY 1,2
  2  /
SELECT a.str AS leftv,b.str AS right_v FROM a , b WHERE b.v(+) = a.v(+) ORDER BY 1,2
ORA-01468: a predicate may reference only one outer-joined table

left join

SQL> SELECT a.str AS leftv,b.str AS right_v FROM a LEFT JOIN b ON b.v = a.v ORDER BY 1,2
  2  /
LEFTV  RIGHT_V
------ -------
left_1 
left_2 
left_3 right_3
left_4 right_4
SQL> SELECT a.str AS leftv,b.str AS right_v FROM a,b WHERE b.v(+) = a.v ORDER BY 1,2
  2  /
LEFTV  RIGHT_V
------ -------
left_1 
left_2 
left_3 right_3
left_4 right_4

right join

SQL> SELECT a.str AS leftv,b.str AS right_v FROM a RIGHT JOIN b ON b.v = a.v ORDER BY 1,2
  2  /
LEFTV  RIGHT_V
------ -------
left_3 right_3
left_4 right_4
       right_5
       right_6
SQL> SELECT a.str AS leftv,b.str AS right_v FROM a,b WHERE b.v = a.v(+) ORDER BY 1,2
  2  /
LEFTV  RIGHT_V
------ -------
left_3 right_3
left_4 right_4
       right_5
       right_6