SQL 加入空值

Question

当我没有在 myComputer 表中设置 ProcessorID 时，我的查询不起作用当我在 ProcessorID 中分配了值时，如何进行查询以显示 ProcessorName 或在没有分配值时显示 NULL？

CREATE TABLE myProcessor
(    
 ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
 Name VARCHAR(255) NOT NULL
) ENGINE=InnoDB;  

INSERT INTO myProcessor (Name) VALUES ("xeon");

CREATE TABLE myComputer 
(
 ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
 Name VARCHAR(255) NOT NULL,
 ProcessorID INT DEFAULT NULL,
 FOREIGN KEY (ProcessorID) REFERENCES myProcessor(ID) ON DELETE CASCADE
) ENGINE=InnoDB;  

INSERT INTO myComputer (Name) VALUES ("Newton");

SELECT p.Name as ProcessorName, c.Name as ComputerName
FROM myComputer c, myProcessor p 
WHERE c.ProcessorID = p.ID
AND c.Name = "Newton";

如果尚未设置 processorID，上述选择查询当前返回 null。

Answer 1

如果计算机没有分配处理器，您当前的查询将不会返回任何行，如您提供的示例所示。

您可能想要使用 left outer join ，而不是隐含的 inner join您正在使用:

SELECT     p.Name as ProcessorName, c.Name as ComputerName
FROM       myComputer c
LEFT JOIN  myProcessor p ON (c.ProcessorID = p.ID)
WHERE      c.Name = "Newton";

返回:

+---------------+--------------+
| ProcessorName | ComputerName |
+---------------+--------------+
| NULL          | Newton       |
+---------------+--------------+
1 row in set (0.00 sec)