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.

Solution 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)

Solution 2

Use two different queries, check if synonymtable has rows and run query with inner join else return rows from products table

IF EXISTS (SELECT * FROM @SynonymTable)
BEGIN
  SELECT * FROM Products 
  INNER JOIN @SynonymTable AS A ON ([Products].[Title] LIKE A.[Synonym])
END
ELSE
  SELECT * FROM Products 

Solution 3

a solution would be to not join on the synonym table but to use it in a where clause

not the most elegant code but should work (unless you have a big synonym table then it gets slower)

where ((select count(1) from @SynonymTable) = 0 or 
       (select count(1) from @SynonymTable 
        where [Products].[Title] LIKE @SynonymTable.[Synonym]) > 0 ))

Comments

[Mithrandir]

I might not got the point, but why don't you use a left outer join?

[Massimiliano Peluso]

I think @Mithrandir is right: using a LEFT JOIN it will return all the record matching a provided "Synonym" and also the not matching record with join field NULL

[Nathan]

Great answer and would have done this myself, but the join made up part of a larger query which I am unable to break up.

[user1477388]

IF EXISTS (SELECT 1 FROM @SynonymTable) would be more efficient.