MySQL multi table connection query, sub query [MySQL]

Posted by mottwsc on Tue, 16 Nov 2021 16:44:58 +0100

Java cultivation program -- the 68th day of learning punch in

Java (punch in the 68th day)

MySQL multi table connection query (MySQL 99)

Yesterday, we shared the multi table MySQL l92 syntax of MySQL. The disadvantage of this syntax is to mix the multi table connection conditions with the common filter conditions. At the same time, we introduced the equivalent connection, that is, the connection conditions are equal conditions

mysql> SELECT
    -> f.fruits_name,c.language
    -> FROM
    -> fruits f
    -> JOIN
    -> culture c
    -> ON
    -> f.fruits_origin = c.location;
+-------------+----------+
| fruits_name | language |
+-------------+----------+
| Grape        | Shandong Dialect   |
| Apple        | Shandong Dialect   |
| Banana        | Hainan Dialect   |
+-------------+----------+
3 rows in set (0.01 sec)

Next, we will introduce the non equivalent connection of inner connection

Non equivalent connection of inner connection

The non equivalent connection of inner connection means that the connection conditions of two tables are no longer equal conditions, but other conditions

That is, the statement after the keyword ON is a non equivalent condition

Here is an example. For example, if two of them rank the price in the fruits table in the cfeng library, the non equivalent condition will be used

mysql> SELECT
    ->  f.fruits_name 'Fruit name',f.fruits_price 'Fruit price',g.grade 'Price grade'
    -> FROM
    -> fruits f
    -> JOIN
    -> fruitsgrade g
    -> ON
    -> f.fruits_price BETWEEN g.lowpri AND g.highpri;
+----------+----------+----------+
| Fruit name | Fruit price | Price grade |
+----------+----------+----------+
| Grape     |      3.7 | commonly     |
| Apple     |      2.8 | cheap     |
| Banana     |        6 | expensive     |
+----------+----------+----------+
3 rows in set (0.00 sec)

It can be seen that the condition is not an equal quantity relationship, and the JOIN is the same as the INNER JOIN

Self connection of internal connection [the key is to see two tables]

For example, here is an employee table, which contains the employee's id, name and leader's id. now it is required to look up the table to get the name of each employee's superior leader

The solution is to treat one table as two tables

Therefore, the solution to this problem is to treat it as two tables in the future. One table is the employee table and the other is the leader table. The query is an equivalent query. The mgl of the employee table and the id of the leader table are equal as the query criteria

Here is the original watch

mysql> SELECT
    -> *
    -> FROM
    -> emp;
+----+-------+-------+
| id | name  | splid |
+----+-------+-------+
|  1 | zhang |     4 |
|  2 | wang  |     3 |
|  3 | liu   |     4 |
|  4 | huang |     5 |
|  5 | hu    |     0 |
+----+-------+-------+
5 rows in set (0.00 sec)

It is treated as an equivalent query. When joining, it is also the same table, which can be distinguished by aliases

mysql> SELECT
    -> e.name 'Employee name',b.name 'Leader name'
    -> FROM
    -> emp e
    -> JOIN
    -> emp b
    -> ON
    -> e.splid = b.id;     //The two fields are the same
+----------+----------+
| Employee name | Leader name |
+----------+----------+
| wang     | liu      |
| liu      | huang    |
| zhang    | huang    |
| huang    | hu       |
+----------+----------+
4 rows in set (0.00 sec)

Here, we alias the employee table e and the leader table b; but they are all tables emp

The above example is the inner connection. Note that there is one less query record than the table

External connection LEFT RIGHT [OUTER can be omitted]

Two tables connected internally are equal, and two tables connected externally are primary and secondary

LEFT OUTER RIGHT OUTER

Any left connection has the writing method of right connection, and the right connection is the same

LEFT regards the table on the LEFT of the JOIN keyword as the main table, mainly to query all the data in the main table, but to query the right table
RIGHT regards the table on the RIGHT of the JOIN keyword as the main table, mainly to query all the data in the main table, but to query the left table

In fact, it is simpler to select a main table. The privilege of the main table is not only to display the matched data, but also to display the unmatched data (that is, to display all data)

The characteristic of inner connection is that only the data that can completely match this condition can be queried

For example, the inner connection of the above fruit

+----------+----------+
| location | language |
+----------+----------+
| Sichuan     | Sichuan dialect   |
| Shandong     | Shandong Dialect   |
| Hainan     | Hainan Dialect   |
+----------+----------+

This is the culture table. There are three rows of records to match

Then after using the SQL statement

+-------------+----------+
| fruits_name | language |
+-------------+----------+
| Grape        | Shandong Dialect   |
| Apple        | Shandong Dialect   |
| Banana        | Hainan Dialect   |
+-------------+----------+
3 rows in set (0.01 sec)

