MySQL left connection, right connection, and internal connection

Take MySql as an example. Create two data tables in the MySQL database and insert some data respectively.

The sample script is as follows:

 
 

  
  
　drop table table1;   
  
  
CREATE TABLE `andrew`.`table1`   
  
  
(   
  
  
`name` VARCHAR(32) NOT NULL,   
  
  
`city` VARCHAR(32) NOT NULL   
  
  
)   
  
  
ENGINE = MyISAM;   
  
  
insert into TABLE1(name, city) values ('Person A', 'BJ');   
  
  
insert into TABLE1(name, city) values ('Person B', 'BJ');   
  
  
insert into TABLE1(name, city) values ('Person C', 'SH');   
  
  
insert into TABLE1(name, city) values ('Person D', 'SZ');   
  
  
commit;   
  
  
drop table table2;   
  
  
CREATE TABLE `andrew`.`table2`   
  
  
(   
  
  
`name` VARCHAR(32) NOT NULL,   
  
  
`city` VARCHAR(32) NOT NULL   
  
  
)   
  
  
ENGINE = MyISAM;   
  
  
insert into TABLE2(name, city) values ('Person W', 'BJ');   
  
  
insert into TABLE2(name, city) values ('Person X', 'SH');   
  
  
insert into TABLE2(name, city) values ('Person Y', 'SH');   
  
  
insert into TABLE2(name, city) values ('Person Z', 'NJ');   
  
  
commit; 
 
 

1. MySQL external connection-left connection result

Table1 is left, so it is the left join. In this case, all records in Table 1 are listed. There are three situations:

A. if the city corresponding to each record in table 1 also exists in table 2 and there is only one record

A new record is generated in the returned result. As shown in the figure above, Person A corresponds to Person B.

B. If the city corresponding to each record in table 1 also exists in table 2 and there are N records, N new records will be generated in the returned results. The following figure shows the situation of Person C.

C. If the city corresponding to each record in Table 1 does not exist in table 2,

New record, and all the right side of the record is NULL. The following figure shows the situation of Person D.

Records that do not comply with the preceding three rules will not be listed.

2. MySQL external connection-right connection result

Table2 is the right join. In this case, all records in Table 2 are listed. There are three situations:

A. if the city corresponding to each record in Table 2 also exists in Table 1 and there is only one record

A new record is generated in the returned result. As shown in the preceding figure, the Person X corresponds to Person Y.

B. If the city corresponding to each record in Table 2 also exists in Table 1 and there are N records, N new records will be generated in the returned results. The following figure shows the situation of Person W.

C. If the city corresponding to each record in Table 2 does not exist in table 1,

New record, and all NULL on the left of the record. The following figure shows the situation of Person Z.

Records that do not comply with the preceding three rules will not be listed.

3. MySQL internal connection

There is no NULL field in the data records connected in MySQL. We can simply think that the result of the inner link is the result obtained after the record with the NULL field is removed from the result of the Left or Right join. It can even be considered that if the two tables only have the data records obtained after the inner join operation, for example, Table 1 contains only Person A, Person B, and Person C, table 2 contains only Person W, Person X, and Person Y. The left join and right join results of the two tables are the same.

Note: select * from table1 a inner join table2 B on. city = B. city and select * from table1 a join table2 B on. city = B. the city effect is the same, that is, if the left side of the join has no keywords such as left, right, or inner, the default value is inner join. MySQL does not support full join.

1. Key parameters for MySQL database performance optimization
2. MySQL will adopt a new development and release Mode
3. Analysis on hidden space in MySQL
4. How to connect to the MySQL database in NetBeans
5. MySQL Index classification and their respective uses