从第一表中获取不在第二表mysql中的记录 [英] Fetch record from 1st table which are not in 2nd table mysql

问题描述

我在这两个mysql表中有以下记录.

表A

       Question_No 
           1
           2
           3
           4
           5

表B

Roll_No  Question_No  Ans_Option
 1001      1         NULL 
 1001      2         D
 1001      3         NULL
 1002      1         C
 1002      2         NULL 

此处在列中明确插入了"NULL"一词，没有什么可混淆的.

如何通过mysql查询显示以下结果?

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

我尝试使用以下代码，但不起作用

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?

非常感谢Advance

解决方案

在LEFT OUTER JOIN上放置WHERE条件时，它实际上与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`

您可以将其重写为:

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

db< fiddle演示

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

db<>fiddle demo

这篇关于从第一表中获取不在第二表mysql中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！