MySQL (ix) DQL's child query

First, meaning

A SELECT statement nested inside another statement is called a subquery or an inner query, and the outer statement can be INSERT, UPDATE, delete, select, and so on, the general select as the outer statement is more, outside if is a SELECT statement, then this statement is called the outer query or the main query.

II. Classification 2.1, by the occurrence of position

Select back: only support scalar quantum query

From Back: Table subquery

Where or having behind: scalar subquery, sub query, row subquery

exists back: Scalar query, sub-query, row subquery, table sub-query

2.2, by the row of the result set

Scalar Quantum Query (single-line subquery): Result set is one row column

Column query (multiline subquery): Result set is more than one row

Row subquery: Result set is multi-row multi-column

Table subquery: Result set is multiple rows and columns

Iii. Example 3.1, where or having behind

Case 1: Whose salary is higher than Abel

SELECT * FROM Employees where salary> (select salary from Employees where last_name = ' Abel ');

Case 2: Find the Department ID and minimum wage for the minimum wage of the department with the lowest wages greater than 50th

Select min (Salary), department_id from Employees GROUP by DEPARTMENT_ID have MIN (Salary) > (SELECT min (salary) from EM Ployees WHERE department_id = 50);

3.2. Select Back

Case: Query the number of employees in each department

Select d.*, (select COUNT (*) from Employees e WHERE e.department_id = d. ' department_id ') the number from Departments D;

3.3. From behind

to act as a table for the subquery result, requiring an alias

Case: Check the salary level for each department's average salary

Select Ag_dep.*,g. ' Grade_level ' from (select AVG (Salary) ag,department_id from Employees GROUP by department_id) AG_DEP INNER JOIN job_grades G on ag_dep.ag between Lowest_sal and Highest_sal;

3.4, exists back

Syntax: Exists (full query statement)

Results: 1 or 0

Case 1: Query the department name with the employee

Select Department_name from Departments D where EXISTS (SELECT * FROM Employees e where d. ' department_id ' =e. ' department_id `);

Equivalent

Select Department_name from Departments D WHERE D. ' department_id ' in (SELECT department_id from Employees)

Follow the public number: Java Back-end life, dry article first time delivery!

MySQL (ix) DQL's child query