MYSQL Syntax for NOT in where clause

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

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,


Re: MYSQL Syntax for NOT in where clause wrote:
Quoted text here. Click to load it

On page

   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'),(NULL);

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

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

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

Bill K.

Re: MYSQL Syntax for NOT in where clause

Bill Karwin wrote:
Quoted text here. Click to load it

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.

Bill K.

Site Timeline