Detailed explanation of query examples within subqueries in MySql

#WHERE or HAVING followed by #1. Scalar subquery (single-row subquery)
#2. Column subquery (multi-row subquery)
#3. Row subquery (multiple columns and multiple rows)
#Features:
# ① Subquery is placed in parentheses # ② Subquery is usually placed on the right side of the condition # ③ Scalar subquery: usually used with single-row operators # Single-row operators: > < >= <= <> !-
# Column subquery, usually used with multi-row operators # IN, ANY/SOME (any), ALL
# ④ The execution of the subquery takes precedence over the main query, and the condition of the main query uses the result of the subquery.

#1. Scalar subquery#Case 1: Whose salary is higher than Abel?
#①Query Abel's salarySELECT salary
FROM employees
WHERE last_name = 'Abel'; 

#②Query the employee information, satisfying Salary>①ResultSELECT *
FROM employees
WHERE salary>(SELECT salary FROM employees WHERE last_name='Abel'); 

#Case 2. Return the name, job_id, and salary of the employee whose job_id is the same as employee No. 141 and whose salary is higher than employee No. 143.
#①Check the job_id of employee 141
SELECT job_id
FROM employees
WHERE employee_id='141'; 

#②Check the salary of employee 143
SELECT salary
FROM employees
WHERE employee_id='143'; 

#③Finally merge the results SELECT CONCAT(last_name,first_name) AS name,
job_id AS job number,
salary AS 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. Return the last_name, job_id and salary of the employee with the lowest salary in the company.
SELECT MIN(salary)
FROM employees; 

SELECT
last_name AS last name,
salary AS salary,
job_id AS job number FROM employees
WHERE salary=(
 SELECT MIN(salary)
 FROM employees
     ); 

#Case 4. Query the department ID and minimum wage of the department whose minimum wage is greater than the minimum wage of department No. 50.
#①Check the minimum salary of 50 departments SELECT MIN(salary)
FROM employees
WHERE department_id=50; 

#After grouping, filter condition ①. [Do not exclude those without departments, so do not filter the department number]
SELECT department_id AS department_id,
MIN(salary) AS monthly salary FROM employees
#WHERE department_id
GROUP BY department_id
HAVING MONTHLY SALARY>(
 SELECT MIN(salary)
 FROM employees
    ); 

#2. Column subquery (multi-row subquery)
#Returning multiple rows#Using multiple row comparison operators 

#Case 1. Return the names of all employees in the department whose location_id is 1400 or 1700.
#①Query the department number whose location_id is 1400 or 1700SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700); 

#②Query the employee name, requiring the department number to be one of the items in list ①SELECT CONCAT(last_name,first_name) AS name FROM employees
WHERE department_id IN (
 SELECT DISTINCT department_id
 FROM departments
 WHERE location_id IN (1400, 1700)
 ); 

Use ANY instead of IN to get the same result as above. SELECT CONCAT(last_name,first_name) AS name FROM employees
WHERE department_id = ANY(
 SELECT DISTINCT department_id
 FROM departments
 WHERE location_id IN (1400, 1700)
 );

# Example. Return the names of all employees in departments whose location_id is not 1400 or 1700.
SELECT CONCAT(last_name,first_name) AS name FROM employees
WHERE department_id NOT IN (
 SELECT DISTINCT department_id
 FROM departments
 WHERE location_id IN (1400, 1700)
);
==============================
SELECT CONCAT(last_name,first_name) AS name FROM employees
WHERE department_id <> ALL(
 SELECT DISTINCT department_id
 FROM departments
 WHERE location_id IN (1400, 1700)
); 

#Case 2. Return the employee ID of any employee in other jobs with a lower salary than any employee in the IT_PROG department with job_id,
# Name, job_id and salary
#①Query the salary of IT_PROG departmentSELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'; 

#②Find out the information of departments other than IT_PROGSELECT *
FROM employees
WHERE job_id != 'IT_PROG'; 

#③Merge ① and ② to find out in the employee table SELECT employee_id AS employee number,
CONCAT(last_name,first_name) AS name,
job_id AS job number,
salary AS salary FROM employees
WHERE job_id != 'IT_PROG'
AND salary<ANY(
 SELECT salary
 FROM employees
 WHERE job_id='IT_PROG'
  ); 

Using MAX instead of ANY has the same effect as above SELECT employee_id AS employee number,
CONCAT(last_name,first_name) AS name,
job_id AS job number,
salary AS salary FROM employees
WHERE job_id <> 'IT_PROG'
AND salary<(
 SELECT MAX(salary)
 FROM employees
 WHERE job_id='IT_PROG'
  );

#Case 3. Return the employee number, name, job_id and salary of all employees in other departments whose salary is lower than that of the department with job_id 'IT_PROG'.
#①First find out the salary of IT_PROG department.
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'; 

