MySQL: Where sub-query & From sub-query & Exists sub-query

1. A good comprehension model:

    1) Where expression: Fit this experssion into every row and get the true/false result.

#Where expression comprehension model
select * from stu where stu_name = 'zhangsan';

    2) Row: Regard as variable which enables basic math operation.

#Row comprehension model
#We can regard the second 'stu_name' as a variable whose value depends on the row cursor now pointing to
select stu_name from stu where stu_name = 'zhangsan'

     3) Result set: Regard as a temporary table which enables further query.

#We regard the sub-query result set as a temp table which enables further query
#We want to find the stu_name whose max score equals or larger than 80
select * from (select stu_name, max(stu_score) as stu_max from stu group by stu_name) as temp where stu_max >= 80;

 

2. Four Types of Sub-query

    1) Where sub-query

# Find the highest score and the corresponding stu_name, stu_course

# Solution 1: Where sub-query
# Regard the result of where sub-query as a value
select stu_name, stu_score, stu_course from stu where stu_score = (select max(stu_score) from stu);

# Solution 2: Order & Limit
select stu_name, stu_score, stu_course from stu group by stu_score limit 1;

 

#Find the highest score and the corresponding student in each course

#Using where sub-query
#But this kind of approach is not that precise.
#Just use the stu_score as the signal, there may be some other stu_score in other courses that equals the max(score).
select * from stu where stu_score in (select max(stu_score) from stu group by stu_course);
#There is an overlap in Literature course because the stu_score in Literature 50 equals the highest score that in Geograph 50
+----------+------------+-----------+
| stu_name | stu_course | stu_score |
+----------+------------+-----------+
| zhangsan | Math       |        90 |
| zhangsan | Literature |        50 |
| lisi     | Literature |        55 |
| zhaoliu  | Geograph   |        50 |
| zhaoliu  | Politic    |        99 |
+----------+------------+-----------+

 

     2) In sub-query

#The core thought lies behind From sub-query is regarding the result of inner query as a table based on which we can start a new outter query.

select * from (select max(stu_score) from stu group by stu_course) as temp;

 

#Find the avg score for students who has more than one invalid course.

#Using Where sub-query
#Most inner query find the stu_name who has more than one invalid course.
#Secondary inner query filter other useless columns for where sub-query
#Outter query find the average score for each students whose name lists in inner query.
select stu_name, avg(stu_score) from stu where stu_name in (select stu_name from (select stu_name, stu_score, sum(stu_score < 60) as invalid_count from stu group by stu_name having invalid_count >= 2) as temp) group by stu_name;
#Another way to find out the student name whose invalid course in more than one
select stu_name, count(*) as invalid_count from stu where stu_score < 60 group by stu_name having invalid_count >= 2;

 

    3) Exists sub-query

#Core thoughts lies on Exists sub-query is that 'Put the result of out query into inner query to judge the inner query true or false'

select * from category;

#First Level Category
####Secondary Category
#######Real Goods
#######Real Goods
####Secondary Category
#######Third Level Category
###########Real Goods
####Secondary Category

# All the categories are stored in category table,
# But some categories contains no goods but secondary category

# What if we want to figure out the category that directly contains goods?

select cat_id, cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);