Application and syntax rules of MySQL sub query:
Let's first introduce an example: compare whose salary is higher than Abel in the salary table
Method 1 (carried out in two steps), the reference code is as follows:

SELECT salary 
FROM employees
WHERE last_name = 'Abel';

SELECT last_name,salary
FROM employees
WHERE salary > 11000;

Method 2 (internal connection), the reference code is as follows:

SELECT e2.last_name,e2.salary
FROM employee e1,employee e2
WHERE e1.last_name = 'Abel'
AND e2.`salary`> e1.`salary`;

Method 3 (sub query), the reference code is as follows:

SELECT last_name,salary
FROM employees
WHERE salary > (
				SELECT salary 
				FROM employees
				WHERE last_name = 'Abel'
);

Through the above code, we can see that the so-called sub query is actually a nested query, and the result of the previous query is used as the filter condition of the next step.

1.

Overview of sub query:
The structure of the query declared outside: external query and primary query
The structure of the query declared inside: internal query and main query
Classification of sub query: single line sub query and multi line sub query

Steps of writing sub query: write from outside to inside and from inside to outside.
We generally suggest to write from the inside out, so that you can check every step of writing, so that you don't know which step is the problem in the end.

--------------------------------------------(split line)---------------------------------------------

2.

Single line sub query: internal query (primary query) returns only one result
We use an example to illustrate the single line sub query
Example 1: return job_ The ID is the same as that of employee 141. Salary has more employee names and jobs than employee 143_ ID and salary
Reference codes are as follows:

SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
				SELECT job_id
				FROM employees
				WHERE employee_id = 141)
AND salary > (
				SELECT salary
				FROM employees
				WHERE employee_id = 143);

+++++++++++++++++++(split line)++++++++++++++++++++

Example 2: return the last of the employee with the lowest salary in the company_ name,job_ ID and salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
				SELECT MIN(salary)
				FROM employees
);

+++++++++++++++++++(split line)++++++++++++++++++++

Example 3: query the Department id and the minimum wage of the Department whose minimum wage is greater than the minimum wage of No. 50 department

SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
						SELECT MIN(salary)
						FROM employees
						HAVING department_id = 50
);

--------------------------------------------(split line)---------------------------------------------

3.

The problem of null value is encountered in the query (sometimes a null may be obtained internally)
Reference codes are as follows:

SELECT last_name, job_id
FROM employees
WHERE job_id =(SELECT job_id
		FROM employees
		WHERE last_name = 'Haas');

Because there is no 'Haas' here, the internal query result is empty

--------------------------------------------(split line)---------------------------------------------

4.

Multi line sub query: multiple results returned by internal query (main query)
The operators you can use are: in all any
in: take each value returned from the internal query (each value is used once) and find the union set
All: during comparison, check each value obtained from the sub query one by one, and the obtained result must meet all conditions (all)
Any: as long as any result of the subquery is satisfied (any one is OK)

SELECT employee_id,last_name
FROM employees
WHERE salary IN (
		SELECT MIN(salary)
		FROM employees
		GROUP BY department_id
); 

Note: in the above code, the sub query returns more than one result. Here, you can only use multiple lines of sub query

Next, let's give an example of multi row sub query
Example 1: return other jobs_ Job in ID_ ID is' it '_ Prog 'employee number, name and job of any employee with low salary in the Department_ ID and salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY (
					SELECT salary 
					FROM employees
					WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';

+++++++++++++++++++(split line)++++++++++++++++++++

Example 2: return other jobs_ Job in ID_ ID is' it '_ Prog 'employee number, name and job of all employees with low salary in the Department_ ID and salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL (
					SELECT salary 
					FROM employees
					WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';

The only difference between example 1 and example 2 above is that in the question stem, example 1 is "any" and example 2 is "ALL". So this is an example of the use scenario of ALL and ANG.