Reusing results from a single SELECT query in MySQL

Question

I have this query, which is a single query that contains 3 SELECTs in total:

SELECT a
FROM tbl
WHERE   b IN ((SELECT a FROM tbl WHERE b = 44))
AND NOT a IN ((SELECT a FROM tbl WHERE b = 44))

The query

SELECT a FROM table WHERE b = 44

is exactly the same, and I'm guessing that the database is running this 2 times, even though it should be faster the second time due to caching etc.

Is there a way in SQL or something specific to MySQL that I can do to reuse 100% of the results from the first one the query is executed?

Or any other ideas on how to speed up this query?

I'm using MySQL 5.7.

Answer 1

Avoiding sub queries and using joins (which should use indexes and be more efficient).

SELECT tbl1.a
FROM tbl tbl1
INNER JOIN tbl tbl2 ON tbl1.b = tbl2.a AND tbl2.b = 44
LEFT OUTER JOIN tbl tbl3 ON tbl1.a = tbl3.a AND tbl3.b = 44
WHERE tbl3.a IS NULL

Depending on whether you can get multiple matches on the joins then you might need to use DISTINCT after the SELECT.