Mysql inside the subquery instance _mysql

One, the child chooses the basic usage
1, the definition of the sub-selection
The sub-option allows one query to be nested within another query. For example, an examination score project divides the examination into two situations: examination (T) and test (Q). The following query will only find the students ' test scores.
SELECT * FROM score where event_id the (select event_id from event where type= ' T ');
2, the use of sub-selection (3 kinds)
Use sub selection to generate a reference value
In this case, the inner 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 student's test scores on a particular day, you should use an inner query to find the event number of the quiz for the day, and then in the outer query you'll find the student's score record in the score sheet with the event number. The specific statement is:
SELECT * FROM score where
Id= (select event_id from event where date= ' 2002-03-21 ' and type= ' Q ');
It is important to note that in the application of this inner query result is mainly used for comparison operations of the method, the inner query should have only one output results. For example, if you want to know which American president's birthday is minimal, 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 certain parameter to work! So we use the sub selection instead:
SELECT * from President where birht= (select min (birth) from presidnet);
exists and NOT EXISTS child selection
The previous use is to pass the results of the search from the inner layer to the outer, the use of this class is reversed, the outer query results are passed to the inner layer. To see whether the results of the external query to meet the internal search between the matching diameter pieces. This "out of" sub-selection is ideal for retrieving a datasheet that has a matching record in another datasheet.

Datasheet T1 data Table t2
I1 C1 I2 C2
1
2
3 A

C 2
3
4 C

A
Find the data that exists in all two tables first.
Select I1 from t1 where exists (select * from T2 where t1.i1=t2.i2);
And then find T1 in the table, T2 data that doesn't exist in the table.
Select I1 form T1 where NOT EXISTS (SELECT * from T2 where t1.i1=t2.i2);

Note: In these two forms of sub selection, the asterisk in the inner query represents the output of the outer query. There is no need for an inner query to list the names of the data columns, and the field is the inner query concerned about how many rows the results of the outer query are. I hope you can understand that.
in and not in child selection
Within this seed selection, the inner query statement should simply return a data column in which the value in the data column is evaluated by the comparison operation in the outer query statement. or the above case
Find the data that exists in all two tables first.
Select I1 from T1 where I1 in (select I2 from T2);
And then find T1 in the table, T2 data that doesn't exist in the table.
Select I1 form t1 where I1 not in (select I2 from T2);
As if this statement is easier to understand, let's take another example.
For example, you want to find all the students who live in A and B.
SELECT * FROM student where State in (' A ', ' B ')
Second, the handle. Select the query to overwrite the method of the associated query.
1, the rewrite of matching sub-select query
The following example from the Score data table to the students in the exam events (t) results (not including test scores!) ) query out.
SELECT * FROM score where event_id the (select event_id from event where type= ' T ');
Visible, the internal query to find out all the exam events, the outer query to use these test events to get students ' results.
This subquery can be rewritten as a simple association query:
Select score.* from Score, event where score.event_id=event.event_id and event.event_id= ' T ';
The following example can be used to identify the results of all female students.
SELECT * FROM score where student_id in (select student_id form student where sex = ' f ');
You can convert it to an associated query as follows:
Select * FROM Score
Where student _id =student.student_id and student.sex = ' f ';
It is a rule to rewrite the matching subquery as an associated query. The following form of the subquery query:
Select * from Tablel
where Column1 in (select column2a from table2 where column2b = value);
You can convert to an associated query that looks like this:
Select Tablel. * FROM Tablel,table2
Where table.column1 = table2.column2a and table2.column2b = value;
(2) The rewriting of mismatched (i.e. missing) sub-selection queries
Another common use for child-select queries is to find things that are in one datasheet but not in another. As I saw earlier, this "in one data table, in another data table" is often implied that a LEFT join can be used to solve the problem. Consider the following subquery, which can be traced to students who do not appear in the absence data table (i.e., those who have never been absent):
Select * FROM Student
Where student_id not in (select student_id from absence);
This subquery can overwrite 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;
It is a rule to rewrite the mismatched sub select query as the associated query. The following form of the subquery query:
Select * from Tablel
Where Column1 Not in (select Column2 from table2);
You can convert to an associated query that looks like this:
Select Tablel. *
From Tablel left join table2 on Tablel.column1=table2.column2
Where table2.column2 is null;
Note: This overwrite requires that the data column table2.column2 be declared NOT NULL.