Reusing results from a single SELECT query in MySQL

Question

I have this query, which is the only query containing a total of 3 SELECT:

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)) 

Request

 SELECT a FROM table WHERE b = 44 

exactly the same thing, and I assume that the database runs 2 times, although it should be faster the second time due to caching, etc.

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

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

I am using MySQL 5.7.

Answer 1

Avoid subqueries and the use of 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 in joins, you may need to use DISTINCT after SELECT.

Answer 2

Save the SELECT first as a temporary table, then use it to run the second query:

 SELECT a, b INTO #temtbl FROM tbl WHERE b IN ((SELECT a FROM tbl WHERE b = 44)) AND NOT a IN ((SELECT a FROM tbl WHERE b = 44)) SELECT a,b FROM #temtbl WHERE b = 44 

Answer 3

Why can't you do:

 SELECT tbl1.a FROM tbl AS t1 INNER JOIN tbl AS t2 ON t1.b = ta AND t2.b = 44 WHERE t1.b <> 44