Mysql not equal operator failing?

Question

I have the following mysql table (simplified):

CREATE TABLE IF NOT EXISTS `mytable` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`emails` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`phones` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`history` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

PRIMARY KEY (`id`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=259 ;

I have the following query :

SELECT *
FROM mytable
WHERE emails LIKE '%[email protected]%'
OR phones LIKE '%[email protected]%'
AND history !='None'

This is producing 2 records:

INSERT INTO `mytable` (`id`, `emails`, `phones`, `history`) VALUES
(118, 'PLEASE SET', NULL, 0, '[email protected]', NULL, 0, 'None'),
(237, 'PLEASE SET', NULL, 0, '[email protected]', NULL, 0, 'gomez');

but I was expecting the one with history = 'None' not to be included. What am I doing wrong?

I think the different operator in MySQL is <> and not != — Marco Aurélio Deleu
– Marco Aurélio Deleu, Commented Jan 12, 2015 at 21:05
@MarcoAurélioDeleu: Both != and <> are "not equals" in MySQL. — gen_Eric
– gen_Eric, Commented Jan 12, 2015 at 21:07

M Khalid Junaid · Accepted Answer · 2015-01-12 21:06:24Z

2

Organize your OR criteria in ()

SELECT *
FROM mytable
WHERE (all_emails LIKE '%[email protected]%'
OR all_phones LIKE '%[email protected]%')
AND history !='None

Sample Demo

answered Jan 12, 2015 at 21:06

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1592380 Over a year ago

Thank you very much! Why does The parentheses help?

M Khalid Junaid Over a year ago

@user61629 its regarding operator precedence for full overview checkout this question and answer stackoverflow.com/questions/1241142/…

souslov · Accepted Answer · 2015-01-12 21:06:20Z

2

You should explicitly set parenthesis in your WHERE clause :

WHERE (all_emails LIKE '%[email protected]%'
   OR all_phones LIKE '%[email protected]%')
  AND history !='None'

answered Jan 12, 2015 at 21:06

souslov

44.7k11 gold badges92 silver badges113 bronze badges

2 Comments

user1592380 Over a year ago

Thank you very much! Why does The parentheses help?

souslov Over a year ago

@user61629 : You are welcome.AND has a precedence over OR as a logical operator, parenthesis helps to avoid cases like you did have, when at first was evaluated AND and only then OR, which wasn't what you've wanted to achieve in the first place.

Ali Mohammadi · Accepted Answer · 2015-01-12 21:08:38Z

0

SELECT *
FROM mytable
WHERE (all_emails LIKE '%[email protected]%'
OR all_phones LIKE '%[email protected]%')
AND history NOT IN(
SELECT history FROM mytable WHERE history LIKE 'None')

answered Jan 12, 2015 at 21:08

Ali Mohammadi

1,3342 gold badges15 silver badges29 bronze badges

2 Comments

Ali Mohammadi Over a year ago

Test Again (I Changed)!

Michael McGriff Over a year ago

You still don't need the subquery.

Collectives™ on Stack Overflow

Mysql not equal operator failing?

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related