Fearless Joins Companion SQL

0_schema.sql

CREATE TABLE rumours
(
    id          BIGSERIAL PRIMARY KEY,
    description TEXT        NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE tidbits
(
    id         BIGSERIAL PRIMARY KEY,
    factoid    TEXT        NOT NULL,
    rumour_id  BIGINT REFERENCES rumours (id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE sources
(
    id         BIGSERIAL PRIMARY KEY,
    name       VARCHAR     NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE rumour_sources
(
    id          BIGSERIAL PRIMARY KEY,
    rumour_id   BIGINT REFERENCES rumours (id),
    source_id   BIGINT REFERENCES sources (id),
    supplied_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (rumour_id, source_id)
);

CREATE TABLE tidbit_sources
(
    id          BIGSERIAL PRIMARY KEY,
    tidbit_id   BIGINT REFERENCES tidbits (id),
    source_id   BIGINT REFERENCES sources (id),
    supplied_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (tidbit_id, source_id)
);


CREATE TABLE labels
(
    id        BIGSERIAL PRIMARY KEY,
    name      VARCHAR NOT NULL,
    parent_id BIGINT REFERENCES labels (id),
    EXCLUDE USING btree (lower(name) WITH =)
);

CREATE TABLE numbers
(
    letter CHARACTER,
    num    INTEGER
);

CREATE TABLE literals
(
    num  INTEGER,
    word VARCHAR
);


INSERT INTO numbers (letter, num)
VALUES ('a', 1),
       ('b', 2);

INSERT INTO literals (num, word)
VALUES (1, 'one'),
       (3, 'three');

1_queries.sql

SELECT rumours.description, s.name
FROM rumours
         INNER JOIN rumour_sources rs ON rumours.id = rs.rumour_id
         INNER JOIN sources s ON rs.source_id = s.id;

SELECT rumours.description, s.name
FROM rumours
         LEFT OUTER JOIN rumour_sources rs ON rumours.id = rs.rumour_id
         LEFT OUTER JOIN sources s ON rs.source_id = s.id
WHERE rs.id IS NULL;

SELECT *
FROM rumours
         LEFT OUTER JOIN rumour_sources rs ON 1 = 1;


SELECT rumours.id, rumours.description, rs.id, rs.supplied_at
FROM rumours
         LEFT OUTER JOIN rumour_sources rs ON rumours.id = rs.rumour_id
    AND rs.supplied_at < '2020-07-31';


SELECT rumours.id, rumours.description, ARRAY_agg(rs.source_id) AS source_ids
FROM rumours
         INNER JOIN rumour_sources rs ON rumours.id = rs.rumour_id
GROUP BY rumours.id, rumours.description;