Database multi-table query where & INNER join__ database

In a multiple-table query, some SQL developers prefer to use where to join, such as:

SELECT a.id, B.name, b.date from Customers A, Sales b WHERE a.id = b.id;

Disadvantage: In the above statement, the Cartesian product of two tables is actually created, and all possible combinations are created. In the Cartesian connection, in the above example, if there were 1000 customers and 1000 sales records, the query would produce 1 million results and then filter out 1000 records with the correct ID. This is an inefficient use of database resources, the database to do 100 times times more work. In a large database, the Cartesian connection is a big problem, and the Cartesian product of two large tables creates 1 billion or trillions of records.

to avoid creating Cartesian product, you should use the inner JOIN:

SELECT a.id, B.name, b.date from Customers a INNER JOIN Sales b on a.id = b.ID;

Advantages: such as the above statement, using the inner join allows the database to produce only 1000 target results that are equal to the ID. Increased query efficiency.

Some database systems recognize where connections and automatically convert to INNER JOIN. In these database systems, there is no performance difference between the WHERE connection and the inner join. However, the INNER JOIN is recognized by all databases, so DBAs recommend using it in your environment.

INNER join (inner JOIN, or equivalent connection): Gets a record of the field matching relationships in the two tables.
LEFT join: Gets all the records in the left table, even if the right table does not have a matching record.
right join: In contrast to a left join, to get all the records in the right table, even if there is no matching record for the left-hand table.

Attention:
1, INNER join is equal to join;
2, schematic: