如何使用子查询作为黑名单排除SQL查询结果：文件浏览器文件夹取消收藏场景的实现与优化问询

我正在开发一款文件浏览器，通过存储文件夹名称的表来维护收藏状态。收藏功能采用级联机制：显式收藏的路径下的所有子文件夹会被自动设置为隐式收藏状态。

现有数据表 #

我的表结构及数据如下：

问题描述 #

添加收藏的逻辑很简单：将指定路径下所有文件夹的is_implicit_favorite设为1。但在实现取消文件夹收藏的反向操作时遇到了困难：取消收藏某文件夹时，需要跳过那些应保持隐式收藏状态的子文件夹。举个例子：取消收藏foo/时，应保留foo/bar/及其子文件夹的隐式收藏状态，而取消foo/bar2/及其子文件夹的隐式收藏状态。

已实现的解决方案 #

单个已收藏子文件夹版本 #

我先实现了一个仅支持单个已收藏子文件夹场景的查询：

SELECT DISTINCT folders.foldername 
FROM folders 
JOIN ( 
    SELECT folders.foldername 
    FROM folders 
    WHERE folders.foldername LIKE 'foo/' || '%' AND folders.is_favorite = 1 
) favs ON folders.foldername NOT LIKE favs.foldername || '%' 
WHERE folders.foldername LIKE 'foo/' || '%'

这个查询的预期结果是：

支持多个已收藏子文件夹版本 #

后来感谢他人提示使用EXCEPT运算符，我修改出了支持多个已收藏子文件夹的版本：

SELECT folders.foldername 
FROM folders 
WHERE folders.foldername LIKE 'foo/' || '%' 
EXCEPT 
SELECT folders.foldername 
FROM folders 
INNER JOIN ( 
    SELECT folders.foldername 
    FROM folders 
    WHERE folders.foldername <> 'foo/' AND folders.is_favorite = 1 
) favs ON folders.foldername LIKE favs.foldername || '%'

我很高兴能完全用纯SQL实现该逻辑，原本一度考虑采用SQL与Node.js的混合方案。现在想寻求针对这个查询的优化建议，感谢各位！

内容的提问来源于stack exchange，提问作者Bjørn Næsby