MySQL JOIN | Join

A junction is the most important operation that can be performed with a select of SQL. In order to improve the effectiveness of storage and avoid data redundancy, it is often possible to store the associated data in several tables, so how can you retrieve the data with a SELECT statement?

The answer is join (junction). In a SELECT statement, we can join multiple tables to return a set of data.
There are 4 main connections, namely INNER join (inner join), left join (Zuo Junction), right join (Starboard Junction), Cross join (Fork Junction)

To be able to clearly illustrate these 4 types of connections, you first need to prepare the instance data:

Create 2 tables:

CREATE TABLE product (ID  INT UNSIGNED not NULL auto_increment,name VARCHAR () Not null,price int UNSIGNED not null,vendor_id int. UNSIGNED not null,primary KEY (ID));

CREATE TABLE Vendor1 (ID  INT UNSIGNED not NULL auto_increment,name VARCHAR () Not null,primary KEY (ID));

Insert data:

INSERT into Product (name,price,vendor_id) VALUES ('Bread',Ten,1INSERT into Product (name,price,vendor_id) VALUES ('Beer', -,2INSERT into Product (name,price,vendor_id) VALUES ('Vegetables', the,0INSERT into Product (name,price,vendor_id) VALUES ('Beef', A,3INSERT into Product (name,price,vendor_id) VALUES ('Noodles', -,2INSERT into Product (name,price,vendor_id) VALUES ('Fish', $,0INSERT into Product (name,price,vendor_id) VALUES ('Milk', A,3INSERT into Product (name,price,vendor_id) VALUES ('Water', -,1);

Insert into vendor (name) values ('hellen'), insert into vendor (name) values (' /c4>Ella'); INSERT into vendor (name) VALUES ('Matt'); I Nsert into vendor (name) VALUES ('jake');

Description: The Product table contains information about the goods, some of which are provided by the store owner, so there is no supplier for this type of product, which is indicated by vendor_id = 0

1. INNER Join | inner coupling

Example 1-1:
Find all the products in the product table that are provided by Ella:

= Vendor. ID  'ella';

Results:

Summarize:

The INNER join can retrieve the intersection of Table product and table vendor that match the criteria (join condition, where condition, and so on), as shown in:

2. Left Join |

Example 2-1:
List all product in the Product table and list its suppliers

= Vendor. ID;

Results:

Summarize:

The left JOIN retrieves all the records for the primary table, and if the records in the schedule are eligible to be appended to the records in the primary table, the relationship between the primary table and the schedule is as follows:

3. Right Join |

Example 3-1:
List all products provided by Ella and Jake

= Vendor. ID WHERE vendor.name in ('ella'Jake');

Results:

Summarize:
The right JOIN retrieves all the records for the schedule, and if the records in the primary table are eligible to be appended to the records in the schedule, the relationship between the primary table and the schedule is as follows:

4. Cross Join | fork coupling

Example 4-1:
List all products provided by Ella and Hellen

= Vendor. ID and vendor.name in ('ella'hellen');

Results:

The cross JOIN returns the pairing of each row in the primary table that matches the criteria in the schedule.

If there is no WHERE clause, you can see more clearly the return result of the cross join:

Example 4-2:

SELECT * from Product,vendor;

Results:

It is clear that the result returned is the pairing of each row in the primary table with each row in the schedule.

MySQL JOIN | Join