SQL - 并集、并集全部、交集、异常(exception)

Question

我正在使用 GalaXQL 学习 SQL，但无法解决以下问题:

Build a query which returns starids from planets. The starids should be selected so that for each starid (x) in the list: - there should exist a planet with a starid that's three times x but - there should not exist a planet with starid two times x. Only use starid:s from the planets table.

使用 UNION、UNION ALL、INTERSECT、EXCEPT 完成此操作的查询是什么？请在您的回答中消化这一点，因为我是初学者。预先感谢您!

这是数据库架构:

CREATE TABLE stars (starid INTEGER PRIMARY KEY,
                    name TEXT,
                    x DOUBLE NOT NULL,
                    y DOUBLE NOT NULL,
                    z DOUBLE NOT NULL,
                    class INTEGER NOT NULL,
                    intensity DOUBLE NOT NULL);

CREATE TABLE hilight (starid INTEGER UNIQUE);

CREATE TABLE planets (planetid INTEGER PRIMARY KEY,
                      starid INTEGER NOT NULL,
                      orbitdistance DOUBLE NOT NULL,
                      name TEXT,
                      color INTEGER NOT NULL,
                      radius DOUBLE NOT NULL);

CREATE TABLE moons (moonid INTEGER PRIMARY KEY,
                    planetid INTEGER NOT NULL,
                    orbitdistance DOUBLE NOT NULL,
                    name TEXT,
                    color INTEGER NOT NULL,
                    radius DOUBLE NOT NULL);

CREATE INDEX planets_starid ON planets (starid);
CREATE INDEX moons_planetid ON moons (planetid);

这是我的查询:

SELECT planets.starid
FROM planets
UNION
SELECT starid*3 FROM planets
EXCEPT
SELECT starid*2 FROM planets

Answer 1

最简单的方法是从存在/不存在开始:

SELECT starid
FROM planets p1
WHERE EXISTS (
    SELECT 1 
    FROM planets p2
    WHERE p2.starid = 3*p1.starid
)
AND NOT EXISTS (
    SELECT 1 
    FROM planets p3
    WHERE p3.starid = 2*p1.starid
)

如果你想用并集/相交来表达这一点，让 A 代表来自行星的所有恒星，并让 B 代表恒星 * 3。由于这两者都必须存在，所以我们正在讨论相交

SELECT starid
FROM planets
INTERSECT
SELECT 3*starid
FROM planets

从该集合中，我们要排除 starids * 2。这是除 starids * 2 之外的所有元素。这称为 EXCEPT 或 MINUS:

SELECT starid
FROM planets
EXCEPT
SELECT 2*starid
FROM planets

由于结果应该属于这两个集合，我们再次应用交集:

(SELECT starid
FROM planets
INTERSECT
SELECT 3*starid
FROM planets)
INTERSECT
(SELECT starid
FROM planets
EXCEPT
SELECT 2*starid
FROM planets)

这有帮助吗？