SQL query with complex subquery

Question

I have two tables: Foo and Bar. Foo contains the foreign key for the primary key Bar (bar_id). The bar is structured to allow the parent / child relationship through a foreign key (bar_parent_id) to another record in the bar. This ratio is limited so that any Bar record that has a parent cannot be a parent. However, any parent can have multiple children.

My request is to select all the entries in Foo that match the given entry in Bar, as well as any of the parents, children or siblings. The following is a query, but it is somewhat slow. Is there a way to structure it so that it works 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. . , self/parent/child, Bar.

Answer 1

,

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  

Answer 2

:

SELECT f.field1, f.field2
  FROM Foo f
 WHERE EXISTS(SELECT NULL
                FROM BAR b
               WHERE b.bar_id = f.bar_id
                 AND (    @bar_id IN (b.bar_id, b.bar_parent_id)
                       OR EXISTS(SELECT NULL
                                   FROM BAR x
                                  WHERE x.bar_parent_id = b.bar_id
                                    AND x.bar_id = @bar_id)
                       OR EXISTS(SELECT NULL
                                   FROM BAR y
                                  WHERE y.bar_parent_id = b.bar_parent_id
                                    AND y.bar_id = @bar_id
                                    AND y.bar_parent_id > 0))

Answer 3

ORs often slow down SQL queries. An alternative that may work better is to combine the result of individual queries, for example:

SELECT f.field1, f.field2 FROM Foo f where f.bar_id = @bar_id
UNION
SELECT f.field1, f.field2 FROM Foo f 
JOIN Bar b on f.bar_id = b.bar_id and @bar_id = b.bar_parent_id
UNION
SELECT f.field1, f.field2 FROM Foo f 
JOIN Bar bsib on f.bar_id = bsib.bar_id 
JOIN Bar b on bsib.bar_parent_id = b.bar_parent_id and @bar_id = b.bar_id
UNION
SELECT f.field1, f.field2 FROM Foo f 
JOIN Bar bpar on bpar.bar_id = f.bar_id 
JOIN Bar b on bpar.bar_id = b.bar_parent_id and @bar_id = b.bar_id