MYSQL query WHERE IN vs OR

mysql sql where-in
hendra picture hendra · Dec 20, 2012 · Viewed 8k times · Source

I have developed a system using an OR query:

SELECT * FROM tableA 
JOIN tableB ON (idA = idB)
WHERE idA = 1 OR 
      idA = 2 OR 
      idA = 3 OR 
      idA = 4 OR 
      idA = 5 ...... OR 
      idA=100

Compare with query IN: 

SELECT * 
FROM tableA JOIN tableB ON (idA = idB)
WHERE idA IN (1,2,3,4,5,......,100)

What is the best query in a MYSQL database?

Answer

Bohemian picture Bohemian · Dec 20, 2012

Use IN.

IN will use an index.

OR will (afaik) not use an index.

Also, and this point is not to be sneezed at, the IN version:

  • uses less code
  • is easier to maintain
  • is easier to understand

For those reasons alone I would be prepared suffer a little performance to gain code quality, but you actually gain performance too.

Related questions

MySQL WHERE IN Query - ORDER BY Match

I'm trying to rephrase my question, cause my last one wasn't clear to everyone. This is my Test Table +----------+---------+-------+ | rel_id | genre | movie | +----------+---------+-------+ | 1 | 1 | 3 | | 2 | 8 | 3 | | 3 | 3 | 3 | | 4 | 2 | 5 | | 5 | 8 | 5 | | 6 | 3 | 5 | | 7 | 1 | 8 | | 8 | 8 | 8 | | 9 | 3 | 8 | | 10 | 5 | 9 | | 11 | 7 | 9 | | 12 | 9 | 9 | | 13 | 4 | 9 | | 14 | 12 | 9 | | 15 | 1 | 10 | | 16 | 8 | 10 | | 17 | 3 | 10 | | 18 | 5 | 10 | | 19 | 1 | 11 | | 20 | 2 | 11 | | 21 | 8 | 11 | | 22 | 5 | 11 | | 23 | 3 | 11 | +----------+---------+-------+ Result should be in the following order if …

Read More
MySQL multiple columns in IN clause

I have a database with four columns corresponding to the geographical coordinates x,y for the start and end position. The columns are: x0 y0 x1 y1 I have an index for these four columns with the sequence x0, y0, …

Read More
Get all rows matching a list in a SQL query

I have the following SQL query that selects any row matching ANY of the values in the list (9, 10): SELECT r.id, r.title, u.name as 'Created By', c.name as 'Category', c.value, cr.category_id FROM category_resource …

Read More