MySQL JOIN Syntax description and illustration

First, MySQL JOIN classification

JOIN is broadly divided into three categories as follows:

INNER join (inner JOIN): Gets the record that has a connection matching relationship in two tables.

Left join: Gets the left table (table1) full record, that is, the right table (table2) has no corresponding matching record.

Right join: In contrast to the left join, get the full record of the right table (table2), which is the Table1 with no matching records.

Ii. Graphical relationsINNER JOIN: used to obtain a record of a connection matching relationship in two tables.

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/89/C2/wKiom1gb3sjDU6M4AABL3sMjtiM021.png-wh_500x0-wm_3 -wmp_4-s_4091312711.png "title=" Inner.png "alt=" Wkiom1gb3sjdu6m4aabl3smjtim021.png-wh_50 "/>

Mysql> SELECT * from a inner joins score on A.SN=SCORE.SN;

+----+---------+-------+----+-------+-------+

| ID | name | sn | ID | sn | Score |

+----+---------+-------+----+-------+-------+

| 1 | Mashen |  10086 | 1 |    10086 | 90 |

| 2 | Haishen |  10087 | 2 |    10087 | 59 |

| 3 | Haoge |  10088 | 3 |    10088 | 77 |

+----+---------+-------+----+-------+-------+

3 rows in Set (0.24 sec)

Mysql> SELECT * from a joins score on A.SN=SCORE.SN;

+----+---------+-------+----+-------+-------+

| ID | name | sn | ID | sn | Score |

+----+---------+-------+----+-------+-------+

| 1 | Mashen |  10086 | 1 |    10086 | 90 |

| 2 | Haishen |  10087 | 2 |    10087 | 59 |

| 3 | Haoge |  10088 | 3 |    10088 | 77 |

+----+---------+-------+----+-------+-------+

3 Rows in Set (0.00 sec)

inner Join

The using (SN) can also be used when the 2 associated table fields are the same, so that the field in the using is displayed once ....

Mysql> SELECT * from a JOIN score using (SN);

+-------+----+---------+----+-------+

| sn | ID | name | ID | Score |

+-------+----+---------+----+-------+

|  10086 | 1 |  Mashen |    1 | 90 |

|  10087 | 2 |  Haishen |    2 | 59 |

|  10088 | 3 |  Haoge |    3 | 77 |

+-------+----+---------+----+-------+

Left JOIN: produces a full set of table A, whereas a match in B table has a value, and no match is substituted with a null value

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/89/BF/wKioL1gb4aTin9ugAABacAx2rfs302.png-wh_500x0-wm_3 -wmp_4-s_320553044.png "title=" Left.png "alt=" Wkiol1gb4atin9ugaabacax2rfs302.png-wh_50 "/>

Mysql> SELECT * from a LEFT JOIN score on A.SN=SCORE.SN;

+----+-----------+-------+------+-------+-------+

| ID | name | sn | ID | sn | Score |

+----+-----------+-------+------+-------+-------+

| 1 | Mashen |    10086 | 1 |    10086 | 90 |

| 2 | Haishen |    10087 | 2 |    10087 | 59 |

| 3 | Haoge |    10088 | 3 |    10088 | 77 |

| 8 | Left JOIN | 11122 |  NULL |  NULL | NULL |

+----+-----------+-------+------+-------+-------+

4 rows in Set (0.00 sec)

# # produce a set that is not in table B and is often used when the business asks for new additions:
650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/89/C0/wKioL1gb4wizUb2UAABaj4Q8oqk331.png-wh_500x0-wm_3 -wmp_4-s_2341308678.png "title=" Left1.png "alt=" Wkiol1gb4wizub2uaabaj4q8oqk331.png-wh_50 "/>

Mysql> SELECT * from a LEFT JOIN score in a.sn=score.sn where score.id is null;

+----+-----------+-------+------+------+-------+

| ID | name | sn | ID | sn | Score |

+----+-----------+-------+------+------+-------+

| 8 | Left JOIN | 11122 | NULL |  NULL | NULL |

+----+-----------+-------+------+------+-------+

1 row in Set (0.01 sec)

# # produce a set in table B that doesn't have in a table, which is the inner JOIN

Mysql> SELECT * from a LEFT JOIN score in a.sn=score.sn where score.id is not null

+----+---------+-------+------+-------+-------+

| ID | name | sn | ID | sn | Score |

+----+---------+-------+------+-------+-------+

| 1 | Mashen |    10086 | 1 |    10086 | 90 |

| 2 | Haishen |    10087 | 2 |    10087 | 59 |

| 3 | Haoge |    10088 | 3 |    10088 | 77 |

+----+---------+-------+------+-------+-------+

Right Join: The complete set of table B is generated, and the match in a table has a value, and no match is substituted with a null value, as opposed to a left join.

Mysql> SELECT * from a right joins score on A.SN=SCORE.SN

;

+------+---------+-------+----+-------+-------+

| ID | name | sn | ID | sn | Score |

+------+---------+-------+----+-------+-------+

| 1 | Mashen |  10086 | 1 |    10086 | 90 |

| 2 | Haishen |  10087 | 2 |    10087 | 59 |

| 3 | Haoge |  10088 | 3 |    10088 | 77 |

| NULL |  NULL |  NULL | 4 |    10089 | 77 |

| NULL |  NULL |  NULL | 5 |    10090 | 70 |

+------+---------+-------+----+-------+-------+

5 rows in Set (0.00 sec)

# # generates a set in table B that does not appear in the a table, no match shows null

Mysql> SELECT * from a right joins score on A.SN=SCORE.SN where a.id is null;

+------+------+------+----+-------+-------+

| ID | name | sn | ID | sn | Score |

+------+------+------+----+-------+-------+

| NULL | NULL |  NULL | 4 |    10089 | 77 |

| NULL | NULL |  NULL | 5 |    10090 | 70 |

+------+------+------+----+-------+-------+

This article is from the "DBSpace" blog, so be sure to keep this source http://dbspace.blog.51cto.com/6873717/1869210

MySQL JOIN Syntax description and illustration