The difference between SQL join and where

Reprint: http://blog.chinaunix.net/uid-27570589-id-3771152.html

Today, when parsing an SQL statement, it is found that the left join is not the same as the execution plan produced by the where A=b (+), and the efficiency is not the same.
What the hell is going on? I got an article on the Internet.
(1.) Order of execution of SELECT statements

Processing Order of the SELECT statement
The following steps show the processing order for a SELECT statement.

1.FROM

2.ON

3.JOIN

4.WHERE

5.GROUP by

6.WITH CUBE or with ROLLUP

7.HAVING

8.SELECT

9.DISTINCT

10.ORDER by

11.TOP

That is, the filter on the first, and then the join, which avoids the two large tables produce all the data of the Cartesian product large data.

When these steps are executed, each step produces a virtual table that is used as input to the next step. These virtual tables are not available to callers (client applications or external queries). Only the table generated in the last step is returned to the caller.

If you do not specify a clause in the query, the corresponding step is skipped.
(2) that on and where that one more efficient?

If it is inner join, put on and put where the result is, but not say which efficiency is higher? If there is a outer join (left or right), there is a difference, because on takes effect earlier, a portion of the data has been filtered in advance, where the where is in effect.

In general, it feels more efficient to put on, because it is executed before the where.

First the Cartesian product, and then on the filter, if the join is inner, continue to go down, if the join is a left join, the on filter off the main table of the data is added back; Then the filter in the where is executed;

On is not the final filter, because the left join may be added back, and where is the final filter.

The difference between on and where is only when an outer join (left, right) is used, if the inner join is the same as where it was made, since on is the where, there is no other step in the middle.