Question on join where one column one side is null

Question

I have two tables, storing candidates and their grades. A simple join can get all the candidates and their grades, by matching IDs etc.

e.g:

Alan    C
George  A

etc.. However:

Nina

The grade for Nina is null because she did not take the exam.

How can I get output like the following?

Alan   C
George A
Nina

So still get Nina, even though she did not take the exam. I've tried null checks and then performing something, etc.

Thanks

Answer 1

You want to use a left join.

select c.name, coalesce(g.grade,'')
    from candidate c
        left join grade g
            on c.candidate_id = g.candidate_id

Answer 2

You need to use a left join. For example:

 select c.name, g.grade
 from candidates c
 left join grades g on g.candidateid=c.id

A left join always lists all rows on the "left" side of the join - in this case, candidates. On rows where the candidate has no entry in the grades table, all columns that are filled from that table will be null.

Answer 3

You need to use a left join for that.