Data Warehousing Community Forum

I would like to select rows with (NULL & non-NULL) combination

student_course table

id,student,subject
1, 100, NULL
2,100, Science
3,200, Mathematics
4,200, science
5, 300, English
6, 400, NULL
7, 500, Mathematics
8, 500, NULL
9, 600, Science

Desired output:-

id,student,subject
1, 100, NULL
2, 100, Science
7, 500, Mathematics
8, 500,

I do not want to select the row with student id 400 as it only contains NULL.
I would like to see a combination of NULL & not null values.

Any help is appreciated.

Using the below query, I am able to retrieve the expected data but I was wondering if there is a better way to do this.

create table student_course(id number,student number,subject varchar2(30));

insert into student_course values(1,100,NULL);
insert into student_course values(2,100,'Science');
insert into student_course values(3,200,'Mathematics');
insert into student_course values(4,200,'Science');
insert into student_course values(5,300,'English');
insert into student_course values(6,400,NULL);
insert into student_course values(7,500,'Mathematics');
insert into student_course values(8,500,NULL);
insert into student_course values(9,600,'Science');

commit;

select * from student_course where student in (select student
from student_course
group by student
   having
   min(nvl(subject,'0'))='0' and count(distinct subject)>=1);

The highlighted part could cause a problem.
Firstly it involves a min() operator applied on a string column, depending on it to return '0' as the min() value.
What about empty string '' in subject ?

Your requirement is specifically to get students that have both nulls and non-nulls in "subject" -this query will also get you students that have the string '0' in subject.
Whether or not '0' could ever appear in subject would be debatable.