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

This article will explain in detail how to use JOIN in MySql. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

The meaning of JOIN is just like the English word "join". Join two tables, which are roughly divided into inner join, outer join, right join, left join and natural join.

First create two tables, the following is used for the example

CREATE TABLE t_blog (id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR (50), typeId INT); SELECT * FROM t_blog +-+ | id | title | typeId | +-- + | 1 | aaa | 1 | 2 | bbb | 2 | 3 | ccc | 3 | 4 | ddd | 4 | 5 | eee | 4 | 6 | fff | 3 | | 7 | ggg | 2 | 8 | hhh | NULL | | 9 | iii | NULL | | 10 | jjj | NULL | +-blog category CREATE TABLE t_type (id INT PRIMARY KEY AUTO_INCREMENT) Name VARCHAR (20)) SELECT * FROM tasking type; +-+-+ | id | name | +-+-+ | 1 | C++ | | 2 | C | | 3 | Java | 4 | C # | | 5 | Javascript | +-+-+ Cartesian product: CROSS JOIN

To understand all kinds of JOIN, you must first understand Cartesian product. Cartesian product is to forcibly combine every record of table A with every record of table B. So, if table A has n records and table B has m records, the result of Cartesian product will produce nimm records. In the following example, t_blog has 10 records, t_type has 5 records, and all of them have a Cartesian product of 50 records. There are five ways to produce Cartesian products as follows.

SELECT * FROM t_blog CROSS JOIN tweetype.SELECT * FROM t_blog INNER JOIN tweetype.SELECT * FROM tincture blog.authoring tactile type.SELECT * FROM t_blog NATURE JOIN tweetype.select * type +-id | title | typeId | id | name | +-aaa | 1 | 1 | C++ | | | 1 | aaa | 1 | 2 | C | | 1 | aaa | 1 | 3 | Java | | 1 | aaa | 1 | 4 | C # | 1 | aaa | 1 | 5 | Javascript | 2 | bbb | 2 | 1 | C++ | 2 | bbb | 2 | C | 2 | bbb | 2 | 3 | | Java | | 2 | bbb | 2 | 4 | C # | 2 | bbb | 2 | 5 | Javascript | 3 | ccc | 3 | 1 | C++ | 3 | ccc | 3 | 2 | C | 3 | ccc | 3 | Java | | 3 | ccc | 3 | 4 | C # | 3 | ccc | | 3 | 5 | Javascript | | 4 | ddd | 4 | 1 | C++ | | 4 | ddd | 4 | 2 | C | 4 | ddd | 4 | 3 | Java | 4 | ddd | 4 | C# | 4 | ddd | 4 | 5 | Javascript | 5 | eee | 4 | 1 | C++ | 5 | eee | 4 | 2 | C | | 5 | eee | 4 | Java | | 5 | eee | 4 | 4 | C # | 5 | eee | 4 | 5 | Javascript | 6 | fff | 3 | C++ | 6 | fff | 3 | C | 6 | fff | 3 | 3 | | Java | | 6 | fff | 3 | 4 | C # | 6 | fff | 3 | Javascript | 7 | ggg | 2 | 1 | C++ | 7 | ggg | 2 | C | 7 | ggg | 2 | Java | 7 | ggg | 2 | 4 | C # | 7 | ggg | | 2 | 5 | Javascript | | 8 | hhh | NULL | 1 | C++ | | 8 | hhh | NULL | 2 | C | 8 | hhh | NULL | 3 | Java | | 8 | hhh | NULL | 4 | C # | 8 | hhh | NULL | 5 | Javascript | 9 | iii | NULL | 1 | C++ | 9 | iii | NULL | 2 | C | | 9 | iii | NULL | 3 | Java | | 9 | iii | NULL | 4 | C # | 9 | iii | NULL | 5 | Javascript | | 10 | jjj | NULL | 1 | C++ | 10 | jjj | NULL | 2 | C | 10 | jjj | NULL | 3 | Java | 10 | jjj | NULL | 4 | C# | | 10 | jjj | NULL | 5 | Javascript | +-+ connection within: INNER JOIN

