UNION

SELECT
FROM
ON
WHERE

SELECT
FROM
ON
WHERE

SELECT
FROM
ON
WHERE

SELECT  'Student' AS type,
        students.name AS javaData
FROM    students JOIN courses
ON      students.courseId = courses.id
WHERE   courses.name = 'Java'

UNION

SELECT 'Book' AS type,
books.name AS javaData
FROM courses JOIN books
ON books.courseId = courses.id
WHERE courses.name = 'Java'

UNION

SELECT 'Author' AS type,
authors.name AS javaData
FROM courses JOIN books JOIN authors
ON books.courseId = courses.id AND
books.authorId = authors.id
WHERE courses.name = 'Java'