SQL

SQL

Apache Derby

MySQL

use test;
drop table Course;
create table Course(
CourseID char,
Name varchar(45),
PRIMARY KEY ( CourseID )
);
INSERT INTO `test`.`Course`
(`CourseID`,
`Name`)
VALUES
("A",
"Math");
INSERT INTO `test`.`Course`
(`CourseID`,
`Name`)
VALUES 
("B",
"English");
INSERT INTO `test`.`Course`
(`CourseID`,
`Name`)
VALUES
("C",
"Science");
drop table Student;
create table Student(
StudentID int,
Name varchar(45),
PRIMARY KEY ( StudentID )
);
INSERT INTO `test`.`Student`
(`StudentID`,
`Name`)
VALUES
(1,
"Bob");
INSERT INTO `test`.`Student`
(`StudentID`,
`Name`)
VALUES
(2,
"Sue");
INSERT INTO `test`.`Student`
(`StudentID`,
`Name`)
VALUES
(3,
"Tom");
drop table Enrollment;
create table Enrollment(
CourseID char,
StudentID int
);
INSERT INTO `test`.`Enrollment`
(`CourseID`,
`StudentID`)
VALUES
("A",
1);
INSERT INTO `test`.`Enrollment`
(`CourseID`,
`StudentID`)
VALUES
("A",
3);
INSERT INTO `test`.`Enrollment`
(`CourseID`,
`StudentID`)
VALUES
("C",
1);

SELECT CourseName, StudentName
FROM(
SELECT C.Name as CourseName, S.Name as StudentName
FROM Enrollment E
LEFT OUTER JOIN Course C ON C.CourseID = E.CourseID
RIGHT OUTER JOIN Student S ON S.StudentID = E.StudentID
UNION
SELECT C.Name as CourseName, S.Name as StudentName
FROM Enrollment E
RIGHT JOIN Course C ON C.CourseID = E.CourseID
LEFT JOIN Student S ON S.StudentID = E.StudentID
) results
ORDER BY CourseName