In-depth query of SELECT statements (2) (1)

Go to "query functions of SELECT statements in depth".

Connection between search statements and multiple tables

The SELECT statement not only retrieves data from a single table, but also connects multiple tables to retrieve data. This section describes the functions of full and left connections.

We create two tables as an example.

mysql> CREATE TABLE first-> (-> id TINYINT,-> first_name CHAR(10)-> );

Enter the following data:

+ ------ + ----------- +

| Id | first_name |

+ ------ + ----------- +

| 1 | Tom |

| 2 | Marry |

| 3 | Jarry |

+ ------ + ----------- +

mysql> CREATE TABLE last-> (-> id TINYINT,-> last_name CHAR(10)-> );

Input data

+ ------ + ----------- +

| Id | last_name |

+ ------ + ----------- +

| 2 | Stone |

| 3 | White |

| 4 | Donald |

+ ------ + ----------- +

Full connection

Full join: Multiple tables are specified during retrieval, and each table is separated. In this way, each table's data row and each row of other tables are combined to generate all possible combinations, this is a full connection. All possible groups and numbers are the total number of rows in each table.

Then observe the following search results:

Mysql> SELECT * FROM first, last;

+ ------ + ------------ + ------ + ----------- +

| Id | first_name | id | last_name |

+ ------ + ------------ + ------ + ----------- +

| 1 | Tom | 2 | Stone |

| 2 | Marry | 2 | Stone |

| 3 | Jarry | 2 | Stone |

| 1 | Tom | 3 | White |

| 2 | Marry | 3 | White |

| 3 | Jarry | 3 | White |

| 1 | Tom | 4 | Donald |

| 2 | Marry | 4 | Donald |

| 3 | Jarry | 4 | Donald |

+ ------ + ------------ + ------ + ----------- +

You can see that there are 3x3 = 9 rows in the output result set, which is the result of the full connection.

You can also use the SQL statement as follows:

Mysql> SELCT first. *, last. * FROM first, last;

The output result is the same as that in the preceding example. The output sorting of the record set is performed in the order of the tables after the FROM clause, that is, the tables at the top are arranged first, even if you change the display order of the columns in the record set, for example:

Mysql> SELECT last. *, first. * FROM first, last;

+ ------ + ----------- + ------ + ------------ +

| Id | last_name | id | first_name |

+ ------ + ----------- + ------ + ------------ +

| 2 | Stone | 1 | Tom |

| 2 | Stone | 2 | Marry |

| 2 | Stone | 3 | Jarry |

| 3 | White | 1 | Tom |

| 3 | White | 2 | Marry |

| 3 | White | 3 | Jarry |

| 4 | Donald | 1 | Tom |

| 4 | Donald | 2 | Marry |

| 4 | Donald | 3 | Jarry |

+ ------ + ----------- + ------ + ------------ +

The above example is an example of two very small tables. For example, if there is a full connection between several very large tables, for example, two tables with 1000 rows respectively, such a connection can generate a very large result set of 1000 × 1000 = 1 million rows. In fact, you do not need the results of so many rows. Generally, you need to use a WHERE clause to limit the number of rows in the returned record set:

Mysql> SELECT * FROM first, last WHERE first. id = last. id;

+ ------ + ------------ + ------ + ----------- +

| Id | first_name | id | last_name |

+ ------ + ------------ + ------ + ----------- +

| 2 | Marry | 2 | Stone |

| 3 | Jarry | 3 | White |

+ ------ + ------------ + ------ + ----------- +