Combining JOINs with OR

Question

I'm not sure how I can optimize this. I need to select from a table, applying a keyword search to multiple columns in various tables:

SELECT  mt.Id
FROM    tbl_MyTable mt
JOIN    tbl_AnotherTable at
ON      mt.ForeignKey = at.Id
WHERE   <some stuff>
        AND (
            mt.Id IN (
                SELECT  mt2.Id
                FROM    tbl_MyTable mt2
                JOIN    @keywordLike kl
                ON      mt2.Name LIKE kl.Keyword
                WHERE   <same stuff as before, but for mt2>
            ) OR
            at.Id IN (
                SELECT  at2.Id
                FROM    tbl_AnotherTable at2
                JOIN    @keywordLike kl
                ON      at2.Name LIKE kl.Keyword
                        OR at2.Widget LIKE kl.Keyword
            ) -- in reality, the "keyword" search is applied to three other tables
        )

@keywordLike is a table variable containing strings to attempt to match against multiple columns in multiple tables.

Note that the Id from tbl_MyTable will be returned any time anything matches the keyword search, not all, which is why I'm not just doing a bunch of JOINs. These tables are very large, and the <some stuff> parts I skipped over is filtering out quite a bit. It seems what I really want is to be able to JOIN...ON..., but with an OR between them, but this is the best alternative I've thought of so far.

Here's some sample data:

[[MyTable]]
Id      Name    ForeignKey
 1      Alice            1
 2      Bob              2

[[AnotherTable]]
Id      Name     Widget
 1      iPhone   Screen
 2      Android  Screen

If @keywordLike contained just the following string: %A%, we'd return:

• 1 (because %A% matches Alice)
• 2 (because %A% matches Android, and Bob's ForeignKey matches that Id

If @keywordLike contained %Alice%, iPhone, we'd return:

• 1 (because %Alice% matches Alice)
• 1 (because %iPhone% matches iPhone and Alice's ForeignKey matches that Id

If @keywordLike contained Screen we'd return:

• 1
• 2

Answer 1

Does this query do what you want?

SELECT mt.Id
FROM tbl_MyTable mt JOIN
     tbl_AnotherTable at
     ON mt.ForeignKey = at.Id
WHERE mt.name LIKE @keywordlike OR
      at.name LIKE @keywordlike OR
      at.widget LIKE @keywordlike;

If so, this will be challenging to make more efficient in SQL Server. One possibility is full text search, but even t that can be tricky across tables.

EDIT:

If @keywordlike is a table variable:

SELECT mt.Id
FROM tbl_MyTable mt JOIN
     tbl_AnotherTable at
     ON mt.ForeignKey = at.Id
WHERE EXISTS (SELECT 1 FROM @keywordlike kl WHERE mt.name LIKE kl.keyword) OR
      EXISTS (SELECT 1 FROM @keywordlike kl WHERE at.name LIKE kl.keyword) OR
      EXISTS (SELECT 1 FROM @keywordlike kl WHERE at.widget LIKE kl.keyword);