MySQL: save SELECT to variable

Question

I have something like this:

SELECT FROM table
WHERE field NOT IN (
    SELECT FROM other_table ... //SECOND QUERY
)

Problem: I don't want SECOND QUERY be executed for each row of table. Can I save QUERY 2 result to some variable?

Do other_table and table have anything in common? Maybe using WHERE NOT EXISTS could be better? Or a JOIN? — gen_Eric
– gen_Eric, Commented May 21, 2014 at 16:20
Why do you think the subquery will be executed multiple times? — juergen d
– juergen d, Commented May 21, 2014 at 16:20
it won't be executed for every row of table. MySQL will run the subquery and save the results of that in memory (if possible) or in disk (if not). I would recommend making this a NOT EXISTS query as opposed to a NOT IN query though. The later should be more efficient. You can verify by using EXPLAIN to determine the Query Execution Plan — Evan Volgas
– Evan Volgas, Commented May 21, 2014 at 16:21
You can use a NOT EXISTS clause. But I would hope that the query engine is smart enough not to run the subquery for each row ;) — Raphaël Althaus
– Raphaël Althaus, Commented May 21, 2014 at 16:21
@V_B It seems you don't understand the difference between imperative and declarative programming languages. SQL is declarative, this means that it is up to the DBMS to figure out the most effective way to (imperatively) execute the queries. — wvdz
– wvdz, Commented May 21, 2014 at 16:26

Vatev · Accepted Answer · 2014-05-21 16:26:18Z

2

You can check if the query will run for every row by running EXPLAIN and looking if it its a DEPENDENT SUBQUERY or SUBQUERY. Dependent will run for each row.

Based on that you want to 'save it to a variable' I'm guessing it will not be dependent.

If you want to save a resultset to a 'variable' you need to use a temp table. In this case it will not be necessary.

answered May 21, 2014 at 16:26

Vatev

7,5801 gold badge36 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeff · Accepted Answer · 2014-05-21 16:26:19Z

1

A LEFT NULL JOIN may be a lot faster for you.

SELECT *
  FROM table AS t
  LEFT JOIN other_table AS ot ON ot.field = t.field
    WHERE ot.field IS NULL

answered May 21, 2014 at 16:26

Jeff

1672 silver badges10 bronze badges

3 Comments

wvdz Over a year ago

I don't see why this would be faster.

Vatev Over a year ago

@popovitsj he said that it may be faster. MySQL's query planner usually works better with joins.

Jeff Over a year ago

Yes, this way just a possibility for the poster to try. Any possibilities should be examined using EXPLAIN.

Collectives™ on Stack Overflow

MySQL: save SELECT to variable

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related