SQL‑запрос для получения строк с уникальными значениями поля

Условие

В базе присутствуют две связанные таблицы — works и materials. Необходимо составить запрос, который вернёт только те записи из таблицы materials, у которых значение столбца name встречается единожды.

CREATE TABLE works
(
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE materials
(
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    quantity INTEGER,
    work_id BIGINT,
    FOREIGN KEY (work_id) REFERENCES works(id) ON DELETE CASCADE
);

INSERT INTO works(id, name)
VALUES (1, 'work 1'),
       (2, 'work 2'),
       (3, 'work 3');

INSERT INTO materials(id, name, quantity, work_id)
VALUES (1, 'mat 1', 10, 1),
       (2, 'mat 2', 20, 2),
       (3, 'mat 2', 15, 2),
       (4, 'mat 4', 30, 2);

Ответ

Посчитайте частоту каждого name и оставьте строки с частотой 1. DISTINCT здесь не подходит: он убирает повторы, а не исключает все строки повторяющихся имён.

В PostgreSQL удобно использовать оконную функцию:

SELECT id, name, quantity, work_id
FROM (
    SELECT materials.*,
           COUNT(*) OVER (PARTITION BY name) AS name_count
    FROM materials
) AS counted
WHERE name_count = 1
ORDER BY id;

На приведённых данных вернутся строки с id 1 и 4. Все NULL-имена в таком запросе относятся к одной группе; если NULL нужно исключить полностью, добавьте явное условие. Таблица works для этого результата не нужна.