MySQL Connectivity issues

Use two tables (a_table, b_table), associated fields a_table.a_id and b_table.b_id to demonstrate MySQL's internal connection, outer join (left (outer) connection, right (outer) connection, full (outer) connection).

MySQL version: Server version:5.6.31 mysql Community server (GPL)

Database tables: a_table, b_table

Subject: Inner connection, left join (left outer connection), right connection (right outer connection), full connection (full outer connection)

Premise
To build a table statement:
CREATE TABLE a_table (
a_idInt (one) DEFAULT NULL,
a_namevarchar () DEFAULT NULL,
a_partvarchar () DEFAULT NULL
) Engine=innodb DEFAULT Charset=utf8
CREATE TABLE b_table (
b_idInt (one) DEFAULT NULL,
b_namevarchar () DEFAULT NULL,
b_partvarchar () DEFAULT NULL
) Engine=innodb DEFAULT Charset=utf8

Table test Data:

One, inner connection
Keyword: INNER join on
Statement: SELECT * from A_table a inner join b_table bon a.a_id = b.b_id;
Execution Result:

Description: Combines records from two tables to return records that match the associated fields, that is, the intersection (shadow) portion of the two tables.

Second, left join (left outer connection)
Keyword: Left join on/left outer JOIN on
Statement: SELECT * from A_table a LEFT join b_table bon a.a_id = b.b_id;
Execution Result:

Description
The LEFT join is a shorthand for the outer join, which is the full name of the outer join, and is one of the outer joins.
Left (outer) joins, the records of the left table (a_table) are all represented, and the right table (b_table) displays only records that match the search criteria. The right table does not have enough records to be null.

Third, right connection (right outer connection)
Keyword: Right join on/right outer JOIN on
Statement: SELECT * from a_table a right outer joins b_table B on a.a_id = b.b_id;
Execution Result:

Description
The right join is a shorthand for outer join, which is the full name of the outer join and is one of the outer joins.
In contrast to the left (outer) connection, the right (outside) connection, the left table (a_table) displays only records that match the search criteria, and the records for the right table (b_table) are all represented. The left table does not have enough records to be null.

Iv. Full connection (full outer connection)
MySQL does not currently support this approach and can be replaced in other ways.
V. Additions, MySQL how to perform associated queries
MySQL thinks that any query is an "association", and not just a query needs to be matched to two tables, so in MySQL, every query, every fragment (including subqueries, even based on a single-table query) can be an association.
The current MySQL association execution strategy is simple: MySQL performs a nested loop association operation on any association, that is, MySQL first loops through a single piece of data in a table, then looks for matching rows in the nested loop to the next table, and goes down until the matching behavior in all tables is found. The columns that are required in the query are then returned based on the rows that match each table. Take a look at the simple query in the following example:

Query statements: Select Tbl1.col1, tbl2.col2 from TBL1 inner join TBL2 using (col3) where Tbl1.col1 in (5, 6);
Assuming that MySQL is associated in the order of the tables in the query, we can use the following pseudocode to indicate how MySQL will complete this query:
Outer_iter = iterator over tbl1 where col1 in (5, 6)
Outer_row = Outer_iter.next
While Outer_row
Inner_iter = iterator over tbl2 where col3 = Outer_row.col3
Inner_row = Inner_iter.next
While Inner_row
Output [Outer_row.col1, Inner_row.col2]
Inner_row = Inner_iter.next
End
Outer_row = Outer_iter.next
End
The above execution plan applies to both single-table and multi-table associated queries, and if it is a single-table query, only the underlying operation of the upper layer is required. For an outer join, the above execution process still applies. For example, we will modify the above query statement as follows:
Select Tbl1.col1, tbl2.col2 from tbl1 left outer joins TBL2 using (col3) where Tbl1.col1 in (5, 6);
So, the corresponding pseudo-code is as follows:
Outer_iter = iterator over tbl1 where col1 in (5, 6)
Outer_row = Outer_iter.next
While Outer_row
Inner_iter = iterator over tbl2 where col3 = Outer_row.col3
Inner_row = Inner_iter.next
If Inner_row
While Inner_row
Output [Outer_row.col1, Inner_row.col2]
Inner_row = Inner_iter.next
End
Else
Output [Outer_row.col1, NULL]
End
Outer_row = Outer_iter.next
End
Description: Part fifth from the third edition of high-performance MySQL

MySQL Connectivity issues