Boolean Operators in MySQL

Question

I have list of items (separated by comma) that I need to look up in a database. Initially I was looking up each item code individually, but there must be an easier way of doing it.

I was playing around in phpMyAdmin trying to select items with no luck.

SELECT * 
FROM  `items` 
WHERE  `code` = ( 20298622
OR 83843296 
OR 46549947 )

Returned no results.

SELECT * 
FROM  `items` 
WHERE  `code` =20298622
OR 83843296 
OR 46549947

Returned every item in the database.

I was reading up on the MySQL docs, and it appears that OR (or ||) should do what I'm looking for. Where did I go wrong?

Also, FWIW, code is a integer field.

Thanks!

alex · Accepted Answer · 2011-05-31 23:33:55Z

4

You could use...

SELECT * 
  FROM `items` 
 WHERE `code`
    IN (20298622,
        83843296, 
        46549947)

edited May 31, 2011 at 23:33

answered May 31, 2011 at 23:24

alex

492k205 gold badges890 silver badges992 bronze badges

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

1 Comment

user316602 Over a year ago

This is the most convenient answer since all of the items are already separated by a comma. Thanks!

IAmTimCorey · Accepted Answer · 2011-05-31 23:30:12Z

2

The reason it failed is because the OR statement separates two conditional statements. Therefore, your first conditional statement would evaluate (code = 20298622) but then your next statement is simply a number. If you want to use the OR statement, you will need to specify the column name again. Here is what it would look like:

SELECT * 
FROM  `items` 
WHERE  `code` = 20298622
OR `code` = 83843296 
OR `code` = 46549947

You could also use more complex statements like IN or you could even do a join to a second table (even a temp table). Basically, there are a lot of ways to accomplish this. You will just need to choose which one fits your situation best (speed, ease of use, etc.)

answered May 31, 2011 at 23:30

IAmTimCorey

16.8k5 gold badges42 silver badges77 bronze badges

Comments

Xint0 · Accepted Answer · 2011-05-31 23:30:56Z

1

You are not using the correct syntax. You should change the statement to:

SELECT * 
FROM `items` 
WHERE `code` = 20298622
    OR `code` = 83843296 
    OR `code` = 46549947

Or as @alex notes on his answer, you can use the IN clause:

SELECT *
FROM `items` 
WHERE `code` IN (20298622, 83843296, 46549947)

answered May 31, 2011 at 23:30

Xint0

5,4192 gold badges30 silver badges29 bronze badges

Collectives™ on Stack Overflow

Boolean Operators in MySQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related