A Brief Analysis of MySQL Connections and Collections

Join query

A join query refers to a matching query between two or more tables, generally referred to as a horizontal operation, which means that the final result will include all the columns in these tables. There are three types of join operations in MySQL: cross join, inner join, and outer join.

A cross join is called a CROSS JOIN. It performs a Cartesian product on two tables and returns the composition of all columns in the two tables. For example, if there are n records in the left table and m records in the right table, the final result is n*m records. However, it can also join with itself, and the final result is n*n records, 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 records in the orders table, 49 records 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 they are written differently. The above writing method is the standard for SQL made by the American National Standards Institute in 1989, called the ANSI SQL 89 standard, while the first writing method was stipulated in 1992.

One use 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 an inner join, which is divided into two parts. First, the Cartesian product is generated, and then it is filtered according to the filter condition of ON. It generates values ​​​​that have 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 the connection condition.

The last one is OUTER JOIN, which can match data between tables according to some filtering conditions. Unlike INNER JOIN, there is unmatched data in the reserved table added by OUTER JOIN. MySQL supports LEFT OUTER JOIN and RIGHT OUTER JOIN, and OUTER can be omitted when writing.

Below is a diagram showing LEFT JOIN. LEFT JOIN returns all records from the left table (table1) and the matching records from the right table (table2).

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

Set Operations

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

• The number and order of columns must be the same in all SELECT statements.
• The columns must also have the same data type.

If we now have the following table

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 end result is this.

+----+
|id|
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)

By default, the UNION statement removes duplicate data from the result set, 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 result sets horizontally, whereas JOIN statements combine result sets vertically.

Summarize

This is the end of this article about MySQL connections and collections. For more relevant MySQL connections and collections, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!