1, Meaning of subquery

• Meaning: select statements that appear in other statements are called subqueries or intra queries; The external query statement is called primary query or external query

• Classification:

According to the location of sub query:
After select: only scalar subqueries are supported
After from: only table subqueries are supported
After where or having: scalar subquery, column subquery and row subquery are supported
After exists: table subquery

According to the number of rows and columns in the result set:
Scalar subquery: the result set has only one row and one column (also known as single row subquery)
Column subquery: the result set has only one column and multiple rows
Row subquery: the result set has only one row and multiple columns
Table sub query: the result set is generally multi row and multi column

2, Subquery after where or having

1.Scalar subquery 2.Column subquery 3.Row subquery

1. Features:

① Subqueries are enclosed in parentheses
② Subqueries are generally placed on the right side of conditions
③ In scalar subquery, it is generally used with single line operator: < > < = > = < >=
In column subquery, it is generally used with multi row operators: in, any / home, all
④ The operation of sub query should take precedence over the main query, because the main query uses the query results of sub query

Case 1 Whose salary is higher than Abel?

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

Case 2 Return job_ The ID is the same as that of employee 141. The name and job of employees with more salary than employee 143_ ID, salary

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
);

Case 3 Returns the last of the lowest paid employee in the company_ name job_ id salary

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

2.HAVING + scalar subquery

Case 4 Query the Department id and minimum wage of the Department whose minimum wage is greater than the minimum wage of No. 50 department

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

3. Column sub query (multi row sub query)

Case 5: return location_ Names of all employees in a department whose ID is 1400 or 1700

SELECT last_name FROM employees e 
INNER JOIN departments d 
ON e.`department_id` = d.`department_id`
WHERE d.`location_id` IN (1400,1700);

or

SELECT last_name FROM employees
WHERE department_id IN (
	SELECT department_id FROM departments
	WHERE location_id IN (1400,1700)
);

Case 6: return to jobs in other types of work_ ID is' it '_ 'prog' employee number, name and job of any employee with low salary_ ID and salary

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

4. Row sub query (the result set is one row with multiple columns or multiple rows with multiple columns)

Case 7: query the information of the employee with the smallest employee number and the highest salary

SELECT * FROM employees
WHERE salary = (
	SELECT MAX(salary) FROM employees
)AND employee_id = (
	SELECT MIN(employee_id) FROM employees
);

SELECT * FROM employees
--Note: the operators should be the same at this time, such as, employee_id = ? salary = ?
WHERE (employee_id,salary) =
	SELECT MIN(employee_id), MAX(salary) FROM employees #Row subquery, the output should be row
);

3, Subqueries placed after SELECT (only scalar subqueries are supported)

Case 8 query the number of employees in each department( ♥)

SELECT d.*,(
	SELECT COUNT(*) FROM employees e
	WHERE d.`department_id` = e.`department_id`
) AS Number of employees
FROM departments d;

Case 9 query the Department name with employee No. 102

SELECT department_name
FROM departments
WHERE department_id = (
	SELECT department_id FROM employees WHERE employee_id = 102
);

The scalar subquery after where can be used (as above), but if it must be placed after SELECT, it can be as follows:

SELECT (
	SELECT department_name FROM departments d
	INNER JOIN employees e 
	ON d.department_id = e.department_id
	WHERE e.employee_id = 102
) AS Department name;

4, The subquery is placed after From

• The essence is to treat the result set as a table, and then name the table, and then query it

Case 10: query the salary grade of the average salary of each department

SELECT AVG(salary)avg_sal,department_id
FROM employees
GROUP BY department_id;

SELECT avg_tab.avg_sal, department_id, jg.grade_level
FROM (
	SELECT AVG(salary) avg_sal,department_id
	FROM employees e
	GROUP BY department_id
) AS avg_tab   ##Be sure to alias the new form!!!!
INNER JOIN job_grades jg
ON avg_tab.avg_sal BETWEEN jg.`lowest_sal` AND jg.`highest_sal`;

5, Subquery after exists

Syntax:
exists (complete query statement)
result:
0 or 1
be careful:
Generally, the query using exists can be replaced by other query methods, so exists is rarely used. The following example is given

Case 11 query the Department name with employees

IN method

SELECT department_name FROM departments d
WHERE  d.`department_id` IN (
	SELECT department_id FROM employees
);

exists method

SELECT department_name FROM departments d
WHERE EXISTS (
	SELECT *
	FROM employees e
	WHERE e.`department_id` = d.`department_id`
);

Case 12 query the information of boys without girlfriends

EXISTS method

SELECT * FROM boys b
WHERE NOT EXISTS (
	SELECT *   FROM beauty g
	WHERE g.boyfriend_id = b.`id` 
);

NOT IN method

SELECT bo.* FROM boys bo
WHERE bo.`id` NOT IN (
	SELECT boyfriend_id FROM beauty
);