MySQL left join 没有给我我所期望的

Question

我需要一些关于左连接语句的帮助，该语句没有做我认为应该做的事情，可能是错误的。

有两个表:

光盘:

CREATE TABLE `cd` (
  `itemID` int(11) NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  `artist` text NOT NULL,
  `genre` text NOT NULL,
  `tracks` int(11) NOT NULL,
  PRIMARY KEY (`itemID`)
)

贷款

CREATE TABLE `loans` (
  `itemID` int(11) NOT NULL,
  `itemType` varchar(20) NOT NULL,
  `userID` int(11) NOT NULL,
  `dueDate` date NOT NULL,
  PRIMARY KEY (`itemID`,`itemType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我想使用左连接选择所有不在贷款中的 cd，然后选择 dueDate 为 null

select 
    t.itemID, 
    t.artist as first, 
    t. title as second, 
    (select AVG(rating) from ac9039.ratings where itemType = 'cd' and itemId = t.itemID) as `rating avarage`, 
    (select COUNT(rating) from ac9039.ratings where itemType = 'cd' and itemId = t.itemID) as `number of ratings` 
from 
    cd t left join loans l 
    on t.itemID = l.itemID 
where l.itemType = 'cd' and l.dueDate is null;

然而，即使 cd 中有很多行的 itemID 不在贷款中，这个表也会返回一个空表

现在我明白左连接应该保留右侧并用空值填充左侧的列但事实似乎并非如此，anbyone可以启发我吗？

Answer 1

您的 WHERE 条件导致错误。如果 L.DueDate IS NULL 为 true，则 L.ItemType = 'cd' 将始终返回 false。 (您的所有字段都是 NOT NULL，因此如果没有匹配的记录，DueDate 只能为 NULL，但在这种情况下 ItemType 字段也将为 NULL)。

另一点是您的查询在语义上不正确。您正尝试从 cd 表中获取记录，其中 loans 表不包含任何包含 dueDates 的行。第二个表充当条件，因此它应该转到 WHERE 条件。

考虑使用 EXISTS 语句来实现您的目标:

SELECT 
    t.itemID, 
    t.artist as first, 
    t. title as second, 
    (select AVG(rating) from ac9039.ratings where itemType = 'cd' and itemId = t.itemID) as `rating avarage`, 
    (select COUNT(rating) from ac9039.ratings where itemType = 'cd' and itemId = t.itemID) as `number of ratings` 
FROM 
    cd t
WHERE
    NOT EXISTS (SELECT 1 FROM loans l WHERE t.itemID = l.itemID AND L.itemType = 'cd')

根据您的数据模型，您必须向子查询添加另一个条件以过滤掉那些现在已过期的记录(dueDate 早于当前时间)

当您不删除过时的贷款记录时就是这种情况。

NOT EXISTS (SELECT 1 FROM loans l WHERE t.itemID = l.itemID AND AND L.itemType = 'cd' l.dueDate > NOW())