当我没有在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,则上述select查询当前返回null。

最佳答案

如果计算机未分配处理器,则当前查询不返回行,如您提供的示例所示。
您可能希望使用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)

关于sql - 具有空值的SQL连接,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/3562889/

10-09 14:34