Ignore SQL INNER JOIN if there are no records to join?

Question

I have the following Join

INNER JOIN @SynonymTable AS A ON ([Products].[Title] LIKE A.[Synonym])

The @SynonymTable table variable contains (if needed) a list of items terms such as:

%shirt%
%blouse%
%petticoat%

These are all based on a list of a synonyms for a particular keyword search, such as the term 'shirt' - from this I can then find all items that may be related, etc. The problem is that if the there is no keyword supplied the query obviously does not join anything.

Is there anyway to eliminate the join or return all items if there are no items in the synonym table?

I've found posts such as Bypass last INNER JOIN in query but am unable to get it to work for my scenario?

Any help or advice would be great.

Answer 1

You can use one select like this:

SELECT * FROM Products 
LEFT JOIN @SynonymTable AS A ON ([Products].[Title] LIKE A.[Synonym])
WHERE A.[Synonym] IS NOT NULL 
      OR NOT EXISTS (SELECT B.[Synonym] FROM @SynonymTable B)