Ultra-detailed Mysql left Join,right join,inner join usage Analysis _mysql

Here is an example analysis
Table A records as follows:
AID Anum
1 a20050111
2 a20050112
3 a20050113
4 a20050114
5 a20050115

Table B is recorded as follows:
BID bname
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408

Create the two tables SQL statements as follows:
CREATE TABLE A
AID Int (1) auto_increment PRIMARY KEY,
Anum Char (20)
)
CREATE TABLE B (
BID Int (1) not NULL auto_increment PRIMARY KEY,
Bname Char (20)
)

INSERT into a
VALUES (1, ' a20050111 '), (2, ' a20050112 '), (3, ' a20050113 '), (4, ' a20050114 '), (5, ' a20050115 ');

INSERT into B
VALUES (1, ' 2006032401 '), (2, ' 2006032402 '), (3, ' 2006032403 '), (4, ' 2006032404 '), (8, ' 2006032408 ');

The experiment is as follows:
1.left join (left join)

The SQL statement is as follows:
SELECT * from a
Left JOIN b
On A.aid =b.bid

The results are as follows:
AID Anum BID bname
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 NULL NULL
(The number of rows affected is 5 rows)

Results show:
The left join is based on the records of a table, and a can be viewed as the left-hand table, B can be viewed as the right table, and left table.
In other words, the records in the left table (A) will all be represented, and the right table (B) will only display records that match the search criteria (in the example: A.aid = b.bid).
b tables where the records are insufficient are null.

2.right join (right join)

The SQL statement is as follows:
SELECT * from a
Right joing b
On a.aid = B.bid

The results are as follows:
AID Anum BID bname
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
NULL NULL 8 2006032408
(The number of rows affected is 5 rows)

Results show:
With a closer look, you will find that the result of the left join is just the opposite, this time based on the right table (B), where a table is deficient with null padding.

3.inner Join (equal join or inner join)

The SQL statement is as follows:
SELECT * from a
INNER JOIN b
On A.aid =b.bid

Equivalent to the following SQL sentence:
SELECT *
From A,b
WHERE A.aid = B.bid

The results are as follows:
AID Anum BID bname
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404

Results show:
Obviously, only the record of A.aid = B.bid is shown here. This shows that inner join is not based on who, it only shows the records that match the criteria.
The left JOIN operation is used in any FROM clause,

The record of the combined source table. Use the left JOIN operation to create a left-hand outer join. The left outer join will contain all the records in the two tables starting at the first (left), i.e.
Make a record in the second (right) table that does not have a matching value.

Syntax: from table1 left JOIN table2 on table1.field1 compopr table2.field2
Description: table1, the table2 parameter is used to specify the name of the table to combine records with.
Field1, the Field2 parameter specifies the name of the field being joined. And these fields must have the same data type and contain the same type of data, but they do not need to have the same
Name.
The COMPOPR parameter specifies the relational comparison operator: "=", "<", ">", "<=", ">=" or "<>".
If you want to join a field that contains Memo data type or OLE Object data type data in a inner JOIN operation, an error occurs.