SELECT employee_id AS employee_id,
CONCAT(last_name,first_name) AS name,
job_id AS job number,
salary AS salary FROM employees
WHERE salary<ALL(
 SELECT DISTINCT salary
 FROM employees
 WHERE job_id='IT_PROG'
)
 AND job_id <> 'IT_PROG';
=============================
MIN instead of ALL
SELECT employee_id AS employee_id,
CONCAT(last_name,first_name) AS name,
job_id AS job number,
salary AS salary FROM employees
WHERE salary<(
  SELECT MIN(salary)
  FROM employees
  WHERE job_id='IT_PROG'
)
 AND job_id <> 'IT_PROG'; 

#3. Row subquery (result set has one row and multiple columns or multiple rows and multiple columns)
#Case 1. Query the employee with the smallest employee number and the highest salary. Introduce SELECT MIN(employee_id)
FROM employees;
=================
SELECT MAX(salary)
FROM employees; 

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

This query result uses virtual fields, and single-row operators must be consistent to be used. The results are the same as above.
SELECT *
FROM employees
WHERE (employee_id,salary)=(
 SELECT MIN(employee_id),
 MAX(salary)
 FROM employees
       );

#2. SELECT subquery#Only supports scalar subqueries, the result is one row and one column#Case 1. Query the number of employees in each departmentSELECT d.*,(SELECT COUNT(*) FROM employees)
FROM departments d; 

Add the condition SELECT d.*,(SELECT COUNT(*)
FROM employees
WHERE e.department_id=d.department_id
) AS numberFROM departments d; 

#Case 2. Query the department name of employee number = 102.
SELECT department_name
FROM departments;
==============
SELECT employee_id
FROM employees
WHERE employee_id = 102; 

SELECT employee_id,
(
 SELECT department_name
 FROM departments
 WHERE e.department_id=d.department_id
)
FROM employees
WHERE employee_id=102; 

#Three. Note after FROM: The subquery result is used as a table, and an alias must be given. #Case: Query the average salary level of each department.
SELECT ROUND(AVG(salary),2),department_id
FROM employees
GROUP BY department_id; 

SELECT e.averagesalary,j.grade_level
FROM job_grades AS j
,(
SELECT ROUND(AVG(salary),2) AS averagesalary,department_id
 FROM employees
 GROUP BY department_id
) AS e
WHERE e.average_sal BETWEEN j.lowest_sal AND j.highest_sal;

#1999 Grammar, teacher's answer SELECT e.*,j.grade_level
FROM (
  SELECT ROUND(AVG(salary),2) AS averagesalary,department_id
  FROM employees
  GROUP BY department_id
) AS e
INNER JOIN job_grades j
ON e.average_sal BETWEEN j.lowest_sal AND j.highest_sal; 

#Four. After EXISTS (correlated subquery)
Syntax: EXISTS (complete query statement)
Note: The complete query statement can be one row and one column, or one row and multiple columns. Note: First perform the outer query, and then filter according to the value of a certain field. EXISTS determines whether the value (Boolean type) exists or not. There are only two results: 1, 0, no. #Introduce SELECT EXISTS (SELECT employee_id FROM employees); 

Query the employee information with salary 3W SELECT EXISTS(SELECT * FROM employees WHERE salary=30000); 

#Case introduction. Query employee name and department name#Query employee name and department numberSELECT first_name,department_id
FROM employees
WHERE department_id; 

#Query department name SELECT department_name
FROM departments; 

#Check employee name and department name SELECT e.first_name,d.department_name
FROM employees
INNER JOIN ( SELECT department_name,department_id
 FROM departments
) AS d
ON e.department_id=d.department_id; 

#Case 1..Query the department name of the employees SELECT department_name
FROM departments
WHERE EXISTS(
 SELECT *
 FROM employees
 WHERE d.department_id=e.department_id
    ); 

Use IN instead of EXISTS, the same result as above SELECT department_name
FROM departments
WHERE d.department_id IN (
 SELECT department_id
 FROM employees
     );

#Case 2. Query the information of the male god who has no girlfriend#IN methodSELECT *
FROM boys bo
WHERE bo.id NOT IN(
 SELECT boyfriend_id
 FROM beauty be
);
===============
#EXISTS method SELECT *
FROM boys bo
WHERE NOT EXISTS(
 SELECT boyfriend_id
 FROM beauty be
 WHERE bo.id=be.boyfriend_id
); 

Advanced 9: Union query UNION Union merge: merge the results of multiple query statements into one result.
grammar:
 Query statement 1
 UNION
 Query 2
 UNION
 ...

Application scenarios:
The results to be queried come from multiple tables, and there is no direct connection between the multiple tables.
But when the query information is consistent.
Web page search content, content is retrieved from different tables and combined and returned to the user.

Features:
1. The number of query columns in multiple query statements must be consistent.
2. The type and order of each column in multiple query statements should be consistent.
3. Use the UNION keyword to remove duplicates by default. If you use UNION ALL, all items will be displayed, including duplicates.