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

This article mainly explains "how to understand MySQL connections and sets". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to understand MySQL connections and sets".

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.

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.