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

This article introduces the relevant knowledge of "the case study of leftjoin, rightjoin and innerjoin of MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Left join (left join) returns records that include all records in the left table and records that are equal to join fields in the right table

Right join (right join) returns records that include all records in the right table and records that are equal to join fields in the left table

Inner join (equivalent join) returns only rows with equal join fields in two tables.

Examples are as follows:

CREATE TABLE a (

AID int

ANum char (20)

)

CREATE TABLE b (

BID int

BName char (20)

)

INSERT INTO a

VALUES (1, 'a20050111'), (2, 'a20050112'), (3,' a20050113'), (4, 'a20050114'), (5,' a20050115')

INSERT INTO b

VALUES (1, '2006032401'), (2,' 2006032402'), (3, '2006032403'), (4,' 2006032404'), (8, '2006032408')

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

-

1.left join

Mysql > select * from A

-> left join B

-> on A.aID = B.bID

+-+

| | 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

Mysql > select * from A

-> right join B

-> on A.aID = B.bID

+-+

| | aID | aNum | bID | bName | |

+-+

| | 1 | a20050111 | 1 | 2006032401 | |

| | 2 | a20050112 | 2 | 2006032402 | |

| | 3 | a20050113 | 3 | 2006032403 | |

| | 4 | a20050114 | 4 | 2006032404 | |

| | NULL | NULL | 8 | 2006032408 | |

+-+

5 rows in set (0.01 sec)

(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

Mysql > select * from An inner join B on A.aID = B.bID

+-+

| | aID | aNum | bID | bName | |

+-+

| | 1 | a20050111 | 1 | 2006032401 | |

| | 2 | a20050112 | 2 | 2006032402 | |

| | 3 | a20050113 | 3 | 2006032403 | |

| | 4 | a20050114 | 4 | 2006032404 | |

+-+

4 rows in set (0.00 sec)

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 in which records are to be combined.

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.

This is the end of the case study of leftjoin, rightjoin and innerjoin of MySQL. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.