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

sql-server tsql inner-join
Nathan picture Nathan · Jan 3, 2013 · Viewed 16.3k times · Source

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

András Ottó picture András Ottó · Jan 3, 2013

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)

Related questions

Is having an 'OR' in an INNER JOIN condition a bad idea?

In trying to improve the speed of an immensely slow query (several minutes on two tables with only ~50,000 rows each, on SQL Server 2008 if it matters), I narrowed down the problem to an OR in my inner join, as in: …

Read More
Opposite Of An Inner Join Query

can someone help me write sql for a scernerio like this: Table 1 2 columns: ID, Name Table 2 2 columns: ID, Name I want a query to show names from Table 1 that are not in table 2. So filter out all the names in …

Read More
Conditional Inner Join

I want to be able to inner join two tables based on the result of an expression. What I've been trying so far: INNER JOIN CASE WHEN RegT.Type = 1 THEN TimeRegistration ELSE DrivingRegistration AS RReg ON RReg.RegistreringsId = R.Id …

Read More