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.
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.
Scalar valued function return single value (not table) so you cannot use joins.
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.
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.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With