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.