Left Joining未返回左表中的所有记录

Question

我想通过对两个表执行join来获得计数，我正在执行group by和left join。

但是，如果这个记录与另一张表不匹配，我就不会得到所有的一行。

在给定的查询中：

create table UserTable (
Id integer not null,
Name varchar(12) not null
);
insert into UserTable  values (1,  'A B');
insert into UserTable  values (2,  'A C');
insert into UserTable  values (3,  'A C A C');
insert into UserTable  values (4,  'A C C');
insert into UserTable  values (5,  'A C B');
insert into UserTable  values (6,  'A C C');
insert into UserTable  values (7,  'A C D');
insert into UserTable  values (8,  'A C E');
insert into UserTable  values (9,  'A C F');

create table LogTable (
LogId integer not null,
Username varchar(12) not null,
Event varchar(12) not null
);
insert into LogTable  values (1, 'A C A C', 'Read');
insert into LogTable  values (2, 'A C F', 'Write');
insert into LogTable  values (3, 'A C F', 'Read');
insert into LogTable  values (4, 'A C C', 'Update');
insert into LogTable  values (5,'A C C', 'Read');
insert into LogTable  values (6,'A C F', 'Read');
insert into LogTable  values (7,'A C F', 'Update');
insert into LogTable  values (7,'A C F', 'Write');
insert into LogTable  values (7,'A C E','Update');
insert into LogTable  values (7,'A C F', 'Delete');
insert into LogTable  values (10,'A C B', 'Delete');
insert into LogTable  values (11, 'A C F','Copy');
insert into LogTable  values (12, 'A C B','Read');
insert into LogTable  values (13, 'A C F','Update');
insert into LogTable  values (14, 'A C F','Copy');
insert into LogTable  values (15, 'A C F','Read');
insert into LogTable  values (16, 'A C F','Update');
insert into LogTable  values (17, 'A C F','Copy');
insert into LogTable  values (18, 'A C C','Read');
insert into LogTable  values (19, 'A C D','Update');

我正在做：

SELECT COUNT(*) as Read,UT.Name, UT.Id 
FROM UserTable UT 
LEFT JOIN LogTable LT ON LT.Username = UT.Name 
WHERE LT.Event = 'Read'
GROUP by UT.Name, UT.Id

我想要的是从UserTable中获取所有记录，如果LogTable中不存在

如果不存在，则将ReadCount设为0。

为什么Left Joining没有返回此查询中UserTable的所有记录？

期望输出：

Id  |   Name    |   ReadCount
-----------------------------
1   |   A B     |   0
2   |   A C     |   0
3   |   A C A C |   1
4   |   A C C   |   2
5   |   A C B   |   1
6   |   A C C   |   2
7   |   A C D   |   0
8   |   A C E   |   0
9   |   A C F   |   3

Answer 1

您需要将条件移动到ON子句中。但是，您不需要COUNT(*)，因为您要计算匹配的数量。那就是：

SELECT UT.Name, UT.Id , COUNT(LT.UserName) as num_read
FROM UserTable UT LEFT JOIN
LogTable LT
ON LT.Username = UT.Name AND LT.Event = 'Read'
GROUP by UT.Name, UT.Id;

也就是说，这种类型的查询通常比相关的子查询更快：

select ut.*,
(select count(*)
from logtable lt
where lt.username = ut.name and lt.event = 'Read'
) as num_read
from usertable ut;

特别地，这可以利用logtable(username, event)上的索引。

Answer 2

SELECT COUNT(*) as Read,UT.Name, UT.Id 
FROM UserTable UT 
LEFT JOIN LogTable LT ON LT.Username = UT.Name 
WHERE LT.Event = 'Read'
GROUP by UT.Name, UT.Id

您使用了WHERE LT.Event = 'Read'，这使它成为内部联接，这就是为什么您没有获得左表的所有行

SELECT COUNT(*) as Read,UT.Name, UT.Id 
FROM UserTable UT 
LEFT JOIN LogTable LT ON LT.Username = UT.Name 
and  LT.Event = 'Read'
GROUP by UT.Name, UT.Id

移动条款中的条件