Mysql nested subquery

Subquery refers to a form of writing in which several small queries with different functions are nested in a complete query statement to complete complex queries together. In order to make readers more aware of the concept of subquery.

Results returned by subquery
There are four types of data that can be returned by sub queries:

1. Single row and single column: returns the contents of a specific column, which can be understood as a single value data;
2. Single row and multiple columns: returns the contents of multiple columns in a row of data;
3. Multiple rows and single column: returns the contents of the same column in multiple rows of records, which is equivalent to giving an operation range;
4. Multi row and multi column: the result returned by the query is a temporary table;

Use subqueries in the WHERE clause

Handle single row single column subquery, multi row single column subquery and single row multi column subquery in the WHERE clause.

Single row single column subquery

**Example 1. * * query the complete information of the lowest paid employees in the company

--Query the complete information of the lowest paid employees 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

-- Find the basic salary ratio ALLEN Low total employee information
SELECT * 
FROM emp e
WHERE e.sal<(
 SELECT sal 
 FROM emp 
 WHERE ename='ALLEN'
);

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

--Query the information of all employees whose basic salary is higher than the average salary of the company
SELECT *
FROM emp e
WHERE e.sal>(
SELECT AVG(sal)
FROM emp);

Single row multi column subquery.

**Example 4. * * find out the information of all employees who are engaged in the same work as ALLEN and whose basic salary is higher than employee number 7521,

--Find and ALLEN Information of all employees who are engaged in the same work and whose basic salary is higher than that of employee No. 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 the information of employees who are engaged in the same work 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<>'SCOTT';

**Example 6. * * query the information of all employees who are engaged in the same work and have the same leadership as employee 7566

  --Query the information of all employees who are engaged in the same work and have the same leadership as the employee 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 engaged in the same work as ALLEN and employed in the same year (including ALLEN)

--Query and ALLEN Information of all employees engaged in the same work and employed in the same year (including ALLEN)
SELECT *
FROM emp e
WHERE (e.job,to_char(e.hiredate,'yyyy'))=(
           SELECT job,to_char(hiredate,'YYYY')
            FROM emp
            WHERE ename='ALLEN'
);

Multi row single column subquery

Three operators are mainly used: IN, ANY and ALL

IN operation

**Example 8. * * query all employee information with the same minimum wage in each department

--Query all employee information with the same minimum wage 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

--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 followed by the FROM sentence is not only the data table, but also a query result, which is the FROM sub query
Usually, the result set javascript with multiple rows and columns of the return type of the FROM sub query

Query the name of the position whose average salary is higher than 2000 and the average salary of the position

The first way: can use HAVING Word sentence realization

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

The second way: use FROM Subquery

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

[external chain picture transferring... (IMG dnmiyjdq-1643208629399)]

Query the information of employees with Commission and whose salary is higher than 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;

[external chain picture transferring... (img-FHEChA0F-1643208629400)]

Summary:

1. The data source of the from sub query is the result of another query. The result of this sub query is related to a temporary data table

2. Alias must be defined for subquery

3. If you want to use the fields in the sub query, you should use the method of "alias. Field name" for reference

select subquery

The subqueries after select can only be scalar queries

#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 No. = 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
);