mysql - 如何在 SQL 中显示 NULL 记录

Question

我得到了包含 3 个表的简单数据库:

Galaxy(GalaxyID INTEGER PRIMARY KEY, Name TEXT);

Planet(PlanetID INTEGER PRIMARY KEY, GalaxyID INTEGER, Name TEXT, Population INTEGER);

Continent(ContinentID INTEGER PRIMARY KEY, PlanetID INTEGER,Name TEXT);

INSERT INTO Galaxy VALUES 
(1, "MILKY WAY"),
(2, "IC 1101"),
(3, "GN-z11");

INSERT INTO Planet VALUES 
(1,1, "Earth", 100000),
(2,1, "Moon", 0),
(3,1, "Saturn", 0),
(4,2, "IC-123", 0),
(5,2, "IC-124", 1),
(6,2, "IC-126", 4),
(7,3, "GN-24", 1),
(8,3, "GN-26", 1),
(9,3, "GN-28", 4);

INSERT INTO Continent VALUES 
(1,1, "Europe"),
(2,4, "UnnamedContinent1"),
(3,4, "UnnamedContinent2");

我想选择根本没有大陆的星系:

SELECT Galaxy.Name, Planet.name
FROM Galaxy
LEFT OUTER JOIN Planet, Continent
ON Continent.PlanetID = Planet.PlanetID AND Galaxy.GalaxyID = Planet.GalaxyID
GROUP BY Galaxy.Name
HAVING COUNT(ContinentID) IS NULL

此查询将不起作用，但如果我给出 HAVING COUNT(ContinentID) IS NOT NULL，它将显示所有带大陆的星系(IC 1101 和 MILKY WAY)如何显示 NULL 记录，如:GN-z11 NULL

Answer 1

不需要COUNT，也不需要having，也不需要group by，只需添加额外的continent左连接表:

SELECT DISTINCT g.Name GalaxyName, p.name PlanetName
FROM Galaxy AS g
LEFT JOIN Planet AS p ON g.GalaxyID = p.GalaxyID
LEFT JOIN Continent AS c ON c.PlanetID = p.PlanetID 
WHERE c.PlanetID IS NULL;

Demo