Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using JOIN statement with CONTAINS function

In SQL Server database I have a View with a lot of INNER JOINs statements. The last join uses LIKE predicate and that's why it's working too slowly. The query looks like :

SELECT *
FROM A INNER JOIN
B ON A.ID = B.ID INNER JOIN
C ON C.ID1 = B.ID1 INNER JOIN
...........................
X ON X.Name LIKE '%' + W.Name  + '%' AND
            LIKE '%' + W.Name2  + '%' AND
            LIKE '%' + W.Name3  + '%'

I want to use CONTAINS instead of LIKE as :

SELECT *
FROM A INNER JOIN
B ON A.ID = B.ID INNER JOIN
C ON C.ID1 = B.ID1 INNER JOIN
...........................
X ON CONTAINS(X.Name, W.Name) AND
     CONTAINS(X.Name, W.Name2) AND
     CONTAINS(X.Name, W.Name3)

I know that CONTAINS is working faster than LIKE and also that can't use CONTAINS in JOIN statements. Is there any workaround in this case or suggestion? Thanks in advance.

like image 205
Alexander Avatar asked Jul 03 '13 12:07

Alexander


People also ask

Can we use joins in with clause?

Always put the join conditions in the ON clause if you are doing an INNER JOIN . So, do not add any WHERE conditions to the ON clause, put them in the WHERE clause.

Can we use scalar function in join?

Scalar valued function return single value (not table) so you cannot use joins.

Is it better to filter in join or WHERE clause?

In terms of readability though, especially in complex queries that have multiple joins, it is easier to spot join conditions when they are placed in the ON clause and filter conditions when they are placed in the WHERE clause.

What does (+) mean in SQL joins?

The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins. An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key. If there's no matching row, return null.


2 Answers

It's not that CONTAINS can't be used in joins.

You just can't use columns as a second parameter of CONTAINS - see MSDN - CONTAINS (Transact-SQL)

CONTAINS
( { column_name | ( column_list ) | * } 
  ,'<contains_search_condition>'     
[ , LANGUAGE language_term ]
) 

However, you can use a variable as a search condition, so you can use a cursor and then get all data you need. Here is some very rough example:

declare @Name nvarchar(max)

declare @Temp_A table(Name nvarchar(max))
declare @Temp_B table(Name nvarchar(max))

--=============================================================================================
insert into @Temp_A (Name)
select 'Test'

insert into @Temp_B (Name)
select 'aaaTestaaa'

--=============================================================================================
-- Query 1 - LIKE
--=============================================================================================
select *
from @Temp_A as A
    inner join @Temp_B as B on B.Name like '%' + A.Name + '%'

--=============================================================================================
-- Query 2 - CONTAINS
--=============================================================================================
declare table_cursor cursor local fast_forward for
    select distinct Name from @Temp_A
open table_cursor
while 1 = 1
begin
    fetch table_cursor into @Name
    if @@fetch_status <> 0 break

    select * from @Temp_B where contains(Name, @Name)
end
close table_cursor
deallocate table_cursor
like image 78
Roman Pekar Avatar answered Sep 21 '22 08:09

Roman Pekar


CONCAT works perfect, I have tested it with PostgreSQL

SELECT * FROM TABLE_ONE AS a INNER JOIN TABLE_TWO AS b ON b.field LIKE CONCAT('%', CONCAT(a.field, '%'));

Please refer to similar answer here

like image 45
geobudex Avatar answered Sep 19 '22 08:09

geobudex