Using NVL with JOIN

I want to display 2 values in table but only Student 301 is displayed. Student 102 doesn’t received any grades. I joined two tables. Here’s my query. Thanks for your help

SELECT g.student_id, NVL(g.numeric_grade, 0) AS numeric_grade, e.enrollment_date
  FROM grade_info g
  JOIN enrollment_info e 
    ON g.student_id = e.student_id
 WHERE g.student_id IN (102, 301)

Answer

Presumably, both students are in enrollment_info. If so, you can use:

SELECT e.student_id, NVL(g.numeric_grade, 0) AS numeric_grade, e.enrollment_date
FROM enrollment_info e LEFT JOIN
     grade_info g
     ON e.student_id = g.student_id
WHERE e.student_id IN (102, 301);

Note the use of LEFT JOIN. The table where you want to keep all the rows comes first. You can filter on this table in the WHERE clause. Although not part of this question, filters on subsequent tables (when using LEFT JOIN) should be in the ON clause.

Also note the use of meaningful table aliases rather than arbitrary letters. Arbitrary letters make the query much harder to follow — and make it easy to make mistakes.

If both students are not in enrollment info, then you need to start with them using a derived table of some sort. That would look like:

SELECT s.student_id, COALESCE(g.numeric_grade, 0) AS numeric_grade, e.enrollment_date
FROM (SELECT 102 as student_id FROM DUAL UNION ALL
      SELECT 301 as student_id FROM DUAL
     ) s LEFT JOIN
     enrollment_info e
     ON e.student_id = s.student_id LEFT JOIN
     grade_info g
     ON g.student_id = s.student_id;

This also uses COALESCE() instead of NVL() because COALESCE() is the Standard SQL function for replacing NULL values.