The interviewer asks: How do you use multi-table queries? Quickly get you the secret to multi-table queries

MySQL Basics (6) ----- subqueries and join queries

In short, a subquery is nested one or more query statements in a query;

A join query refers to joining multiple tables or a single table together for querying

The tables involved in this article are still from the MySQL Basic (4) ----- simple queries and conditional queries_Spring@W blog - CSDN blog, if you don't know, you can check it out

1. Subqueries

1. Simple multi-table query

So far, our queries have been on a single table, if we need to query data from multiple tables. So how should we deal with it? Sample question, how should I check the grades of students named 'Hanxin'?

The dumbest way to deal with it:

• First check the student ID of Hanxin from the student information form;
• Then check the grade sheet according to the student ID;

Practical effect:

But what if we want to query more complex cases? The method introduced next can solve it for us

2. Standard quantum query

Looking at the above query statement, the analysis can show that the conditions of the second query statement are actually the results of the first statement. For the sake of simplicity, let's combine the two into one.

sql复制代码SELECT * from student_score WHERE number = (SELECT id from student WHERE `name`='韩信');
           

You can find that the results are consistent with the results of separate queries. Why is that?

• We enclose the second query statement in parentheses and put it as an operand at the search condition of the first query statement, which plays the role of merging the two query statements;
• The query statement in parentheses is also called a subquery or inner query, and a query that uses the results of a subquery as a search condition is called an outer query;
• If you need more tables in a query statement, you can continue to nest another subquery within one subquery, and the queries will be executed in inside-out order when the query statement is executed.
• All subqueries must be enclosed in parentheses, otherwise it is illegal!

