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

This article introduces the join query and collection operation in MySQL. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Join query

Join query refers to the matching query between two or more tables, which is generally called horizontal operation, that is, the final result will contain all the columns in these tables. There are three join operations in MySQL, namely, cross join, inner join and outer join. [related recommendation: mysql video tutorial]

Cross join is called CROSS JOIN, he performs Cartesian product on two tables, he will return the composition of all the columns in the two tables, for example, if there are n pieces of data in the left table and m pieces of data in the right table, then the final result is n pieces of data, but you can also join yourself, then the end result is n entries, such as the following statement.

Select * from orders as a cross join orders as b +-+ | orderId | userId | orderId | userId | +-+ | 10007 | 2 | 10001 | 1 | 10006 | 4 | 10001 | 1 |. | 10002 | 1 | 10007 | 2 | 10001 | 1 | 10007 | 2 | +-+ 49 rows in set (sec)

Since there are seven pieces of data in the orders table, 49 pieces of data will be generated in the end, and another way to write it is as follows.

Mysql > select * from orders as a, orders as b

Their results are all the same, but the writing method is different. The above writing method is in 1989, the American National Standards Institute carried out the specification of SQL, called ANSI SQL 89 standard, and the first writing method was stipulated in 1992.

One of the uses of cross joins is to quickly generate duplicate data, such as the following statement.

Insert orders select a.orderId,a.userId from orders as a, orders as b limit 10

The following is the inner join, which is divided into two parts, first generating the Cartesian product, and then filtering according to the filtering conditions of the later ON, which produces values with the same record in both tables.

After ON, in addition to the equal operator (=), other operators can be used, such as greater than (>), less than (

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.