DROP TABLE IF EXISTS Instructor2;
DROP TABLE IF EXISTS Teaches2;

CREATE TABLE Instructor2 
  ( ID      	numeric(5,0),
    name        varchar(20),
    dept_name   varchar(20)    
  );

CREATE TABLE Teaches2
  ( ID			numeric(5,0) REFERENCES Instructor2(ID),
	course_id   varchar(20)
  );
  
INSERT INTO Instructor2 VALUES (10101,'Srinivasan', 'CompSci');
INSERT INTO Instructor2 VALUES (12121,'Wu', 'Finance');
INSERT INTO Instructor2 VALUES (15151,'Mozart', 'Music');

INSERT INTO Teaches2 VALUES (10101, 'CS-101');
INSERT INTO Teaches2 VALUES (12121, 'FIN-201');
INSERT INTO Teaches2 VALUES (76766, 'BIO-101');

SELECT * FROM Instructor2 NATURAL JOIN Teaches2 ;

-- THIS ONE GETS DUPLICATE ID COLUMNS 
SELECT DISTINCT * FROM Instructor2 LEFT  OUTER JOIN Teaches2 ON Instructor2.ID=Teaches2.ID GROUP BY Instructor2.ID;

-- But these do work
SELECT * FROM Instructor2 LEFT OUTER JOIN Teaches2 ON Instructor2.ID=Teaches2.ID;
SELECT * FROM Instructor2 RIGHT OUTER JOIN Teaches2 ON Instructor2.ID=Teaches2.ID;

/* MySQL doesn't have FULL OUTER JOIN */


