CODEWITHSUNDEEP
sqlsql-serverjoinsubset
DMX David Cardinal
DMX David Cardinal

Reputation: 659

SQL Select or join two separate subsets of a table

Say I have a table Element in my SQL Server database with, we'll suppose, nothing other than an Id column.

I also have tables Foo and Bar, which each have a many-to-many relationship with Element, such as I have some tables FooElement (FkId INT, FkFoo INT) and BarElement (FkId INT, FkBar INT) tables to handle the relationship. Note that Elements can be either linked to a Foo, a Bar, both or none.

If I want to find which Elements are linked to a Foo, I can easily find them using

SELECT id FROM Element RIGHT JOIN FooElement

And I can easily do the same for BarElements.

Here is my question: what is the best way to find which Elements are either linked to a Foo or a Bar? Below is my current solution, but I was wondering if there was a better option, in particular performance-wise, as all three tables, especially Element will be extremely packed with data.

SELECT id 
    FROM Element e
    LEFT JOIN FooElement fe ON fe.FkId = e.Id
    LEFT JOIN BarElement be ON be.FkId = e.Id
    WHERE NOT (FkFoo IS NULL AND FkBar IS NULL)

Upvotes: 1

Views: 705

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I would use exists:

SELECT e.id 
FROM Element e
WHERE EXISTS (SELECT 1
              FROM FooElement fe 
              WHERE fe.FkId = e.Id
             ) OR
      EXISTS (SELECT 1
              FROM BarElement be 
              WHERE be.FkId = e.Id
             );

With indexes on FooElement(FkId) and BarElement(FkId), I would expect this to have comparable or better performance than most alternatives.

Upvotes: 1

Related Questions