SQL query with complex subquery

I have two tables, Foo and Bar. Foo contains a foreign key to Bar’s primary key (bar_id). Bar is structured to allow a parent/child relationship to itself through a foreign key (bar_parent_id) to another record in Bar. This relationship is limited such that any Bar record that has a parent cannot itself be a parent. However, any given parent can have multiple children.

My query needs to select all of the records in Foo that match a given record in Bar as well any of the Bar’s parents, children or siblings. The query below works, but is somewhat slow. Is there any way to structure it so that it will run faster?

SELECT f.field1, f.field2
FROM Foo f
WHERE f.bar_id IN (
    SELECT bar_id
    FROM Bar
    WHERE bar_id = @bar_id OR
    bar_parent_id = @bar_id OR
    bar_id = (SELECT bar_parent_id FROM Bar WHERE bar_id = @bar_id) OR
    bar_parent_id = (SELECT bar_parent_id FROM Bar WHERE bar_id = @bar_id AND bar_parent_id > 0)
)

P.S. This is a simplified version of the real query. It actually has a second, identical subquery to another table that has the same self/parent/child relationship as Bar.

You can try but I’m not sure of the correctness

SELECT
    f.field1, f.field2
FROM
    foo f
    LEFT JOIN bar b
    LEFT JOIN bar bParent
    ON b.parent_id = bParent.bar_id
    LEFT JOIN bar bChildren
    ON b.bar_id = bChildren.Parent_id
WHERE
    b.bar_id = @bar_id
    or
    bParent.bar_id = @bar_id
    or
    bChildren.bar_id = @bar_id
Hello, buddy!稿源:Hello, buddy! (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » SQL query with complex subquery

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录