Want to connect two tables for left, oneself wrote two SQL statements:
First: select * from table A left join table B on the table A. B=table B.
The second: select * from table A left join (select * from table B where table B. A IN (' 101 ', 102)) on the table A. B=table B.
That is to say, the second method is to think in the left before connection, a first screening, shrink range of records, to connect again; But to find out table in advance of the B column value,
These two methods on the execution efficiency which is better?

CodePudding user response:

This depends on the execution plan,

IN addition, the first statement, the last is also want to add the WHERE table b. IN A ()?

CodePudding user response:

Explain the select * from table A left join table B on the table A. B=table B.
Explain the select * from table A left join (select * from table B where table B. A IN (' 101 ', 102)) on the table A. B=B.
Look at the two statements the execution plan

CodePudding user response:

Is your current content of the two tables, there is no difference between

CodePudding user response:

reference MW_long reply: 3/f

as your content of the two tables, there is no difference between

I this is, for example, the complex than the actual data

CodePudding user response:

The conditions of the two results are different, theoretically conditions more, there is an index and the efficiency of the filtering effect obviously better

CodePudding user response:

Under the same conditions of, whether high efficiency depends on the execution plan, system cost from multiple execution plan the youngest, so it's possible execution plans are all the same.

CodePudding user response:

Mysql are stupid and the second execution of the plan must be performed before the brackets IN the "select * from table b where table b. A IN (' 101 ', 102),"
If A is A primary key or unique key, two SQL almost
A lot of records if comply with this condition, and table a few eligible records, the first performance is better