Shulou(Shulou.com)06/03 Report--

This article mainly introduces the difference between left join, right join and inner join in MySQL, which has certain reference value. Friends who need it can refer to it. I hope you can get something.

Left join (left join) returns records that include all records in the left table and join fields equal in the right table right join (right join) returns records that include all records in the right table and records with equal join fields in the left table inner join (equivalent join) returns only rows with equal join fields in the two tables:

Examples are as follows:

Table An is as follows: aID aNum1 a200501112 a200501123 a200501134 a200501145 a20050115

Table B is as follows: bID bName1 20060324012 20060324023 20060324034 20060324048 2006032408

1.left join

The sql statement is as follows: select * from Aleft join B on A.aID = B.bID

Select * from A Magi B where A.aID=B.bID

The results are as follows: aID aNum bID bName1 a20050111 1 20060324012 a20050112 2 20060324023 a20050113 3 20060324034 a200501144 20060324045 a20050115 NULL NULL

The result shows that left join is based on the record of A table, A can be regarded as left table, B can be regarded as right table, and left join is based on left table. In other words, the records in table An on the left will be fully represented, while those in table B on the right will only show records that meet the search criteria (in this case, A.aID = B.bID). The deficiencies in table B are all NULL.

2.right join

The sql statement is as follows: select * from Aright join B on A.aID = B.bID

The results are as follows: aID aNum bID bName1 a20050111 1 20060324012 a20050112 2 20060324023 a20050113 3 20060324034 a200501144 2006032404NULL NULL 8 2006032408

(the number of rows affected is 5) the result shows that if you look closely, you will find that, contrary to the result of left join, this time it is based on table (B) on the right, and the deficiency of table An is filled with NULL.

3.inner join

The sql statement is as follows: select * from Ainnerjoin B on A.aID = B.bID

The results are as follows: aID aNum bID bName1 a20050111 1 20060324012 a20050112 2 20060324023 a20050113 3 20060324034 a200501144 2006032404

The results show that obviously, only the record of A.aID = B.bID is shown here. This shows that inner join is not based on anyone, it only displays records that meet the criteria.

Note: the LEFT JOIN operation is used to combine the records of the source table in any FROM clause. Use the LEFT JOIN operation to create a left outer join. The left outer join will contain all the records in the two tables starting from the first (left), even if there are no records with corresponding values in the second (right) table.

Syntax: FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2

Description: the table1, table2 parameter is used to specify the name of the table to combine records. The field1, field2 parameter specifies the name of the field to be 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 join fields that contain Memo data type or OLE Object data type data in an INNER JOIN operation, an error will occur.

So much for the difference between left join, right join and inner join. I hope the above content can be helpful to everyone and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.