You can see that the final display result does not contain the first row record of the culture table, which is caused by the fact that only the query data that meets the query conditions can be queried

What if you want to query the data without matching here?

At this time, the external connection should be used

Syntax format of external connection

SELECT ...... FROM table1 RIGHT JOIN table2 ......

This is a right JOIN, which means that there is no query result, and no matching data in the right table will be displayed, that is, the table after the JOIN

The above query is just that the JOIN table has data and remains

mysql> SELECT
    -> f.fruits_name,c.language
    -> FROM
    -> fruits f
    -> RIGHT JOIN
    -> culture c
    -> ON
    -> f.fruits_origin = c.location;
+-------------+----------+
| fruits_name | language |
+-------------+----------+
| NULL        | Sichuan dialect   |
| Apple        | Shandong Dialect   |
| Grape        | Shandong Dialect   |
| Banana        | Hainan Dialect   |
+-------------+----------+
4 rows in set (0.01 sec)

It can be seen that there is one more row of data than the previous query, that is, the first row of data in the culture table that does not match

The second is LEFT JOIN. The LEFT JOIN keyword is LEFT, and the data not matched in the table on the LEFT of the JOIN will be displayed additionally, which is also different from the table above the JOIN

SELECT ...... FROM table1 LEFT JOIN table2 ......

The example here is to reverse the order of the two columns in the above table and query fruits_name by language

mysql> SELECT
    -> c.language,f.fruits_name
    -> FROM
    -> culture c
    -> LEFT JOIN
    -> fruits f
    -> ON
    -> f.fruits_origin = c.location;
+----------+-------------+
| language | fruits_name |
+----------+-------------+
| Sichuan dialect   | NULL        |
| Shandong Dialect   | Apple        |
| Shandong Dialect   | Grape        |
| Hainan Dialect   | Banana        |
+----------+-------------+
4 rows in set (0.00 sec)

The number of external connection query results must be > = the number of internal connection query results. If the mapping of two tables is bijection, the equal sign holds

When querying the leader's name above, a record is missing because some data do not match. You can use external connection. For example, it is required to query the leader's name of all employees

It can be seen from the requirements that all employees are emphasized, so the employee table is the main table

mysql> SELECT
    -> e.name 'Employee name',b.name 'Leader name'
    -> FROM
    -> emp e
    -> LEFT JOIN
    -> emp b
    -> ON
    -> e.splid = b.id;
+----------+----------+
| Employee name | Leader name |
+----------+----------+
| zhang    | huang    |
| wang     | liu      |
| liu      | huang    |
| huang    | hu       |
| hu       | NULL     |
+----------+----------+
5 rows in set (0.00 sec)

This queries all data, including NULL

Here, the left connection is used at the same time

Connection of multiple tables

The above is about the connection of two tables. How to connect multiple tables

To put it bluntly, it is to JOIN ON many times

Basic format

SELECT
......
FROM
table_a
JOIN
table_b
ON
a Table and b Table connection conditions
JOIN
table_c
ON
a Table and c Table connection conditions
JOIN
table_d
ON
a Table and d Table connection conditions

Note that you should not bypass yourself here. The connection conditions here are all for querying the fields of table a, so they are the connection conditions between table a and other tables

For example, the fruits table, the culture table and the fruits grade table are queried together, one time equivalent connection and one time non equivalent connection

mysql> SELECT
    -> f.fruits_name 'Fruit name',c.language 'Local dialect',g.grade 'Price grade'
    -> FROM
    -> fruits f
    -> JOIN
    -> culture c
    -> ON
    -> f.fruits_origin = c.location
    -> JOIN
    -> fruitsgrade g
    -> ON
    -> f.fruits_price BETWEEN g.lowpri AND g.highpri;
+----------+----------+----------+
| Fruit name | Local dialect | Price grade |
+----------+----------+----------+
| Grape     | Shandong Dialect   | commonly     |
| Apple     | Shandong Dialect   | cheap     |
| Banana     | Hainan Dialect   | expensive     |
+----------+----------+----------+
3 rows in set (0.01 sec)

It can be understood that the culture table is connected first, and then another table is connected

In multi table connection, internal and external connections can be used at the same time

Even if one keeps adding JOIN and ON, it can also use self connection at the same time

Subquery

A subquery is a subquery in which a SELECT statement is nested in a SELECT statement, because the keyword of the query is SELECT

Where subqueries can appear

SELECT
......(SELECT)
FROM
...... (SELECT)
WHERE
...... (SELECT)
//That is, sub queries can appear after SELECT, FROM and WHERE

Subquery in WHERE clause

Previously, when sharing grouping functions and multiline functions, it was mentioned that grouping returns cannot be used directly in the WHERE clause, because GROUP BY is executed after WHERE and before SELECT. Grouping functions cannot be used before there is no grouping

