Is there any difference between:
SELECT * FROM users WHERE username="davyjones"
and
SELECT * FROM users WHERE username LIKE "davyjones"
(I think I've bungled up the syntax... pardon me for that,
I'm mostly a desktop app development guy)
views:
488answers:
12
+17
A:
LIKE allows partial matching / use of wildcards, while = checks for exact matches.
For example
SELECT * FROM test WHERE field LIKE '%oom';
Will return rows where field value is any of the following:
Zoom, Boom, Loom, Groom
code_burgar
2009-10-01 16:26:50
Thanks for the help -- SQL is really freaking me out -- it ... like ... some odd variant of broken english :O ... and why can't the SQL guys use the standard wildcards ... or maybe the standard regex ops...
Aviral Dasgupta
2009-10-01 16:33:34
It won't return those rows if column is CHAR(5) instead of VARCHAR(5).
Milan Babuškov
2009-10-01 16:39:51
@Milan: It will in MySQL
code_burgar
2009-10-01 16:50:31
@codeburger: MySQL has years before it gets anywhere near being SQL standard compliant ;)
Milan Babuškov
2009-10-02 10:14:19
+2
A:
That will give you the same result. However, LIKE allows wildcards, for example...
SELECT * FROM users WHERE username LIKE 'davy%'
The only syntax problem was double quotes instead of single quotes
Richard
2009-10-01 16:27:00
+4
A:
LIKE allows wildcards like % (any number of characters here) and _ (one character here).
SELECT * FROM users WHERE username LIKE 'joe%'
Selects all usernames starting with joe.
T.J. Crowder
2009-10-01 16:27:21
+7
A:
In that case, there is no difference that would come up in the results. However, it uses a different method for comparision, and the "LIKE" would be much slower.
Check out this for examples of LIKE : http://www.techonthenet.com/sql/like.php
In this case, you still want to use the equals.
Update: Note that there is a crucial difference when it comes to CHAR type columns in which the results will be different. See this answer for more details. When using VARCHAR (presumably the norm), the above are equivalent and equals is to be preferred.
Erich
2009-10-01 16:27:26
Potentially in a stored procedure, however in testing in SQL Server through dynamic SQL, I've noticed it does not optimize this.I've also run a test on Oracle, and it doesn't seem to optimize Dynamic SQL.
Erich
2009-10-01 16:34:05
In what manner would you say that my answer is wrong? In this situation, LIKE and '=' have identical functionality. He really means '=', and thus should. I also pointed out a bit of documentation on how LIKE should be used...Pretty sure I answered the question as much as everyone else.
Erich
2009-10-01 16:42:38
See my answer below, and read up on SQL specs. There is an important difference if you use CHAR columns.
Milan Babuškov
2009-10-01 16:44:32
Ah, I knew of that difference, however I hadn't run into that in a while. I made the assumption he was using VARCHAR, since I (and my company) tend to use it any time the data doesn't have a fixed length.
Erich
2009-10-01 16:46:10
A:
Equals '=' is just for equality. On the other hand, LIKE supports SQL wildcard matching.
So, with LIKE you can do name like '%jones' to get all the names ending in jones. With LIKE, the percent '%' character is anything, length zero or more, and the underscore character, '_', is any one character.
jbourque
2009-10-01 16:27:29
A:
The LIKE condition allows you to use wildcards:
SELECT * FROM suppliers
WHERE supplier_name like 'Hew%';
and Equals = is used for equality matching.
Daniel May
2009-10-01 16:28:42
+1
A:
LIKE searches for a pattern.
/* Returns all users whose username starts with "d" */
SELECT * FROM users WHERE username LIKE 'd%'
/* Returns all users whose username contains "dav" */
SELECT * FROM users WHERE username LIKE '%dav%'
Larsenal
2009-10-01 16:29:03
A:
As far as I know, there is no difference but a time cost to the two selects you wrote. Usually one uses LIKE together with '%', meaning 'any string'. I think there's also a character that can be used with LIKE for 'any character', not sure what that is without googling.
But as your two selects go, the only difference I see is a different run time, since LIKE is used in a regexp-sort-of-fashion.
laura
2009-10-01 16:29:20
+7
A:
As per SQL standard, the difference is treatment of trailing whitespace in CHAR columns. Example:
create table t1 ( c10 char(10) );
insert into t1 values ('davyjones');
select * from t1 where c10 = 'davyjones';
-- yields 1 row
select * from t1 where c10 like 'davyjones';
-- yields 0 rows
Of course, assuming you run this on a standard-compliant DBMS. BTW, this is one the main differences between CHARs and VARCHARs.
Milan Babuškov
2009-10-01 16:31:11
why would the second example yield 0 rows? These two statememts look equivalent to me given your test data
mjmarsh
2009-10-01 16:33:59
because 'test' is stored as 'test' + 4 spaces. SQL standard says that LIKE should not match that, while = should. For VARCHAR columns there is no difference.
Milan Babuškov
2009-10-01 16:35:15
Yet another reason for never using char data type unless you will always have that exact number of characters.
HLGEM
2009-10-01 17:26:29
A:
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 exct you may place 'davyjones' and you may also use "=" in this case
SQL Like
2009-11-29 18:03:12
A:
Like is pattern matching operator and '=' is exact matching operator. i.e. where name like 'W%' it means start with 'W' and after that one or more characters and '=' i.e. where name ='James' this is exact matching
P Sharma
2009-11-29 18:30:48