CODEWITHSUNDEEP
sqlsql-server-2008t-sql
bapi
bapi

Reputation: 1943

Between "like" and "=" Which one is more approprite and why in TSQL

I'm writing a update query to update some blank value like following:

UPDATE TA
SET TA.Trust_Desc = TB.Trust_Desc
FROM TB
INNER JOIN TA
ON TA.TrustID = TB.TrustID 
AND TA.Trust_Desc like ''

and

UPDATE TA
SET TA.Trust_Desc = TB.Trust_Desc
FROM TB
INNER JOIN TA
ON TA.TrustID = TB.TrustID 
AND TA.Trust_Desc = ''

Both are working. But I want to know which one("like" or "=") is more approprite and why?

Upvotes: 0

Views: 76

Answers (5)

Bacon Bits
Bacon Bits

Reputation: 32145

LIKE is a logical operator, and is used for pattern matching strings.

= is a comparison operator, and is use for exactly matching any two items of the same (or similar) datatypes.

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

= for exact matches

I would add to the condition 

UPDATE TA
   SET TA.Trust_Desc = TB.Trust_Desc
  FROM TB
 INNER JOIN TA
    ON TA.TrustID = TB.TrustID 
   AND TA.Trust_Desc  = '' 
   AND TB.Trust_Desc <> ''

Upvotes: 0

SAS
SAS

Reputation: 4035

Equals operator is better if you know the exact value to match, since it will allow indexes to be used. Like is more likely to be slower (unless you exclusively search using 'abc%', where an index can be used).

Upvotes: 2

Michal Krasny
Michal Krasny

Reputation: 5916

AND TA.Trust_Desc like ''

This statement decides if TA.Trust_Desc corresponds with pattern ''. In your case it is no pattern (does not make much sense). This operation may be quite expensive, use '=' when possible.

AND TA.Trust_Desc = ''

This is statement compares TA.Trust_Desc with string ''. It is generally faster operation, so use this one, it also makes more sense.

Pattern is a string using wildards like % for any string. For example you can have a table with these three strings. 'Hello World' 'Hi World' 'Hello people'

Statement column like 'Hello%' will be true for 'Hello World' and 'Hello people' Statement column like '%World' will be true for 'Hello World' and 'Hi World'

Upvotes: 2

Madara Uchiha
Madara Uchiha

Reputation: 126

Like gets you to work with wild card operators, you may use it in your case for like 'davyjon%' to get all the results starting with davyjon, and to get the exact you may place 'davyjones' and you may also use = in this case

Upvotes: 0

Related Questions