Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

It would seem that in general '=' is faster than 'like' when using wildcards. This appears to be the conventional wisdom. However, lets suppose I have a column containing a limited number of different fixed, hardcoded, varchar identifiers, and I want to select all rows matching one of them:

select * from table where value like 'abc%'

and

select * from table where value = 'abcdefghijklmn'

'Like' should only need to test the first three chars to find a match, whereas '=' must compare the entire string. In this case it would seem to me that 'like' would have an advantage, all other things being equal.

This is intended as a general, academic question, and so should not matter which DB, but it arose using SQL Server 2005.

2 Answers

0 votes
by (119k points)
edited by

Using '=' operator is faster than the LIKE operator in comparing strings because '=' operator compares the entire string but the LIKE keyword compares by each character of the string. We can use LIKE to check a particular pattern like column values starting with 'abc' in this case. You can use equal (=) operator if you are looking for a specific observation like 'abcdefghijklmn' in this case.

If you want to learn SQL then sign up for this SQL Certification course that provides instructor-led training, certification, and also job assistance

0 votes
by (1.5k points)

Using the '=' operator is quicker than the LIKE operator in string comparison as '=' compares the whole string while LIKE compares character by character. In this scenario, we have the option to utilize LIKE to examine a specific pattern such as columns that begin with 'abc'. If you are searching for a particular observation, such as 'abcdefghijklmn', you can utilize the equal (=) operator.

Related questions

0 votes
1 answer
0 votes
2 answers
asked May 4, 2023 in SQL by Nandini V (33.9k points)
0 votes
1 answer
asked Nov 21, 2020 in SQL by Appu (6.1k points)
0 votes
1 answer

1.2k questions

2.7k answers

501 comments

693 users

Browse Categories

...