A brief analysis of join queries and set operations in MySQL learning

This article will take you through the join query and set operations in MySQL. I hope it will be helpful to you!

Join query Join query refers to a matching query between two or more tables, generally called horizontal Operation means that the final result will contain all the columns in these tables. There are three join operations in MySQL, cross join, inner join, and outer join. [Related recommendations: mysql video tutorial ]

Cross join is called CROSS JOIN . It performs Cartesian product on two tables, and it will return the two tables. The composition of all columns, for example, 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*m , but you can also connect it to yourself, then the final result is n*n items, 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 (0.01 sec) Since there are 7 pieces of data in the orders table, 49 pieces of data will be generated in the end. Another way to write it is as follows.

mysql> select * from orders as a ,orders as b; Their results are the same, but the writing method is different. The above writing method was standardized for SQL by the American National Standards Institute in 1989, called the ANSI SQL 89 standard, and the first writing method was 92 year regulations.

Cross joins are useful for quickly generating repeated 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 an inner join, which is divided into two parts. First, a Cartesian product is generated, and then it is filtered according to the subsequent ON filtering conditions. It generates values with the same records in the two tables.

In addition to the equal operator (=) after ON, you can also use other operators, such as greater than ( > ), less than ( ) and not equal to ( ) operators to form join conditions.

The last one is OUTER JOIN, which can match data between tables according to some filtering conditions. Different from INNER JOIN, there are unfound matching data in the reserved table added through OUTER JOIN. MySQL supports LEFT OUTER JOIN, RIGHT OUTER JOIN, you can omit OUTER when writing.

The following is a diagram of LEFT JOIN. LEFT JOIN returns all records in the left table (table1) and matching records in the right table (table2).

The following is a diagram of RIGHT JOIN. RIGHT JOIN returns all records in the right table (table2) and matching records in the left table (table1).

Set operation There is a UNION operator in MySQL, which is used to combine 2 or more SELECT result sets and Delete duplicate rows between SELECT statements. When using it, follow the following basic rules:

The number and order of columns appearing in all SELECT statements must be the same. Columns must also be of the same data type. If you have the following table now

CREATE TABLE t1 ( id INT PRIMARY KEY );

CREATE TABLE t2 ( id INT PRIMARY KEY );

INSERT INTO t1 VALUES (1),(2),(3); INSERT INTO t2 VALUES (2),(3),(4); Execute the following SQL

SELECT id FROM t1 UNION SELECT id FROM t2; The final result is like this.

+----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | +----+ 4 rows in set (0.00 sec) The UNION statement will delete duplicate data from the result row set by default, but you can use UNION ALL to obtain duplicate records.

SELECT id FROM t1 UNION ALL SELECT id FROM t2; The results are as follows

+----+ | id | +----+ | 1 | | 2 | | 3 | | 2 | | 3 | | 4 | +----+ 6 rows in set (0.00 sec) The basic difference between UNION and JOIN is that UNION combines the result set horizontally, while the JOIN statement combines the result set vertically.

Original address: https://juejin.cn/post/7001772087534682143

Author: i Tingfeng Passing Night

For more programming-related knowledge, please visit: Programming Video ! !

A brief analysis of join queries and set operations in MySQL learning

Related articles