Add default for missing rows (combinations of students and subjects)

Question

I have a table which holds grades of students in some subjects.

STUDENT_GRADES

id | studentid | subjectid | grade
-----------------------------------
1  |  1        | 1         | A+
2  |  1        | 2         | A
3  |  1        | 3         | A-
4  |  2        | 2         | A
5  |  2        | 3         | A-
6  |  3        | 2         | B
7  |  4        | 3         | B+

and I have another table which holds subject list.

SUBJECTS

subjectid | subjectname
-----------------------------
1         | Maths     
2         | Science
3         | History

Now I have to retrieve result in the following format.

FORMATTED_GRADES

studentid | subjectid | grade
-----------------------------------
1         | 1         | A+
1         | 2         | A
1         | 3         | A-

2         | 1         | fail
2         | 2         | A
2         | 3         | A-

3         | 1         | fail
3         | 2         | B
3         | 3         | fail

4         | 1         | fail
4         | 2         | fail
4         | 3         | B+

The FORMATTED_GRADES contains each student from STUDENT_GRADES and his/her grade in each subject. If there is no grade of a student in a subject in STUDENT_GRADES, then the grade of the student in that subject should be fail.

Answer 1

Use cross join to generate the rows, and left join and coalesce() to get the data you want:

select st.student_id, su.subject_id,
       coalesce(sg.grade, 'fail') as grade
from (select distinct student_id from student_grades) st cross join
     subjects su left join
     student_grades sg
     on sg.student_id = st.student_id and sg.subject_id = su.subject_id

Answer 2

You most probably have another table students with studentid as primary key. Use that in a CROSS JOIN to form a Cartesian product with one row for each combination of student and subject:

SELECT student_id, subject_id, COALESCE(g.grade, 'fail') AS grade
FROM        students       t
CROSS  JOIN subjects       u
LEFT   JOIN student_grades g USING (student_id, subject_id);

Most importantly, this includes students that failed in all subjects. If there is no entry in student_grades at all, the attempt to distill unique students from student_grades (like @Gordon demonstrates) will miss out on those.

Assuming that subjects has no column named student_id and students has no column subject_id. Else you must use the more explicit join syntax with ON instead of the shortcut with USING.

When joining with the USING clause, only one instance of the (necessarily identical) join columns is in the output table, hence we do not need table qualification in the SELECT list (even if that would be allowed anyway).

Also assuming that student_grades.grade is a character data type. Else you need to cast explicitly to get compatible data types in COALESCE. Like: COALESCE(g.grade::text, 'fail')