获取不了mysql表,从第一个表中获取不在第二个表mysql中的记录

I have following records in these two mysql tables.

Table-A

Question_No

1

2

3

4

5

Table-B

Roll_No Question_No Ans_Option

1001 1 NULL

1001 2 D

1001 3 NULL

1002 1 C

1002 2 NULL

Here the word "NULL" is explicitly inserted into column , nothing to be confused.

How can I display the following result by mysql query?

Questions not attempted by roll no 1001 are : 1, 3, 4, 5

Questions not attempted by roll no 1002 are : 2, 3, 4, 5

I tried with following code but not working

select distinct a.* from table_A a NATURAL LEFT JOIN table_B b where

b.Question_No IS NULL and b.Roll_No=1001;// where I am wrong here ? I have

not set any column as primary key , should I set?

Thanks a lot in Advance

解决方案

When you put WHERE condition on LEFT OUTER JOIN it is literally the same as INNER JOIN:

select distinct a.*

from table_A a

NATURAL LEFT JOIN table_B b

where b.Question_No IS NULL

and b.Roll_No=1001; -- this condition makes it `NATURAL JOIN`

You could rewrite it as:

SELECT DISTINCT a.*

FROM table_A a

LEFT JOIN table_b b

ON a.Question_No = b.Question_No

AND b.Roll_No=1001

WHERE b.Ans_Option_No IS NULL