Subquery instances in MySQL
- 2020-05-06 11:50:04
- OfStack
First, the child selects the basic usage
1. Definition of child selection
Subaltern allows one query to be nested within another. For example, a test scoring project divides test events into test (T) and test (Q) situations. The following query will only find the students' test scores
select * from score where event_id in (select event_id from event where type='T');
2. Usage of sub-selected (3 kinds)
The & # 61548; generates a reference value
with a subselection In this case, the inner query is used to retrieve a data value, which is then used in the comparison operation of the outer query. For example, if you want to query the test scores of high school students on a given day, you should use an inner query to find the event number of the test for that day, and then use the event number in the outer query to find the score record of the students in the score table. 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 when the results of this inner query are primarily a division for comparison operations, the inner query should have only one output. For example, if you want to know which U.S. President has the smallest birthday, construct the following query
select * from president where birth=min(birth)
This query is wrong! Because MySQL does not allow statistics in clauses! The min() function should have a certain parameter to work! So let's switch to a subselection:
select * from president where birht=(select min(birth) from presidnet);
The & # 61548; exists and not exists subselect
The previous usage is to pass the lookup result from the inner layer to the outer layer. This class USES the opposite principle and passes the result of the outer query to the inner layer. See whether the result of the external query satisfies the matching size of the internal query. This "outside-in" subexample is ideal for retrieving a data table with a matching record
in another data table
Data sheet t1 data sheet t2
I1 C1 I2 C2
1
2
3 A
C 2
3
4 C
A
Find the data
that exists in both tables select i1 from t1 where exists(select * from t2 where t1.i1=t2.i2);
Then find the data
which exists in t1 and t2 which does not exist in
select i1 form t1 where not exists(select * from t2 where t1.i1=t2.i2);
Note that in both forms of subselection, the asterisk in the inner query represents the output of the outer query. It is not necessary for an inner query to list the names of the relevant data columns; what matters for an inner query is how many rows the result of the outer query has. I hope you understand that The & # 61548; in and not in children choose
In this subselection, the inner query should return only one data column whose values are evaluated by the comparison operation in the outer query. Again, example
Find the data
that exists in both tables select i1 from t1 where i1 in (select i2 from t2);
Then find the data
which exists in t1 and t2 which does not exist in
select i1 form t1 where i1 not in (select i2 from t2);
It seems that this statement is easier to understand. Here is another example,
Let's say you want to find all the students who live in A and B.
select * student state in(' A','B')
1, match type subselect the rewrite
of the query The following example shows the students' scores (not including test scores!) in the score data sheet. Look it up.
Select * from score where event_id in (select event_id from event where type='T');
As you can see, the inner query finds all the exam events, and the outer query USES these exam events to get students' grades.
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 find the grades of all female students.
Select * score
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
sex = 'f');
You can convert it to an association query like this:
Select * from score
Where student _id =student.student_id and student.sex ='f';
There is a rule to rewrite the matching subselection query as an association query. The following form of a subselect query:
Select * from tablel
Where column1 in (select column2a from table2 where column2b = value);
This can be converted to an association query as shown below:
Select tablel. * from tablel,table2
Where table.column1 = table2.column2a and table2.column2b = value;
(2) rewrite
of the unmatched (that is, missing) type subselect query Another common use of subselect queries is to find things that are in one table but not in another. As you've seen before, this "in one table, not in another table" statement usually implies an left join solution to the problem. See the following subselect query for students who are not in the absence table (that is, those who have never been absent) :
Select * from student
Where student_id not in (select student_id from absence);
This subselect query can be overwritten as left join query:
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 a mismatched subselect query as an associated query. The following form of a subselect query:
Select * from tablel
Where column1 not in (select column2 from table2);
This can be converted to an association query as shown below:
Select tablel . *
From tablel left join table2 on tablel.column1=table2.column2
Where table2.column2 is null;
Note: this rewrite requires the data column table2.column2 is declared as not null.
1. Definition of child selection
Subaltern allows one query to be nested within another. For example, a test scoring project divides test events into test (T) and test (Q) situations. The following query will only find the students' test scores
select * from score where event_id in (select event_id from event where type='T');
2. Usage of sub-selected (3 kinds)
The & # 61548; generates a reference value
with a subselection In this case, the inner query is used to retrieve a data value, which is then used in the comparison operation of the outer query. For example, if you want to query the test scores of high school students on a given day, you should use an inner query to find the event number of the test for that day, and then use the event number in the outer query to find the score record of the students in the score table. 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 when the results of this inner query are primarily a division for comparison operations, the inner query should have only one output. For example, if you want to know which U.S. President has the smallest birthday, construct the following query
select * from president where birth=min(birth)
This query is wrong! Because MySQL does not allow statistics in clauses! The min() function should have a certain parameter to work! So let's switch to a subselection:
select * from president where birht=(select min(birth) from presidnet);
The & # 61548; exists and not exists subselect
The previous usage is to pass the lookup result from the inner layer to the outer layer. This class USES the opposite principle and passes the result of the outer query to the inner layer. See whether the result of the external query satisfies the matching size of the internal query. This "outside-in" subexample is ideal for retrieving a data table with a matching record
in another data table
Data sheet t1 data sheet t2
I1 C1 I2 C2
1
2
3 A
C 2
3
4 C
A
Find the data
that exists in both tables select i1 from t1 where exists(select * from t2 where t1.i1=t2.i2);
Then find the data
which exists in t1 and t2 which does not exist in
select i1 form t1 where not exists(select * from t2 where t1.i1=t2.i2);
Note that in both forms of subselection, the asterisk in the inner query represents the output of the outer query. It is not necessary for an inner query to list the names of the relevant data columns; what matters for an inner query is how many rows the result of the outer query has. I hope you understand that The & # 61548; in and not in children choose
In this subselection, the inner query should return only one data column whose values are evaluated by the comparison operation in the outer query. Again, example
Find the data
that exists in both tables select i1 from t1 where i1 in (select i2 from t2);
Then find the data
which exists in t1 and t2 which does not exist in
select i1 form t1 where i1 not in (select i2 from t2);
It seems that this statement is easier to understand. Here is another example,
Let's say you want to find all the students who live in A and B.
select * student state in(' A','B')
1, match type subselect the rewrite
of the query The following example shows the students' scores (not including test scores!) in the score data sheet. Look it up.
Select * from score where event_id in (select event_id from event where type='T');
As you can see, the inner query finds all the exam events, and the outer query USES these exam events to get students' grades.
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 find the grades of all female students.
Select * score
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
sex = 'f');
You can convert it to an association query like this:
Select * from score
Where student _id =student.student_id and student.sex ='f';
There is a rule to rewrite the matching subselection query as an association query. The following form of a subselect query:
Select * from tablel
Where column1 in (select column2a from table2 where column2b = value);
This can be converted to an association query as shown below:
Select tablel. * from tablel,table2
Where table.column1 = table2.column2a and table2.column2b = value;
(2) rewrite
of the unmatched (that is, missing) type subselect query Another common use of subselect queries is to find things that are in one table but not in another. As you've seen before, this "in one table, not in another table" statement usually implies an left join solution to the problem. See the following subselect query for students who are not in the absence table (that is, those who have never been absent) :
Select * from student
Where student_id not in (select student_id from absence);
This subselect query can be overwritten as left join query:
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 a mismatched subselect query as an associated query. The following form of a subselect query:
Select * from tablel
Where column1 not in (select column2 from table2);
This can be converted to an association query as shown below:
Select tablel . *
From tablel left join table2 on tablel.column1=table2.column2
Where table2.column2 is null;
Note: this rewrite requires the data column table2.column2 is declared as not null.