SQL query with complex subquery

Question

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.

Answer 1

Try this:

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 2

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  

Answer 3

ORs often slow down SQL queries. An alternative that may perform better is to union the result of separate queries, like so:

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