MYSQL authoritative guide reading notes _ MySQL

MYSQL authoritative guide reading notes MySQL sub-selection
I. basic usage of sub-selection
1. definition of sub-selection
Child iterations allow one query to be nested in another query. For example, an exam score item divides exam events into two types: Exam (T) and quiz (Q. The following query only shows the students' test scores.
Select * from score where event_id in (select event_id from event where type ='t ');
2. sub-selection usage (3)
? Generate a reference value using sub-selection
In this case, an internal query statement is used to retrieve a data value, and then the data value is used in the comparison operation of the outer query statement. For example, if you want to query the test scores of the students in the table on a certain day, you should use an inner-layer query to first find the quiz event number for this day, then, use the event number in the outer query statement to find the score records of the students in the score table. The specific statement is:
Select * from score where
Id = (select event_id from event where date = '2017-03-21 'and type = 'Q ');
Note: when applying the results of such an inner-layer query for comparison, only one output result is required for the inner-layer query. Let's look at the example. if you want to know which American president has the lowest birthday, construct the following query:
Select * from president where birth = min (birth)
This query is wrong! Because MySQL does not allow the use of statistical functions in clauses! The min () function should have a definite parameter to work! So we use the sub-choice:
Select * from president where birht = (select min (birth) from presidnet );
? Exists and not exists subselection
The previous method is to pass the query result from the inner layer to the outer layer. in this class, the results of the outer query are passed to the inner layer. Check whether the external query results meet the matching path of the internal query. This "from external to inner" sub-selection method is very suitable for retrieving a data table with matching records in another data table.

Data table t1 data table t2
I1 C1 I2 C2
1
2
3
B
C 2
3
4 C
B
A
First, find the data in both tables.
Select i1 from t1 where exists (select * from t2 where t1.i1 = t2.i2 );
Find the data that exists in table t1 and does not exist in table t2.
Select i1 form t1 where not exists (select * from t2 where t1.i1 = t2.i2 );

Note: In the two forms of sub-selection, the asterisks in the inner query represent the output results of the outer query. There is no need for an inner-layer query to list the names of data columns. the inner-layer query is concerned with the number of rows in the results of the outer-layer query. I hope you can understand this.
? In and not in subselection
In this seed selection, the inner-layer query statement should return only one data column, and the value in this data column will be evaluated by the comparison operation in the outer query statement. Or the above example
First, find the data in both tables.
Select i1 from t1 where i1 in (select i2 from t2 );
Find the data that exists in table t1 and does not exist in table t2.
Select i1 form t1 where i1 not in (select i2 from t2 );
It seems that such a statement is easier to understand.
For example, you want to find all the students living in A and B.
Select * from student where state in ('A', 'B ')
2. rewrite the subquery to the associated query method.
1. rewrite the matching child selection query
In the following example, the score table shows the score of the students in the test event (T) (excluding the test score !) Query.
Select * from score where event_id in (select event_id from event where type ='t ');
It can be seen that the inner layer queries all test events, and the outer layer queries use these test events to get students' scores.
This subquery can be rewritten as a simple join query:
Select score. * from score, event where score. event_id = event. event_id and event. event_id ='t ';
The following example shows the scores of all female students.
Select * from score where student_id in (select student_id form student where sex = 'F ');
You can convert it into an associated query as follows:
Select * from score
Where student _ id = student. student_id and student. sex = 'F ';
There is a rule to rewrite the matching subquery to an associated query. The subselection query in this form is as follows:
Select * from tablel
Where column1 in (select column2a from table2 where column2b = value );
It can be converted into an associated query as follows:
Select tablel. * from tablel, table2
Where table. column1 = table2.column2a and table2.column2b = value;
(2) rewrite of non-matching (I .e. missing) subselection query
Another common use of sub-selection queries is to find things that exist in a data table but do not exist in another data table. As we can see earlier, this "in a data table, not in another data table" statement usually implies that a left join can be used to solve this problem. Please refer to the subquery below, which can be used to check out the students (that is, those who have never been absent) that do not appear in the absence data table:
Select * from student
Where student_id not in (select student_id from absence );
You can rewrite the left join query as follows:
Select student .*
From student left join absence on student. student_id = absence. student_id
Where absence. student_id is null;
There is a rule to rewrite a non-matching subquery to an associated query. The subselection query in this form is as follows:
Select * from tablel
Where column1 not in (select column2 from table2 );
It can be converted into an associated query as follows:
Select tablel .*
From tablel left join table2 on tablel. column1 = table2.column2
Where table2.column2 is null;
Note: This rewrite requires that the data column table2.column2 be declared as not null.