I would like to clarify one small nuance that I constantly encounter in my practice. For example. To get a list of all enterprises in Moscow, we make a request:

SELECT `company` FROM `table_1` WHERE `city`='MOSCOW'

But what should the query look like if we want to get a list of all enterprises in any city except Moscow? I have two options:

 SELECT `company` FROM `table_1` WHERE `city`!='MOSCOW' SELECT `company` FROM `table_1` WHERE NOT `city` IN ('MOSCOW')

What do literate programmers think about this? What should be the record?

  • I am for the first option, in the case of one city. Have you checked the second option? - ArchDemon
  • Both options work. But I wonder how it will be more correct. And maybe there is even a third option. On one of the forums, I once made a comment regarding the incorrectness of the first option. Since then, I have been in doubt. - StasHappy
  • and what EXPLAIN SELECT company FROM table_1 WHERE city!='MOSCOW' and EXPLAIN SELECT company FROM table_1 WHERE NOT city IN ('MOSCOW') tells us - ArchDemon
  • As it turned out, in both versions EXPLAIN shows absolutely identical results. What apparently should indicate the absence of any difference between them. - StasHappy

2 answers 2

There is no real difference. The optimizer will still reduce all these options to the same result.

You can try to check through EXPLAIN , if interested. If there are indexes, then range will be used, since involves the enumeration of all elements that are not included in a certain set (even if consisting of one element), therefore there will be no difference between NOT city IN ('MOSCOW') and city!='MOSCOW' .

But on the other hand there are a couple of fundamental points:

  1. If suddenly there is a possibility that the query will be expanded, and you will have to add extra. cities in condition, better leave NOT city IN ('MOSCOW') .
  2. If it is possible to initially transmit the full set of cities, without touching the DB each time, then it is better to convert the request into a direct entry, for example, city IN ('Saint Petersburg', 'Novosibirsk') . Such logic is not always possible and justified, but in many requests it can give a tangible performance gain.

    all three options are correct:

     SELECT `company` FROM `table_1` WHERE `city`!='MOSCOW' SELECT `company` FROM `table_1` WHERE NOT `city` IN ('MOSCOW') SELECT `company` FROM `table_1` WHERE `city` NOT IN ('MOSCOW')

    the confirmation:

    SQL feeddle

    MySQL 5.6 Schema Setup :

     create table t (c text); insert into t values ('москва'), ('Π±ΠΎΠ»ΠΎΠ³ΠΎΠ΅'), ('Π»Π΅Π½ΠΈΠ½Π³Ρ€Π°Π΄');

    Query 1 :

     select * from t where c != 'москва'

    Results :

     | c | |-----------| | Π±ΠΎΠ»ΠΎΠ³ΠΎΠ΅ | | Π»Π΅Π½ΠΈΠ½Π³Ρ€Π°Π΄ |

    Query 2 :

     select * from t where not c in ('москва')

    Results :

     | c | |-----------| | Π±ΠΎΠ»ΠΎΠ³ΠΎΠ΅ | | Π»Π΅Π½ΠΈΠ½Π³Ρ€Π°Π΄ |

    Query 3 :

     select * from t where c not in ('москва')

    Results :

     | c | |-----------| | Π±ΠΎΠ»ΠΎΠ³ΠΎΠ΅ | | Π»Π΅Π½ΠΈΠ½Π³Ρ€Π°Π΄ |