Inner connection INNER JOIN is the most commonly used connection operation. From a mathematical point of view, it is to find the intersection of two tables, and from the point of view of Cartesian product, it is to pick out the records of ON clause conditions from Cartesian product. There are four ways of writing: INNER JOIN,WHERE (equivalent connection) and STRAIGHT_JOIN,JOIN (omitting INNER).

SELECT * FROM t_blog INNER JOIN t_type ON tweak blog.typeIdflower type.id; SELECT * FROM tweak blogmall tonal type WHERE tonal blog.typeIddistributroomtype.id; SELECT * FROM t_blog STRAIGHT_JOIN t_type ON tonal blog.typeIdcharacters;-- notice that STRIGHT_JOIN has an underscore SELECT * FROM t_blog JOIN t_type ON t_blog.typeId=t_type.id. +-id | title | typeId | id | name | +-+ | 1 | 1 | C++ | 2 | bbb | 2 | C | | 7 | Ggg | 2 | 2 | C | | 3 | ccc | 3 | Java | | 6 | fff | 3 | Java | 4 | ddd | 4 | 4 | C # | 5 | eee | 4 | C # | +-+-- left connection: LEFT JOIN

The meaning of the left join LEFT JOIN is to find the intersection of two tables plus the rest of the data in the left table. Still from the point of view of Cartesian product, it is to pick out the records of the conditional ON clause from the Cartesian product, and then add the remaining records in the left table (see the last three).

SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id +-+ | id | title | typeId | id | name | +-+ | 1 | aaa | 1 | 1 | C++ | | 2 | bbb | 2 | 2 | C | | 7 | ggg | 2 | C | 3 | ccc | 3 | Java | 6 | fff | 3 | Java | 4 | ddd | 4 | 4 | C # | 5 | eee | 4 | C # | 8 | hhh | NULL | NULL | NULL | 9 | iii | NULL | NULL | NULL | | 10 | Jjj | NULL | NULL | NULL | +-+ right connection: RIGHT JOIN

In the same way, the right join RIGHT JOIN is to find the intersection of two tables plus the rest of the data in the right table. Again, from the perspective of Cartesian product, the right join is to pick out the records from the Cartesian product where the condition of the ON clause is valid, and then add the remaining records in the right table (see the last item).

SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id +-+-+ | id | title | typeId | id | name | +-+-+ | 1 | aaa | 1 | 1 | C++ | | 2 | bbb | 2 | 2 | C | | 3 | ccc | 3 | 3 | Java | 4 | ddd | 4 | 4 | C # | 5 | eee | 4 | 4 | C # | 6 | fff | 3 | Java | 7 | ggg | 2 | 2 | C | | | NULL | 5 | Javascript | +-+ external connection: OUTER JOIN

An outer join is to find the union of two sets. From the point of view of Cartesian product, it is to pick out the records with ON clause conditions from Cartesian product, then add the remaining records in the left table, and finally add the remaining records in the right table. In addition, MySQL does not support OUTER JOIN, but we can do UNION operations on the results of left and right connections.

SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id UNION SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id +-+ | id | title | typeId | id | name | +-+ | 1 | aaa | 1 | 1 | C++ | 2 | bbb | 2 | C | 7 | ggg | 2 | C | 3 | ccc | 3 | Java | 6 | fff | 3 | Java | 4 | ddd | 4 | 4 | C # | 5 | eee | | 4 | 4 | C# | | 8 | hhh | NULL | | 9 | iii | NULL | | 10 | jjj | NULL | | NULL | 5 | Javascript | +-+ USING clause |

In the join SQL statement in MySQL, the syntax format of the ON clause is: table1.column_name = table2.column_name. When the schema design uses the same naming style for the columns of the join table, you can use the USING syntax to simplify the ON syntax in the format: USING (column_name).

