CODEWITHSUNDEEP
mysqlsqlwhere-clause
Elo
Elo

Reputation: 306

Which version of WHERE clause of MySQL is the best?

Which version of WHERE is the best to use in MySQL? Result is the same. Is there any difference in speed or efficiency or in something else?

WHERE NOT OR:

SELECT Country
FROM Customers
WHERE NOT (Country = 'Argentina' OR Country = 'Brazil')
GROUP BY Country

WHERE <> AND:

SELECT Country
FROM Customers
WHERE (Country <> 'Argentina' AND Country <> 'Brazil')
GROUP BY Country

WHERE != AND:

SELECT Country
FROM Customers
WHERE (Country != 'Argentina' AND Country != 'Brazil')
GROUP BY Country

WHERE NOT LIKE AND:

SELECT Country
FROM Customers
WHERE (Country NOT LIKE 'Argentina' AND Country NOT LIKE 'Brazil')
GROUP BY Country

Here it can be tried.

Upvotes: 0

Views: 90

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521209

All 4 of your queries are logically identical, and in fact all 4 queries may have an identical execution plan. Try running EXPLAIN to see for yourself. The bigger issue here then is code craft, and towards that point, I prefer none of the above, but rather this WHERE IN version:

SELECT Country
FROM Customers
WHERE Country NOT IN ('Argentina', 'Brazil')
GROUP BY Country

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Use whichever you are most comfortable with. They are all equivalent -- or at least equivalent under most reasonable circumstances. However, like would not the the same, for instance, if the strings had any wildcards. Note: like without wildcards should still use an index, so even performance should be similar.

However, I would recommend:

where country not in ('Argentina', 'Brazil')

I think it most concisely captures the logic you want.

Upvotes: 2

Related Questions