Full join, left join, and right join

SQL beginners often have a headache for left join and right join. In fact, you don't have to memorize it. Just write an SQL statement and it will be finished.

Use SQL to verify the result each time you want, and then use it again. It takes a long time to remember.

As follows:

1. Create an environment

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

Some examples of full join are also written (+). I don't know if the author has tried it.

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