verkter verkter - 6 months ago 20
SQL Question

SQL - UNION, UNION ALL, INTERSECT, EXCEPT

I am learning SQL with GalaXQL and can't figure out the following question:


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.


What would be a query that would accomplish this using UNION, UNION ALL, INTERSECT, EXCEPT?
Please digest this in your answer as I am a beginner.
Thank you in advance!

Here is the database schema:

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);


Here is my query:

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

Answer

Easiest is to start with exists/not exists:

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
)

If you want to express this in terms of union/intersect, let A be all starids from planet, and let B be the starids * 3. Since both of these must exists we are talking intesection

SELECT starid
FROM planets
INTERSECT
SELECT 3*starid
FROM planets

From that set we want to exclude starids * 2. That is all elements but starids * 2. This is known as EXCEPT or MINUS:

SELECT starid
FROM planets
EXCEPT
SELECT 2*starid
FROM planets

Since the result should belong to both of these sets we once again apply intersection:

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

Did that help?