For example, query all the data of the city with the lowest population in the city table

mysql> SELECT
    -> *
    -> FROM
    -> city
    -> WHERE
    -> Population = MIN(Population);
ERROR 1111 (HY000): Invalid use of group function

This will report an error: invalid use of grouping function

Therefore, the correct idea is to find out the minimum population first, and then query the city according to the minimum population

1. SELECT MIN(Population) FROM city;
2. SELECT * FROM city WHERE Population = min;

Then merge, that is, replace min with the above statement

SELECT * FROM city WHERE Population = (SELECT MIn(Population) FROM city);

You can see if it can be used after nesting

mysql> SELECT
    -> *
    -> FROM
    -> city
    -> WHERE
    -> Population = (SELECT MIN(Population) FROM city);
+------+-----------+-------------+----------+------------+
| ID   | Name      | CountryCode | District | Population |
+------+-----------+-------------+----------+------------+
| 2912 | Adamstown | PCN         | –       |         42 |
+------+-----------+-------------+----------+------------+
1 row in set (0.01 sec)

Nested statements are written in one line to facilitate nesting

The order of sub query is to execute the sub query first before the parent query. Here, execute the query statement in parentheses before executing the outer statement

Subquery of FROM clause

For the sub query after FROM, you can treat the results of the sub query as a temporary table

In fact, it is not difficult to understand that from is to obtain the table, WHERE is to filter, and the sub query behind from is of course a temporary table

Give me a case

Find the population level of the average population of each country code in the top 100 cities in the city table?

The query target here can also be divided into two steps

1.Query the average population of each country code in the top 100 cities
SELECT CountryCode,AVG(Population) FROM city WHERE ID <= 100 GROUP BY CountryCode
2.Query the population level corresponding to the average population of each country code
 The average population queried here forms a temporary table temp，Combine the table with populationgrade Connection acquisition level
SELECT temp.CountryCode,temp.AVG(Population),g.grade FROM temp JOIN populationgrade g ON temp.temp.AVG(Population) BETWEEN g.lowpop AND g.highpop

In this way, you can see that you can put the above query statement in the following temp position and give it an alias

mysql> SELECT
    -> c.CountryCode 'Country code',c.AVG(Population) 'Average population',g.grade 'Population level'
    -> FROM
    -> (SELECT CountryCode,AVG(Population) FROM city WHERE ID <= 100 GROUP BY CountryCode) c
    -> JOIN
    -> populationgrade g
    -> ON
    -> c.AVG(Population) BETWEEN g.lowpop AND g.highpop;
ERROR 1630 (42000): FUNCTION c.AVG does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

The reason for the error is that the AVG function in the following ON does not exist, and SQL will treat it as a function, so the error is reported. The solution here is to alias the fields in the above sub query

mysql> SELECT
    ->  c.CountryCode 'Country code',c.pop 'Average population',g.grade 'Population level'
    -> FROM
    -> (SELECT CountryCode,AVG(Population) AS pop  FROM city WHERE ID <= 100 GROUP BY CountryCode) c
    -> JOIN
    -> populationgrade g
    -> ON
    -> c.pop BETWEEN g.lowpop AND g.highpop;
+------+-------------+----------+
| Country code | Average population    | Population level |
+------+-------------+----------+
| AFG  | 583025.0000 | Densely populated |
| NLD  | 185001.7500 | Normal population |
| ANT  |   2345.0000 | sparse population |
| ALB  | 270000.0000 | Normal population |
| DZA  | 288454.3889 | Normal population |
| ASM  |   3761.5000 | sparse population |
| AND  |  21189.0000 | Normal population |
| AGO  | 512320.0000 | Densely populated |
| AIA  |    778.0000 | sparse population |
| ATG  |  24000.0000 | Normal population |
| ARE  | 345667.2000 | Densely populated |
| ARG  | 513810.6250 | Densely populated |
+------+-------------+----------+
12 rows in set (0.00 sec)

SELECT sub sentence query

mysql> SELECT
    -> f.fruits_name,(SELECT c.language FROM culture c WHERE c.location = f.fruits_origin)
    -> FROM
    -> fruits f;
+-------------+-----------------------------------------------------------------------+
| fruits_name | (SELECT c.language FROM culture c WHERE c.location = f.fruits_origin) |
+-------------+-----------------------------------------------------------------------+
| Grape        | Shandong Dialect                                                                |
| Apple        | Shandong Dialect                                                                |
| Banana        | Hainan Dialect                                                                |
+-------------+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)

It is not recommended here. In fact, it is a table connection. The problem is that the results of sub queries are chaotic if they are not a field

Therefore, the previous two sub queries are mainly used

That's all for today. There will be more dry goods tomorrow~ 🌳

Topics: Database MySQL

Programmer Think