In the above example, the result of the subquery has only one value (that is, Hanxin's student number), and this subquery is called a scalar quantum query.

• Because a scalar quantum query simply represents a value, it can participate in operations as an operand of an expression;
• In addition to being used in the search conditions of the outer query, the standard quantum query can also be placed at the query list. (Example below)

• Because scalar quantum queries represent a value simply, they can be joined with other operands through operators to form more complex expressions. We often use it after the where clause. Example below

3. Column subquery

If we want to query the grades of MySQL technology students, we must first find the student's ID according to the major name from the student information form student, and then check the grade table according to the ID student_score to get the corresponding score. Analysis: Method 1:

Method 2:

sql复制代码SELECT * FROM student_score WHERE number IN(SELECT id from student WHERE major='MySQL技术');
           

The search condition in method one uses the query result of the first query statement, and we can use the first statement as the inner query and the second as the outer query on the basis of the standard quantum query, so as to obtain the statement of our method two.

In the statement of method two, the result set of the subquery is not a single value, but a column (the id column, which contains 2 values, which are 20230103 and 20230104), so this subquery is also called a column subquery.

4. Row subquery

There are column subqueries, and of course there will definitely be row subqueries. As long as the result set of a subquery contains at most one record, and the record contains more than one column of data (if the record contains only one column, the subquery becomes a standard quantum query), then the subquery can be called a row subquery. Examples are as follows:

edit

Then the subquery limits the return of a maximum of one record, and the subquery can be treated as a row subquery

Note: When you want to get a scalar quantum query or row subquery, but you cannot guarantee that the result set of the subquery has only one record, you need to use the limit 1 clause to limit the number of records in the result set

In the previous article we introduced that an operand is a single value. However, the result set produced by the subquery execution in the above example is a row (containing 2 columns), so the other operand to compare with it must also contain 2 values, in the above statement (number, subject) (note that this place must be enlarged with parentheses, otherwise it will cause ambiguity). The semantics it expresses are to get the execution result in the subquery first, and then execute the outer query, and if the number of the record in the score table is equal to the number column in the subquery, and the subject column is equal to 'computer science and technology', then the record is added to the result set.

5. Table subquery

If the result set of a subquery contains many rows and columns, the subquery can also be called a table subquery

Example:

The result set after the subquery execution contains many rows and columns, so it can be treated as a table subquery.

6. EXISTS and NOT EXISTS subqueries

Sometimes the outer query does not care what the result of the subquery is, only whether the result set of the subquery is empty. You can use the operators EXISTS and NOT EXISTS.

Example:

Because there are no students majoring in 'hahahahahahah' in the subquery table, the returned result is empty, so the entire statement cannot query the data.

In the statement containing the [NOT] EXISTS subquery, since we only care whether the result set of the subquery is empty and not what the specific result is, we can write anything in the query list of the subquery, and we do not necessarily have to write '*';

7. Unrelated subqueries and related subqueries

In the previous subquery, the subquery can run independently and produce results, and then use the results as a condition of the outer query to execute the outer query, which is called an irrelevant subquery.

Example:

In the subquery in the sample, only the student table is used without the grade table, which can run independently and produce results, so this is a typical uncorrelated subquery.

Sometimes, we need to reference the columns of the outer query in the statement of the subquery, so that the subquery cannot be executed as a separate statement, and this subquery is called a correlation subquery.

Example:

sql复制代码SELECT id number,sn,`name`,id_number,major FROM student WHERE EXISTS (SELECT * FROM student_score WHERE student.id=student_score.number);
           

Note that when the columns in two tables are the same, you can precede the table name to distinguish when writing in the where statement of the subquery. Write directly: the same column name = the same column name will cause ambiguity, will confuse the MySQL server, do not know which table this column belongs to. Correct writing: Table 1.Same column name = Table 2.Same column name, even if the table name is not the same, you can write it this way, this display of the name of the table to which the column belongs is called the fully qualified name of the column.

8. Subqueries on the same table

Subqueries can be performed not only on different tables, but also on the same table. Examples are as follows:

sql复制代码SELECT * FROM student_score WHERE `subject` = 'MySQL技术' AND score >(SELECT AVG(score) FROM student_score WHERE `subject` = 'MySQL技术');
           

2. Connection query

Before introducing join queries, let's redefine the relational tables. We combined the student sheet and the grade sheet and named it student_merge

In this way, it is convenient for us to query the data, but its disadvantages are also obvious.

• Waste of storage space - we have to save students' information every time we add a new subject grade, repeat the operation and still save it repeatedly;
• Difficulty in maintenance - when modifying the basic information of a student, the place to be modified will be all the records containing the student, which can easily lead to inconsistency of information and increase the difficulty of maintenance.

Therefore, in order to reduce the redundant information stored as much as possible and reduce the difficulty of maintenance, we divided this consolidated table into a student table and a student grade table as soon as we started. But there is a relationship between the two tables as a link, and here a relationship refers to the student ID that both tables have.

1. The concept of connection

After we split the table into a student table and a grade table, we did solve the problem of data redundancy, but the query of data became a problem. So far, all the queries introduced in all pages have not been able to query all the information of a student. Even subqueries are not possible, although subqueries can design multiple tables in a query statement, but the final result set produced by the entire query statement is still used to display the results of the outer query, and the results of the subquery are only used as intermediate results.

Let's recreate two test tables and insert data for connection queries.

sql复制代码CREATE TABLE t1(
	a1 int,
	b1 char(1)
);
CREATE TABLE t2(
	a2 int,
	b2 char(1)
);

INSERT INTO t1 VALUES(1,'a'),(2,'b'),(3,'c');
INSERT INTO t2 VALUES(2,'b'),(3,'c'),(4,'d');
           

At present, we have created two tables, t1 and t2, and added three pieces of data to each table.

The essence of joining two tables is to combine the records in one table and the records in another table, and add the combined records to the final result set. The process of joining the T1 and T2 tables is shown in the figure

This process seems to be joining the records of the T1 table and the records of the T2 table to form a new and larger record, so this query process is called a join query.

Without any filter conditions, the result set produced by joining two tables is also known as the Cartesian product. Because there are 3 records in table t1 and 3 records in t2, the Cartesian product after the two tables are joined has 33=9 records.

Syntax for join queries:

sql复制代码	SELECT * FROM 表名1,表名2;
           

Example:

The '*' at the query list represents all columns of each table to be read from clause. The above statement is actually equivalent to the following writing.

• Writing method 1: This writing method is to explicitly write out the column names in the T1 and T2 tables, that is, the fully qualified names of the columns are used;

• Writing method 2: Since the column names in the t1 and t2 tables are not duplicated, there is no ambiguity that may make the server confused, so it is also possible to use the column names directly on the query list;

• Writing method three: This writing means to query all the columns of the T1 table and all the columns of the T2 table.

2. Filter conditions for connection queries

When the table has more data, the result of the Cartesian product is very large, so we must specify the filter conditions when performing the join query.

There are two types of filter criteria in join queries

• Conditions involving a single table: This kind of filter condition that only involves a single table has been mentioned many times, and it has always been called a search condition in the previous article, such as t1.a1>1 is only a filter condition for the t1 table;
• Conditions involving two tables: We have not seen this kind of filter before, such as t1.a1=t2.a2, t1.b1>t2.b2, etc., these conditions find that involves two tables

Sample analysis:

Filter criteria:

• t1.a1 > 1
• t1.a1 = t2.a2
• t2.b2 < 'd'

Query procedure analysis:

• First, determine the first table that needs to be queried, which is the driving table. Assuming that t1 is used as the driver table, then it is necessary to find records in the t1 table that satisfy t1.a1>1. There are 2 records that meet the criteria;
• For two records in the drive table that meet the conditions of t1.a1>1, you need to find the matching records in the t2 table (the so-called matching records refer to the records that meet the filter conditions). Because the records in the T2 table are looked up based on the records in the T1 table, the T2 table can also be called the driven table. There are two records that meet condition 1, so check the t2 table twice, and the conditions for checking the two times are: a1=a2=2, t1.a1=t2.a2 AND t2.b2<'d'; a1=a2=3, t1.a1=t2.a2 AND t2.b2<'d';

From the above process analysis, it can be seen that in the two-table join query, the driving table only needs to be queried once, and the driven table may be queried multiple times.

3. Inner connection and outer connection

Suppose a requirement, we want to query the basic new of the student, but also query the student's performance information.

This requirement requires a two-table query. The connection process is to take out the records from the student table, find the records with the same student ID in the grade table, and the filter condition is student.id=student.number. Example:

ini复制代码SELECT student.id number,sn,`name`,department,major,`subject`,score FROM student,student_score WHERE student.id=student_score.number;
           

From the results, we can see that except for 20230105 and 20230106, the information of other students is displayed, because these two students may not have taken the test for some reason, so they have no results;

But we want to see all the records, and the essence of this result is: the records in the driving table need to be added to the result set even if there are no matching records in the driven table;

So we have inner and outer joins:

• For two joined tables, if a record in the drive table cannot find a matching record in the driven table, the record is not added to the final result set. The connections mentioned above are all inner joins;
• For two tables on an outer join, even if the records in the drive table do not have matching records in the driven table, they still need to be added to the result set.

However, there is still a problem, for outer joins, sometimes we do not want to add all the records of the driver table to the final result set. This is where the ON clause is required.

Where clause and on clause filters have different meanings:

• Filter conditions in the where clause - we have been putting filter conditions in the where clause summary, whether it is an inner join or an outer join, all records that do not meet the filter conditions in the where clause will not be added to the final result set;
• Filter condition in the on clause - For records of the driver table of the outer join, if a record matching the filter condition in the on clause cannot be found in the driven table, the record will still be added to the result set, and the fields of the corresponding driven table record are filled with NULL values

Note: The special role of this on clause is only reflected in outer join queries, and if the on clause is placed in an inner join, MySQL will treat it the same as the where clause. The where clause and the on clause in an inner join are equivalent.

3.1. Connection syntax

In MySQL, the outer join can be subdivided into left (outer) join and right (outer) join depending on the driver table selected.

• The syntax for left (outer) joins

Template:

sql复制代码SELECT * FROM 表1 LEFT [OUTER] JOIN 表2 ON 过滤条件 [WHERE 过滤条件];
           

Among them, the word outer in parentheses can be omitted. FOR LEFT (OUTER) JOINS, WE CALL THE TABLE ON THE LEFT SIDE OF THE LEFT [OUTER] JOIN THE OUTER OR DRIVER TABLE, AND THE TABLE ON THE RIGHT SIDE THE INNER OR DRIVEN TABLE. (Table 1 is the external table or driving table, Table 2 is the inner table or driven table) usually put the filter conditions involving the scale into the on clause (not absolute, how to filter depends on the user's personal choice)

Note: For left (outer) joins and right (outer) joins, the filter condition in the on clause cannot be omitted

Solve the needs:

sql复制代码SELECT student.id number,sn,`name`,department,major,`subject`,score FROM student LEFT JOIN student_score on student.id=student_score.number;
           

• The syntax for right (outer) joins

The syntax of the right (outer) join is the same as the syntax of the left (outer) join, except that left is changed to right

sql复制代码SELECT * FROM 表1 RIGHT [OUTER] JOIN 表2 ON 过滤条件 [WHERE 过滤条件];
           

It's just that the driver table is the table to the right of the RIGHT [OUTER] JOIN, and the driven table is the table on the left

3.2. Syntax for internal joins

Again, the fundamental difference between inner join and outer link is that when the records of the driver table do not meet the filter conditions in the on clause, the inner join does not add the records of the driver table to the final result set; And the outer link will.

In fact, the simplest syntax for inner join is to directly put multiple tables that need to be joined directly after the from clause, in addition, MySQL also provides a variety of other syntax for internal join queries.

Template:

sql复制代码SELECT * FROM 表1 [INNER | CROSS] JOIN 表2 [ON 过滤条件] [WHERE 过滤条件];
           

The following injoins are written equivalently:

vbnet复制代码SELECT * FROM 表1,表2 [WHERE 过滤条件];
SELECT * FROM 表1 JOIN 表2 [ON 过滤条件] [WHERE 过滤条件];
SELECT * FROM 表1 INNER JOIN 表2 [ON 过滤条件] [WHERE 过滤条件];
SELECT * FROM 表1 CROSS JOIN 表2 [ON 过滤条件] [WHERE 过滤条件];
           

In these writings, it is recommended to write inner joins in the form of inner joins, which is exactly the difference between left joins and right joins in outer joins. However, since the on clause and the where clause are equivalent in the inner join, it is not required to force the on clause in the inner join.

You can try all the connection syntax according to your mastery.

4. Multi-table join

You can join as many tables as you want. After creating a T3 table, do a demonstration

sql复制代码CREATE TABLE t3(
	a3 int,
	b3 char(1)
);

INSERT INTO t3 VALUES(3,'c'),(4,'d'),(5,'e');
           

Example:

sql复制代码SELECT * FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.a1=t2.a2 AND t1.a1=t3.a3;
           

Note that the table positions of inner joins are interchangeable and do not affect the results, but outer joins do not. The driver table and the driven table in the inner join are interchangeable, and the driver table and the driven table in the outer join are not easily interchangeable.

5. The alias of the table

The aliases of tables are the same as the aliases of columns, separated by whitespace characters or as. In cases where the table name is particularly long, defining an alias for the table can make the statement clearer. Aliases for tables can be used on clauses such as order by, group by, and so on.

Example:

sql复制代码SELECT s1.id number,sn,`name`,department,major,`subject`,score FROM student s1 LEFT JOIN student_score s2 on s1.id=s2.number;
           

6. Self-connection

Not only can you join multiple different tables, but the same table can also be joined, but one point to note is that when you operate the same table join, you must alias the table, otherwise an error will be reported. Example:

Since the joined table actually originates from the same table, this join is called a self-join. Self-joins can also be used for outer joins or inner joins, followed by filters. It will not be shown here, if you are interested, you can try to write it yourself and discuss it in the comment area.

7. Conversion of connection query and subquery

Some query requirements can be solved by using either join queries or subqueries. All equivalent. In actual development, please write according to your own habits.

Example:

sql复制代码/**连接查询**/
SELECT s2.* FROM student s1 INNER JOIN student_score s2 WHERE s1.id=s2.number and s1.major='计算机科学与技术';
/**子查询**/
SELECT * FROM student_score WHERE number in(SELECT id number FROM student WHERE major='计算机科学与技术');
           

The MySQL server is internally converting subqueries to join queries for processing, or in other ways.

MySQL Basics (6) ----- subqueries and join queries. Just share it here, and the MySQL basics (7) will be updated later----- union query and data insertion, deletion, and update.

This is the end of today's sharing, if you think it is helpful to you, trouble to give a trip!