Differences between inner join and where

1. Connection statements used in the WHERE clause are called implicit connections in the database language. Inner join ...... The connection generated by the on clause is called an explicit connection. (Other join parameters are also explicit connections.) There is no essential difference between the connection relationship between where and inner join, and the result is the same. But! Recessive connections have been gradually eliminated with the standardization and development of the database language, and the new database language has basically abandoned recessive connections, all of which adopt explicit connections.

2. Join clauses can be used no matter how they are connected. When connecting to the same table, be sure to define aliases. Otherwise, an error is returned!

A> Inner join: It is understood as "valid join". Only the data in both tables shows left join: It is understood as "with left display", for example, on. field = B. field to display all the data in Table A and all the data in Table A \ B. data in Table A and in Table B is displayed as null.

B> right join: it is interpreted as "right display", for example, on. field = B. field, all data in Table B and data in a \ B are displayed. data in Table B and in Table A is displayed as null.

C> full join: it is interpreted as "Full join". All data in the two tables is displayed, which is actually inner + (left-inner) + (right-inner)

3. Join can be divided into three types: Full outer join, left join, and right join.
Full outer join contains all records of the two tables.
The left join is based on the table on the left, supplemented by the right join, and the right join is opposite.

4. The following principles should be followed to Improve the Performance of database query statements:

• When performing table-to-table connection queries, the large table is in front of the small table
• Table aliases are not used. Fields in different tables are distinguished by field prefixes.
• The restrictions in the query conditions should be written before the table connection conditions.
• Use indexed fields as the query condition whenever possible