So, USING is equivalent to ON, except that USING specifies a property name to join two tables, while ON specifies a condition. In addition, when SELECT *, USING removes the column specified by USING, but ON does not. The example is as follows.

SELECT * FROM t_blog INNER JOIN t_type ON t_blog.typeId = t_type.id +-id | title | typeId | id | name | +-+ | 1 | 1 | C++ | 2 | bbb | 2 | C | | 7 | Ggg | 2 | 2 | C | | 3 | ccc | 3 | Java | | 6 | fff | 3 | Java | 4 | ddd | 4 | 4 | C # | 5 | eee | 4 | C # | +-- +-+ SELECT * FROM t_blog INNER JOIN t_type USING (typeId) ERROR 1054 (42S22): Unknown column 'typeId' in' from clause' SELECT * FROM t_blog INNER JOIN t_type USING (id);-- because the typeId of t_blog has a different name from the id of t_type, so Using cannot be used. Here, use id instead. +-+ | id | title | typeId | name | +-+ | 1 | aaa | 1 | C++ | | 2 | bbb | 2 | C | | | 3 | ccc | 3 | Java | | 4 | ddd | 4 | C # | | 5 | eee | 4 | Javascript | +-+ Natural connection: NATURE JOIN |

A natural join is a simplified version of the USING clause that finds the same columns in two tables as join conditions. It can be divided into left natural connection, right natural connection and ordinary natural connection. In the t_blog and t_type examples, the same column for both tables is id, so id is used as a join condition.

Also, be sure to distinguish the difference between the following three sentences.

Natural connection: SELECT * FROM t_blog NATURAL JOIN t_type

Cartesian product: SELECT * FROM t_blog NATURA JOIN t_type

Cartesian product: SELECT * FROM t_blog NATURE JOIN t_type

SELECT * FROM t_blog NATURAL JOIN tweak blog.idtitletitle; SELECT treadblog.name FROM tactile blog.id; SELECT treadblog.idllegtitletitletype.name FROM t_blog INNER JOIN t_type ON tabilblog.idllegtitletitletype.name FROM t_blog INNER JOIN t_type ON tabilblog.idtitletitletitletype.id; SELECT tweak blog.idtitletitletitle typeId.name FROM t_blog INNER JOIN t_type USING (id) +-- + | id | title | typeId | name | | 1 | aaa | 1 | C++ | 2 | bbb | 2 | C | | 3 | ccc | 3 | Java | | 4 | ddd | 4 | C# | 5 | eee | 4 | Javascript | SELECT * FROM t_blog NATURAL LEFT JOIN t_type SELECT tincture blog.idretitleGravity typeIdreparenttype.name FROM t_blog LEFT JOIN t_type ON tantalblog.idwritten tactile type.id; SELECT tabilblog.idtitletitle FROM t_blog LEFT JOIN t_type USING typeIddiretype.name FROM t_blog LEFT JOIN t_type USING (id) | | 6 | fff | 3 | NULL | | 7 | ggg | 2 | NULL | | 8 | hhh | NULL | NULL | 9 | iii | NULL | NULL | | 10 | jjj | NULL | NULL | SELECT * FROM t_blog NATURAL RIGHT JOIN tactile type; SELECT tactile blog.idtitledytypeIdname FROM t_blog RIGHT JOIN t_type ON t_blog.id=t_type.id | SELECT tweeblog.idretitleReciprocedtypeIdrewrittype.name FROM t_blog RIGHT JOIN t_type USING (id) +-- + | id | name | title | typeId | | 1 | C++ | aaa | 1 | | 2 | C | bbb | 2 | 3 | Java | ccc | 3 | | 4 | C # | ddd | 4 | | 5 | Javascript | eee | 4 | this is the end of the article on how to use JOIN in MySql. Hope that the above content can be helpful to you, so that you can learn more knowledge, if you think the article is good, please 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.