Say I have created the following tables:

CREATE TABLE TEACHER
(
TEACHER_ID CHAR(4) PRIMARY KEY,
SUBJECT_ID CHAR(4)
);

CREATE TABLE SUBJECT
(
SUBJECT_ID CHAR(4) PRIMARY KEY,
DESCRIP VARCHAR2(20) NOT NULL
);

In the TEACHER table, I have the following data included:

('T001', 'S5');
('T002', 'S1');
('T003', 'S2');
('T004', NULL);
('T005', 'S2');
('T006', 'S2');
('T007', NULL);
('T008', 'S2');
('T009', NULL);
('T010', 'S2');
('T011', NULL);
('T012', 'S7');
('T013', NULL);

And in the SUBJECT table, I have the following data included:

('S1', 'MATHS');
('S2', 'ENGLISH');
('S3', 'GEOGRAPHY');
('S4', 'HISTORY');
('S5', 'ARTS');
('S6', 'INFO TECH');
('S7', 'PHYSICAL EDU');

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:

SUBJECT_ID DESCRIP
------------ --------
S3 GEOGRAPHY
S4 HISTORY
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?

The SQL is wrong in 2 senses:

SELECT TEACHER.SUBJECT_ID, SUBJECT.DESCRIP
FROM TEACHER, SUBJECT
WHERE SUBJECT.SUBJECT_ID = TEACHER.SUBJECT_ID
AND TEACHER.SUBJECT_ID IS NULL;

1) There are no SUBJECT records where SUBJECT_ID is null, so no SUBJECT record could possibly match a TEACHER record where TEACHER.SUBJECT_ID is null.

2) Even if there were, the "=" operator will fail when either or both operands is null - i.e. even this won't work:

SELECT *
FROM TEACHER
WHERE SUBJECT_ID = NULL;

NULLs can only be compared using IS NULL and IS NOT NULL.

There are various ways to get the subjects that have no teacher. Here is one that works in Oracle:

SELECT SUBJECT_ID FROM SUBJECT
MINUS
SELECT SUBJECT_ID FROM TEACHER;

However, some DBMSs have "EXCEPT" instead of "MINUS", and some don't support either. So here is another way:

SELECT SUBJECT_ID FROM SUBJECT
WHERE NOT EXISTS
( SELECT NULL FROM TEACHER WHERE TEACHER.SUBJECT_ID = SUBJECT.SUBJECT_ID);

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):