MySQL Server Forum - MYSQL Syntax for NOT in where clause

Author

MYSQL Syntax for NOT in where clause

sunny076@yahoo.com

2005-08-08, 8:23 pm

Hi,

I am confused with the syntax for NOT in MYSQL where clause and wonder
if an expert in MYSQL can enlighten me. There are possibly two places
NOT can go in:

select * from employee_data where NOT employee_id LIKE 'A%'

select * from employee_data where employee_id NOT LIKE 'A%'

In the first case, there was no data returned and no error message on
syntax either. But the second case returned the correct result. But I
am confused because if the first case is not right, the syntax error
should be thrown. Am I missing something?

Thank you in advance,

Sunny

Bill Karwin

2005-08-08, 8:23 pm

sunny076@yahoo.com wrote:
> Hi,
>
> I am confused with the syntax for NOT in MYSQL where clause and wonder
> if an expert in MYSQL can enlighten me. There are possibly two places
> NOT can go in:
>
> select * from employee_data where NOT employee_id LIKE 'A%'
>
> select * from employee_data where employee_id NOT LIKE 'A%'
>
> In the first case, there was no data returned and no error message on
> syntax either. But the second case returned the correct result. But I
> am confused because if the first case is not right, the syntax error
> should be thrown. Am I missing something?

On page http://dev.mysql.com/doc/mysql/en/s...functions.html:

expr NOT LIKE pat [ESCAPE 'escape-char']
This is the same as NOT (expr LIKE pat [ESCAPE 'escape-char']).

So the two forms you used are correct syntax, and according to the
above, _should_ yield the same results. I tried it on my test database,
and it seems to work as expected:

CREATE TABLE `t` (`c` varchar(10) default NULL);
INSERT INTO `t` VALUES ('A123'),('B123'),(N
ULL);

select * from `t` where `c` like 'A%';
A123

select * from `t` where NOT `c` like 'A%';
B123

Can you give a example of the data in the table, and the return sets
that are not equal?

Regards,
Bill K.

Bill Karwin

2005-08-08, 8:23 pm

Bill Karwin wrote:
> On page http://dev.mysql.com/doc/mysql/en/s...functions.html:
>
> expr NOT LIKE pat [ESCAPE 'escape-char']
> This is the same as NOT (expr LIKE pat [ESCAPE 'escape-char']).

Aha! A person answered this in your cross-post on on comp.databases.

Notice the parentheses.
One must use parens so that NOT applies to the whole LIKE predicate,
instead of just the expr. NOT binds more tightly than LIKE.

Regards,
Bill K.

Sponsored Links