How to analyze nested subquery problems in Mysql

Mysql Nested Subquery

Subquery refers to a writing form that nests several small queries with different functions in a complete query statement to complete complex queries together. In order to Let readers know more about the concept of subquery.

Subquery return results
The data types that subqueries can return are divided into four types:

• Single row and single column: Returns the content of a specific column, which can be understood as a single-valued data;

• Single row and multiple columns: Returns the content of multiple columns in one row of data;

• Multiple rows and single column: Returns the contents of the same column in multiple rows of records, which is equivalent to giving an operating range;

• Multiple rows and multiple columns: Query The returned result is a temporary table;

Use subqueries in the WHERE clause

Process single row and single column subqueries in the WHERE clause, Multi-row, single-column subquery, single-row, multi-column subquery.

Single row and single column subquery

**Example 1, **Query the complete information of the lowest paid employee in the company

--查询公司之中工资最低的雇员的完整信息
SELECT * 
FROM emp e
WHERE e.sal=(
  SELECT MIN(sal) 
  FROM emp);

**Example 2,* *Query the information of all employees whose basic salary is lower than ALLEN

-- 查询出基本工资比ALLEN低的全部雇员信息
SELECT * 
FROM emp e
WHERE e.sal<(
 SELECT sal 
 FROM emp 
 WHERE ename=&#39;ALLEN&#39;
);

**Example 3. **Query the information of all employees whose basic salary is higher than the company's average salary

--查询基本工资高于公司平均薪金的全部雇员信息
SELECT *
FROM emp e
WHERE e.sal>(
SELECT AVG(sal)
FROM emp);

Single row and multi-column subquery.

**Example 4, **Find the information of all employees who have the same job as ALLEN and whose basic salary is higher than employee number 7521,

--查找出与ALLEN从事同一工作，并且基本工资高于雇员编号为7521的全部雇员信息,
SELECT *
FROM emp e
WHERE e.job=(
  SELECT job
  FROM emp 
  WHERE ename='ALLEN') 
  AND e.sal>(
  SELECT sal
  FROM emp 
  WHERE empno=7521);

**Example 5, **Query Information about employees who are engaged in the same job and have the same salary as SCOTT

SELECT *
FROM emp e
WHERE (e.job,e.sal) = (
  SELECT job,sal
  FROM emp 
  WHERE ename='SCOTT')
  AND ename<>&#39;SCOTT&#39;;

**Example 6, **Query the information of all employees who are engaged in the same job and have the same leadership as employee 7566

  --查询与雇员7566从事同一工作且领导相同的全部雇员信息
SELECT *
FROM emp e
WHERE (e.job,e.mgr) =(
        SELECT job,mgr
        FROM emp
      WHERE empno=7566
);

**Example 7 , **Query the information of all employees (including ALLEN) who work the same job as ALLEN and were hired in the same year (including ALLEN)

--查询与ALLEN从事同一工作且在同一年雇佣的全部雇员信息（包括ALLEN）
SELECT *
FROM emp e
WHERE (e.job,to_char(e.hiredate,'yyyy'))=(
           SELECT job,to_char(hiredate,'YYYY')
            FROM emp
            WHERE ename='ALLEN'
);

Multiple rows and single column subquery

Mainly use three operators: IN, ANY , ALL

IN operation

**Example 8, **Query all employee information with the same minimum salary in each department

--查询出与每个部门中最低工资相同的全部雇员信息
SELECT *
FROM emp e
WHERE e.sal IN(
     SELECT MIN(sal)
    FROM emp
    GROUP BY deptno
);

**Example 9, **Query all employee information that is not the same as the minimum wage in each department

--查询出不与每个部门中最低工资相同的全部雇员信息
SELECT *
FROM emp e
WHERE e.sal NOT IN(
     SELECT MIN(sal)
    FROM emp
    GROUP BY deptno
);

FROM subquery:

The data source that follows the FROM clause Not only a data table, but also a query result. This is a FROM subquery
Generally the return type of a FROM subquery is a multi-row, multi-column result setjavascript

Query the job title with an average salary higher than 2,000 and the average salary of the positionjava

第一种方式：能够使用HAVING字句实现

SELECT job,AVG(sal)
FROM emp
GROUP BY job
HAVING AVG(sal)>2000;

第二种方式：使用FROM子查询

SELECT job,avgsal
FROM (
  SELECT job,AVG(sal) AS avgsal
    FROM emp
      GROUP BY job) temp
WHERE temp.avgsal>2000;

Query the information of employees with commissions and whose salary is higher than the commission: web

SELECT e1.empno,e1.ename,e1.comm,e2.sal
FROM emp e1,emp e2
WHERE e1.comm IS NOT NULL AND e1.comm<e2.sal
AND e1.empno=e2.empno;

Summary:

1. The data source of the FROM subquery is the result of another query. The result of this subquery is equivalent to a temporary data table

2. An alias must be defined for the subquery

3. If you want to use the fields in the subquery, you should use the "alias.field name" method to reference

select subquery

Select the following subquery to use , can only be scalar query

#Case: Query the number of employees in each department

SELECT d.*,(
    SELECT COUNT(*)
    FROM employees
    WHERE employees.`department_id`=d.department_id
)

FROM
    departments d;

#Case: Query the department name with employee number = 102

SELECT department_name
FROM
employees e
INNER JOIN
    departments d
ON
    e.`department_id`=d.`department_id`
WHERE
    e.`employee_id`=102;

Use subquery

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

Use subquery

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

The above is the detailed content of How to analyze nested subquery problems in Mysql. For more information, please follow other related articles on the PHP Chinese website!