The difference between on and where in left_join and inner_join in SQL statements

The following is a detailed analysis of the differences between on and where in left_join and inner_join in SQL statements. For more information, see tablea (id, type ): idtype ---------------------------------- 112132 tableb (id, class): idclass ---------------------------------

The following is a detailed analysis of the differences between on and where in left_join and inner_join in SQL statements. For more information, see table a (id, type ): id type ---------------------------------- 1 1 2 1 3 2 table B (id, class): id class ---------------------------------

The following is a detailed analysis of the differences between on and where in left_join and inner_join in SQL statements. For more information, see

Table a (id, type ):
Id type
----------------------------------
1 1
2 1
3 2
Table B (id, class ):
Id class
---------------------------------
1 1
2 2
SQL statement 1: select a. *, B. * from a left join B on a. id = B. id and a. type = 1;
SQL statement 2: select a. *, B. * from a left join B on a. id = B. id where a. type = 1;
SQL statement 3: select a. *, B. * from a left join B on a. id = B. id and B. class = 1;

The execution result of SQL statement 1 is:
A. id a. type B. id B. class
----------------------------------------
1 1 1 1
2 1 2 2
3 2

The execution result of SQL statement 2 is:
A. id a. type B. id B. class
----------------------------------------
1 1 1 1
2 1 2 2

The execution result of SQL statement 3 is:
A. id a. type B. id B. class
----------------------------------------
1 1 1 1
2 1
3 2
As can be seen from SQL statement 1, all records in the left table in left join will be queried and displayed. The condition after on does not work for it unless it is followed by where for filtering, this is SQL statement 2. It can be seen from SQL statement 3. In the condition following on, the restriction condition of the right table will take effect.
**************************************** **********************************
SQL statement 4: select a. *, B. * from a inner join B on a. id = B. id and a. type = 1;
SQL statement 5: select a. *, B. * from a inner join B on a. id = B. id where a. type = 1;
SQL statement 6: select a. *, B. * from a, B where a. id = B. id and a. type = 1;
SQL statement 7: select a. *, B. * from a, B where a. type = 1 and a. id = B. id;

The execution results of these four statements are the same, as follows:
A. id a. type B. id B. class
----------------------------------------
1 1 1 1
2 1 2 2
It can be seen that all the constraints after on in inner join will take effect, which is the same as the execution result of where. In addition, the where statement and inner join can indeed get the same result, but the efficiency is different (I have not tested this, but I believe this conclusion ).
But is SQL statement 6 less efficient than SQL statement 7? I don't have enough data to test it, but I believe so.

,