SQL | How to display "NA" if row doesn't exists, without switch case

January 14, 2022

I have two tables,

Table 1:

studentId	department
S01	Mech
S02	Mech
S03	CSE

Table 2:

studentId	Result
S01	Pass
S03	Fail

I want to display : studentId, Result of Mech department, for those students whose result is not given in table 2, result should show "Absent".

Desired output,

studentId	Result
S01	Pass
S02	Absent

How can I do it without using switch cases? (Using only: Joins, Sub queries, Function, Group by, Having, Where, etc.) (i.e Basics only)

Using, SQL (Oracle)

I tried too much, but was unable to display "Absent" for "S02"

>Solution :

Use a left join between the two tables, along with COALESCE to render Absent as the result for any mechanical student missing in the second table.

SELECT t1.studentId, COALESCE(t2.Result, 'Absent') AS Result
FROM Table1 t1
LEFT JOIN Table2 t2
    ON t2.studentId = t1.studentId
WHERE t1.department = 'Mech';