关于MySQL Coursera页面上WHERE EXISTS和子查询的问题

Question

再次编辑以添加：第二个表("dogs")的user_guid值为空。如果我过滤掉NULL值，那么NOT IN运算符在没有WHERE的情况下工作。我仍然不知道为什么：|

---

编辑：对不起，我意识到我把问题搞砸了。问题是在"；用户"；表中的NOT；狗；桌子我最初的代码是：

select count(distinct u.user_guid)
from users u
where u.user_guid not IN (select d.user_guid
from dogs d)

得到0计数的结果。答案应该是2226只有当我添加WHEN条件时，我才得到正确的答案：

select count(distinct u.user_guid)
from users u
where u.user_guid not IN (select d.user_guid
from dogs d
WHERE d.user_guid = u.user_guid);

我仍然不知道为什么WHERE条件会对产生影响

---

问题是：

如何确定"；用户"；桌子他们也在"；狗；桌子

正确答案应该是

select count(u.user_guid)
from users u
where u.user_guid IN (select d.user_guid
from dogs d
where u.user_guid=d.user_guid);

现在我不明白最后一个WHERE过滤器的意义。我认为"；IN"；运算符已经确保只筛选同样存在于d.user_guid中的u.user_guid的值(即"in"运算符将确保有匹配的值)，这与WHERE条件所做的相同(即u.user_guid=d.user_guid)。然而，当我运行这两个代码(有和没有最后一行WHERE)时，结果是不同的。我不确定哪里出了问题：/

Answer 1

下面的查询查找dogs表上存在且不唯一的用户，并且基于dogs表上找到的用户，仅从users表返回唯一值：

select u.user_guid
from users u
where exists ( select d.user_guid
from dogs d
where u.user_guid=d.user_guid
)
group by user_guid
having count(*)=1;

最后一个where条件where u.user_guid=d.user_guid只返回在users表上找到的dogs表中的用户，因此在您的情况下，它应该会产生错误的结果。

如果您只想用count(u.user_guid)更改号码u.user_guid

select count(u.user_guid)
from users u
where exists ( select d.user_guid
from dogs d
where u.user_guid=d.user_guid
)
group by user_guid
having count(*)=1;

Answer 2

嘿，伙计们，我觉得你们把这里的事情搞得太复杂了
如果执行简单联接，则只能获得两个表中的值。如果进行左联接，则第二个表中不匹配的字段将返回null
NB：如果我们在一个可以为null的列上联接，我们可能会得到错误的结果，但这在任何情况下都不好。

create table dogs (d int);
create table user (u int);
insert into dogs values
(1),(2),(3),(4),
(1),(2),(5),(6);
insert into user values
(1),(2),(3),(4),
(1),(2),(7),(8);

SELECT COUNT(DISTINCT d) as du 
FROM dogs join user on d=u;

|du||-：||4 |

<blockquote\

SELECT COUNT(DISTINCT d) as dd
FROM dogs LEFT JOIN user on d=u
WHERE u IS NULL;

>

|dd||-：||2|

db<gt；小提琴这里

Answer 3

如果您想选择dogs表中不可用的唯一用户，那么最好使用not exists。

select count(distinct u.user_guid) unique_users    
from users u     
where not exists (select 1 from dogs d where u.user_guid = d.user_guid);

CCD_ 13和CCD_。下面的链接将解释

NOT IN与NOT EXISTS与LEFT JOIN/IS NULL：MySQL

如果要计算唯一用户，如果任何用户都有多行，则可能需要distinct关键字。例如：count( distinct u.user_guid)。

对于IN，您不需要where u.user_guid=d.user_guid。但我建议使用exists的解决方案

使用IN:

select count( u.user_guid)
from users u
where u.user_guid IN (select d.user_guid
from dogs d);

使用Exists:

select count( u.user_guid)
from users u
where exists IN (select 1
from dogs d
where u.user_guid=d.user_guid);

IN和exists的示例在users表中，user_guid有三个值。1,2和3。2有两次。但在dogs表中，user_guid是2、3和5。2和3在两个表中都是常见的。请检查查询和输出。

模式和插入语句：

create table users(user_guid int);
create table dogs(user_guid int);
insert into users values(1);
insert into users values(2);
insert into users values(2);
insert into users values(3);
insert into dogs values(2);
insert into dogs values(3);
insert into dogs values(5);

查询：使用IN从用户u
中选择count(distinct u.user_guid)unique_users
，其中u.user_guid IN(从dogs d中选择d.user_guid)；

输出：

unique_users
2

好的，所以回答我自己的问题很奇怪，但我发现了IN在没有WHERE条件的情况下工作的原因，而NOT IN绝对需要WHERE条件(在我的查询中)。这是因为；狗；数据集有几个空的user_guid值。

例如。在这里摆弄

CREATE TABLE users (
id INT
);
INSERT INTO users (id) VALUES (1);
INSERT INTO users (id) VALUES (2);
INSERT INTO users (id) VALUES (3);
CREATE TABLE dogs (
id INT
);
INSERT INTO dogs (id) VALUES (3);
INSERT INTO dogs (id) VALUES (4);
INSERT INTO dogs (id) VALUES (NULL);

如果我使用IN而不使用WHERE:

select count(distinct u.id)
from users u
where u.id in (select d.id
from dogs d
);

结果是1(值3)，因为SQL只需要找到"0"的匹配项；3〃；一次，不需要关心包括NULL值在内的所有其他值。但是，如果我使用没有最后一个WHERE的NOT IN，而不是给我2的结果(ID 1和2)，结果是0。

select count(distinct u.id)
from users u
where u.id not in (select d.id
from dogs d);

这是因为SQL必须确保users表中的ID 1和2与dogs表中的任何行不匹配，这意味着它必须比较包括NULL行在内的ALL列。NULL值被认为是未知的，因此它不能与非NULL值进行比较。所以我实际上也可以使用WHERE d.user_guid is not null，它也会返回相同的数字。