Apologies if this is duplicate, but failing to google or search here for an answer. I'm writing in oracle SQL, and trying to figure out how to always return the rows from a given table when seeing the results. So, for a simple example:

​

Table: Students

Student_ID

Name

Creation_Date

​

Table: Classes

Class_ID

Dept_ID

Class_name

Student_ID

Class_Start_Date

Class_End_Date

​

If I wanted to query showing all students in the Student table, and a count of how many classes they are registered to, I could do:

SELECT

Students.Name, count(Classes.class_ID)

FROM

Students

LEFT OUTER JOIN Clases

on Students.Student_ID = Classes.Student ID

GROUP BY

Students.Name

;

​

Here is my problem: I want to run the same query as above and also filter on class_start_date, but I still want to show all students whether they have a class within the date range filtered for or not. I tried using:

WHERE (class_start_date between '01-JAN-2019' and '01-APR-2019' OR class_start_date is null)

​

This seems to work, but only when the student has no classes. If the student has classes where the date falls outside that condition, the name is missing from the output (i.e.: If student does not exist in classes table, student name shows up; if student has classes in the date range, Student name shows up; if student has classes, but none in the date range, student does not show up).

For students with classes but none in the date range, I would like to return Student Name with count of 0.

How can I trick SQL into doing what I want. :)