SQL nested subqueries inside and outside layers

Each row of the outer query is scanned line by row, and each row is compared to the internally queried table, which satisfies the return true of the where condition of the internal query and does not satisfy the return false.

Reminder: An error occurs when an internal query is executed separately.

Perform an external query first

Each row of the corresponding external query executes a subquery once, and the value of the current row in the outer query is referenced each time the subquery is executed.

Use the results of a subquery to determine the result set of an external query.

Cases:

Order information for the largest amount of orders handled by different employees

Select a.* from Order a WHERE amount = (select Max (amount) from order form where employee number =A. Employee number)

Employee information for the maximum wage in different warehouses

Select A.* from Employee a WHERE salary = (select Max (payroll) from worker where warehouse number = A. Warehouse number)

SQL nested subqueries inside and outside layers