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 ASSELECT 'left_1' AS str,'1' AS v FROM dual UNION ALLSELECT 'left_2','2' AS v FROM dual UNION ALLSELECT 'left_3','3' AS v FROM dual UNION ALLSELECT 'left_4','4' AS v FROM dual/CREATE TABLE b ASSELECT 'right_3' AS str,'3' AS v FROM dual UNION ALLSELECT 'right_4','4' AS v FROM dual UNION ALLSELECT 'right_5','5' AS v FROM dual UNION ALLSELECT '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_3left_4 right_4       right_5       right_66 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,2ORA-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_3left_4 right_4SQL> 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_3left_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_3left_4 right_4       right_5       right_6SQL> 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_3left_4 right_4       right_5       right_6