Real-mysql table joins Cartesian product (join, left join)

1. Why duplicate data appears when two tables are connected

2. What is the connection process of the table?

Example:

Table A:

1

0

Table B:

1

0

0

2

EXECUTE statement: SELECT * from A join B on a.id = b.id;

The order is as follows:

Tables formed by join:

1 1

1 0

1 0

1 2

0 1

0 0

0 0

0 2

On after:

1 1

0 0

0 0

Therefore, duplicate data appears after the table is connected

　　

　　There is a one-to-many relationship between the two tables, so there will be duplicates


What is Cartesian product?
When two tables are connected, they are connected by the Cartesian product.
The product of Descartes (Descartes) is also called direct product. Assuming collection a={a,b}, set b={0,1,2}, the Cartesian product of two sets is {(a,0), (a,1), (a,2), (b,0), (b,1), (b,2)}. Can be extended to multiple collections of cases. Similarly, if a represents a collection of students in a school and b represents a collection of all courses in the school, the Cartesian product of A and B represents all possible elective courses.
So after two tables are connected (using join, comma connection) is the Cartesian product.

Whether it is a join or a LEFT JOIN, the table is connected by a Cartesian product, and then filtered by on, the join displays only qualifying data, and the left join shows not only the data that satisfies the criteria, but also the absence of a match on the main table.
　　

　　

　　

The left join must be followed by an on

　　

　　　　　　

　　

　　

Real-mysql table joins Cartesian product (join, left join)