I have also set up the referential integrity between these two tables:
ALTER TABLE TEACHER
ADD (CONSTRAINT FK_TEACHER_SUB FOREIGN KEY(SUBJECT_ID) REFERENCES SUBJECT(SUBJECT_ID));
Just say if I want to list any of the SUBJECT_ID that contains a NULL value in the TEACHER table, how would I be able to achieve that using SELECT to query the data?
Or another way to put it, how do I show all the subjects that do not have any teachers?
From the data above, and given that my SELECT query is correct, I would expect something like this to show:
S6 INFO TECH
This is what I did, but it showed up nothing...
SELECT TEACHER.SUBJECT_ID, SUBJECT.DESCRIP
FROM TEACHER, SUBJECT
WHERE SUBJECT.SUBJECT_ID = TEACHER.SUBJECT_ID
AND TEACHER.SUBJECT_ID IS NULL;
It's quite easy to show up all the subjects that have teachers (by just removing the last line from the code above so it gets all data but not the ones with NULL), but why can't i do it for subjects that have no teachers?
I just started learning SQL recently, please guide me on what went wrong?
SELECT SUBJECT_ID FROM SUBJECT
SELECT SUBJECT_ID FROM TEACHER;
Hi Andrewst, thank you for clearing this up for me, now I understand what went wrong. As with the quoted code above, you used MINUS, is that relational algerba to find the difference between two sets of data (find the common data and remove it)? I was thinking about doing that, but I didn't know it would work even when there are NULL values involved.
Yes, it is - and it does work even if there are NULLs around (NULLs are ignored):
SQL> select * from t1;
SQL> select * from t2;
SQL> select val from t1
3 select val from t2;