Use the tables below to show the difference between the results of a natural join, an equijoin (with PROF_CODE = 2) and a full outer join. Provide the resulting table of each join

Code:

CREATE TABLE student (

stu_code INTEGER PRIMARY KEY,

prof_code INTEGER

);

INSERT INTO student VALUES (100278,null);

INSERT INTO student VALUES (128569,2);

INSERT INTO student VALUES (512272,4);

INSERT INTO student VALUES (531235,2);

INSERT INTO student VALUES (531268,null);

INSERT INTO student VALUES (553427,1);

CREATE TABLE professor (

prof_code INTEGER PRIMARY KEY,

dept_code INTEGER

);

INSERT INTO professor VALUES (1,2);

INSERT INTO professor VALUES (2,6);

INSERT INTO professor VALUES (3,6);

INSERT INTO professor VALUES (4,4);

The SQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a way that, columns with the same name of associated tables will appear once only. In our Example, Prof_code will appear only once and it will be matched in both the table;  

SELECT * FROM Student natural join Professor on student.prof_code=Professor.prof_code ;  

Stud_code   Prof_code  Dept_code

128569        2           6

512272        4           4

531235        2           6

553427        1           2

EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables and an equal sign (=) is used as a comparison operator in the where clause to refer to equality. In our example, it will only show the result with prof_code=2 from the natural join result.

SELECT * FROM Student join Professor on student.prof_code=Professor.prof_code where student.prof_code=2;

Stud_code Prof_code Prof_code Dept_code

128569     2         2         6

531235     2         2         6

In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause.

SELECT * FROM professor full outer join student on professor.prof_code=student.prof_code ;

Stud_code Prof_code Prof_code Dept_code

100278                

128569    2          2         6

512272    4          4         4

531235    2          2         6

531268    

                    3         6

553427    1          1         2