Demonstrating FULL, LEFT, RIGHT Join

SQL> select * from movie;

       MID        DID
---------- ----------
       100          1
       101          2
       102          2
       103          3
       104          1
       105
       106

7 rows selected.

SQL> select * from director;

       DID NAME
---------- --------------------
         1 Jane
         2 Bob
         3 Denis
         4 Jack
         5 Dev
         6 Kay

6 rows selected.

SQL> select * from movie join director  using (did) order by mid;

       DID        MID NAME
---------- ---------- --------------------
         1        100 Jane
         2        101 Bob
         2        102 Bob
         3        103 Denis
         1        104 Jane

SQL> select * from movie left join director  using (did) order by mid;

       DID        MID NAME
---------- ---------- --------------------
         1        100 Jane
         2        101 Bob
         2        102 Bob
         3        103 Denis
         1        104 Jane
                  105
                  106

7 rows selected.

SQL> select * from movie right join director  using (did) order by mid;

       DID        MID NAME
---------- ---------- --------------------
         1        100 Jane
         2        101 Bob
         2        102 Bob
         3        103 Denis
         1        104 Jane
         4            Jack
         5            Dev
         6            Kay

8 rows selected.

SQL> select * from movie full join director  using (did) order by mid;

       DID        MID NAME
---------- ---------- --------------------
         1        100 Jane
         2        101 Bob
         2        102 Bob
         3        103 Denis
         1        104 Jane
                  105
                  106
         4            Jack
         5            Dev
         6            Kay

10 rows selected.