Request:

SELECT * FROM table WHERE s=1 ORDER BY id

Received a number of id: 2,5,6,7,9,11,12,15,16,17,18,20

Which MySQL operator can be pulled from the above series:
2,5,6,7,12,15,16,17 ?

The selection condition is to leave the numbers that make up the pair - the number itself and it’s plus 10.
That is, we take 2, we add 10 (it turns out 12), in this row there is the number 12 - bingo! - both are left, then we take 5 and look for 5 + 10 = 15 - there are both two, too - we leave both and so on, and now we come to 9 - we add 10, we get 19, there is no such number, we delete nine.

So it did not work out:

 SELECT * FROM (SELECT * FROM table WHERE s=1 ORDER BY id) aa WHERE ((aa.id MOD 10)=0)

    1 answer 1

    Try using the exists operator:

     SELECT id FROM test t1 WHERE EXISTS (SELECT id FROM test t2 where t2.id in (t1.id + 10, t1.id - 10))

    An example on sqlfiddle .

    • 3
      Maybe one exists where t2.id in(t1.id+10, t1.id-10) - Mike
    • @Mike yes, for sure! Thank you, updated the answer. - Denis
    • Mike, do you know why this rule was needed? I'm still looking for an answer to that topic about finding a free 2x2 die and more, if you're interested, I can describe the approximate algorithm that I have invented ... Probably not here - Sergey V.
    • Is the IN operator really useful? For the initial version of the request (two existences), the execution time is 0.0006, for the short, 0.0017, this is about a 10x10 grid. And the execution plan for the long version looks prettier, passing 100 lines without an index and 2 times 10 lines with Using index condition, a short version - two passes with 100 lines each without indexes. - Sergey V.
    • @ SergeyV. here at your discretion) c in just looks shorter. - Denis