Shulou(Shulou.com)05/31 Report--

This article introduces how to achieve left connection and right connection in MySQL. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Table An is recorded 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

The experiments are as follows:

1.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)

The results show that:

Left join is based on the record of table A, A can be regarded as the left table, B can be regarded as the right table, and left join is based on the 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 deficiency in table B is NULL.

2.right join

The sql statement is as follows:

SELECT *

FROM A

RIGHT 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

NULL NULL 8 2006032408

(the number of rows affected is 5)

The results show that:

If you take a closer look, 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 in Table An is filled with NULL.

3.inner join

The sql statement is as follows:

SELECT *

FROM A

INNER 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

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.

-[the following is some information on the Internet]-

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 the 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 "".

An error will occur if ④ joins fields that contain data of either the Memo data type or the OLE Object data type in the INNER JOIN operation.

On how to achieve left connection and right connection in MySQL is shared here, I hope the above content can be of some help to you, can 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.