Analysis of MySQL external connection and internal connection

Time:2020-6-1

Table 1: Test_ user

Analysis of MySQL external connection and internal connection

Table 2:

Analysis of MySQL external connection and internal connection

1: Left join
1、SELECT * from test_user a LEFT JOIN test_order b ON a.name=b.name

Analysis of MySQL external connection and internal connection

2、SELECT * from test_user a LEFT JOIN test_order b ON a.name=b.name where a.name=’Zhang San

Analysis of MySQL external connection and internal connection

3、SELECT * from test_user a LEFT JOIN test_order b ON a.name=b.name and a.name=’Zhang San’

Analysis of MySQL external connection and internal connection

4、SELECT * from test_user a LEFT JOIN test_order b ON a.name=b.name where b.book=’Legend of blood’

Analysis of MySQL external connection and internal connection

5、SELECT * from test_user a LEFT JOIN test_order b ON a.name=b.name and b.book=’Legend of blood’

Analysis of MySQL external connection and internal connection

Conclusion: according to the above results, the difference between where and on is that the left join query has the characteristics of finding all the data on the left, regardless of whether there is matching data on the right.

2: Right join

Conclusion: similar to left join, right join is to find all data on the right regardless of whether there is matching data on the left.

3、 Inner join

1、SELECT * from test_user a INNER JOIN test_order b ON a.name=b.name WHERE b.book=’Legend of blood’

Analysis of MySQL external connection and internal connection

2、SELECT * from test_user a INNER JOIN test_order b ON a.name=b.name and b.book=’Legend of blood’

Analysis of MySQL external connection and internal connection

3.Equivalent to:SELECT * from test_user a , test_order b where a.name=b.name and b.book=’Legend of blood’

Analysis of MySQL external connection and internal connection

Conclusion: from the above test, it can be seen that there is no difference between the results written in where and on, but the efficiency written in on is higher than that written in where in theory, because in on, the conditions are filtered first and then connected, and the efficiency is higher than that written in connection first and then filtered.

Efficiency:
1. Inner join is faster than left join
2. Inner join conditions are faster on than where

Recommended Today

The way of nonlinear optimization

Mathematical knowledge 1、 Nonlinear functionLinear function is another name of a function of first degree, then nonlinear function means that the function image is not a function of a straight line.Nonlinear functions include exponential function, power function, logarithmic function, polynomial function and so on. 2、 Taylor expansion1. Taylor formula:Taylor’s formula is to add a_ The […]