SQL - JOIN 3 tables but show where data is missing from the middle table

Question

Given some Students:

StudentID  Name
---------------
1          James
2          Emma

Given some Exams:

ExamID  Name
---------------
1      Maths
2      English
3      Science

Each student will eventually have a Mark for each exam, but we're midway through exams and some marks won't exist yet:

MarkId StudentId ExamId Grade
-----------------------------
1      1         1      B
2      1         2      A
3      2         1      A

How can I query that (middle) Mark table to show all existing marks, but a null row where a student hasn't taken an exam yet?

The results I'm after should look like this:

I thought I should be able to get this with a RIGHT JOIN, but the 3 JOINs always throw the NULLs away.

Answer 1

Use a cross join to generate the rows and left join to bring in the grades:

select s.*, e.*, m.grade
from students s cross join
     exams e left join
     marks m
     on m.studentid = s.studentid and m.examid = e.examid;