Папки с AVI-файлами и пустые папки

36. Найти папки с AVI-файлами или без файлов

Условие задачи:
Даны таблицы folder и file.

Необходимо вывести названия папок, которые удовлетворяют хотя бы одному условию:

содержат хотя бы один файл с расширением .avi;
не содержат ни одного файла.

Код:

CREATE TABLE folder (
    id uuid PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE file (
    id uuid PRIMARY KEY,
    name text NOT NULL,
    folder_id uuid NOT NULL,
    CONSTRAINT fk__file__folder_id
        FOREIGN KEY (folder_id) REFERENCES folder(id)
);

Спойлеры к решению

Подсказки

💡 Для условия «есть хотя бы один AVI-файл» удобно использовать EXISTS.
💡 Для пустой папки можно проверить отсутствие любых файлов через NOT EXISTS.
💡 Альтернативный вариант — LEFT JOIN с проверкой file.id IS NULL.
💡 При использовании JOIN может потребоваться DISTINCT.

Решение

Вариант через EXISTS и NOT EXISTS:

SELECT f.name
FROM folder f
WHERE EXISTS (
    SELECT 1
    FROM file fi
    WHERE fi.folder_id = f.id
      AND fi.name LIKE '%.avi'
)
OR NOT EXISTS (
    SELECT 1
    FROM file fi
    WHERE fi.folder_id = f.id
);

Первая часть проверяет, есть ли в папке хотя бы один AVI-файл:

EXISTS (
    SELECT 1
    FROM file fi
    WHERE fi.folder_id = f.id
      AND fi.name LIKE '%.avi'
)

Вторая часть выбирает папки, в которых вообще нет файлов:

NOT EXISTS (
    SELECT 1
    FROM file fi
    WHERE fi.folder_id = f.id
)

Альтернативный вариант через LEFT JOIN:

SELECT DISTINCT f.name
FROM folder f
LEFT JOIN file fi
    ON fi.folder_id = f.id
WHERE fi.name LIKE '%.avi'
   OR fi.id IS NULL;

LEFT JOIN сохраняет пустые папки, а DISTINCT убирает повторения, если в одной папке находится несколько AVI-файлов.