代码之家 › 专栏 › 技术社区 › user391986

具有空值的SQL联接

join mysql sql

user391986 · 技术社区 · 15 年前

当我在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";

2 回复 | 直到 15 年前

Daniel Vassallo 15 年前

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

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)

Eton B. 15 年前

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

推荐文章

Bard.Mus · 迁移后的数据库字符集环境

1 年前

tinkerr · 如何在2025年在MacOS上安装MySQL 5.7

1 年前