Examples of SQL nested subqueries:
Some novice programmers are often in the SQL syntax of subqueries, by their nested subqueries (including a subquery in the subquery) are unfamiliar with the use of this article to do a basic explanation, I believe the novice will have a certain harvest.
Principles for using subqueries
1. A subquery must be placed in parentheses.
2. Place the subquery on the right side of the comparison condition to increase readability.
The subquery does not contain an ORDER BY clause. You can only use an ORDER BY clause for a SELECT statement,
And if it is specified, it must be placed at the end of the main SELECT statement.
The ORDER BY clause can be used and is required for top-n analysis.
3. There are two comparison criteria that can be used in subqueries: Single-line operators and multi-line operators.
Type of subquery
Single-line subqueries: Only one row of queries returned from the SELECT statement
Multiline subquery: Returning multiple rows of queries from a SELECT statement
Single line subquery
A single-line subquery is a query that returns one row from an inner query. Use a single line operator in the subquery type. A single line operator is listed on the slide.
Cases
Displays those employees with the same job ID as the employee 141.
SELECT last_name, job_id
From Employees
WHERE job_id =
(SELECT job_id
From Employees
WHERE employee_id = 141);
SELECT last_name, job_id, 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);
Show job IDs that are the same as employees 141, and those whose salaries are higher than the employee's 143.
Note: external and internal queries can obtain data from different tables.
SELECT last_name, job_id, salary
From Employees
WHERE salary =
(SELECT MIN (Salary)
From employees);
Ask everyone who has the least pay.
SELECT department_id, MIN (Salary)
From Employees
GROUP by department_id
Having MIN (Salary) >
(SELECT MIN (Salary)
From Employees
WHERE department_id = 50);
Ask for minimum wages in each department, but higher than 50th.
SELECT employee_id, last_name
From Employees
WHERE salary =
(SELECT MIN (Salary)
From Employees
GROUP by department_id);
The problem now is that a single subquery returns multiple query values;
should read:
SELECT employee_id, last_name
From Employees
WHERE Salary in
(SELECT MIN (Salary)
From Employees
GROUP by department_id);
SELECT last_name, job_id
From Employees
WHERE job_id =
(SELECT job_id
From Employees
WHERE last_name = ' Haas ');
If the subquery returns a value of 0, it does not affect the main program;
If the subquery returns a null value, it affects the return value of the main program;
SELECT employee_id,
Last_Name
From Employees
WHERE employee_id not in
(SELECT manager_id
From employees)
SELECT employee_id,
Last_Name
From Employees
WHERE employee_id not in
(SELECT manager_id
From Employees
WHERE manager_id is not NULL)
The return value of a subquery placed under Select must be a specific value.
A subquery can also be added from the back;
Having the following can also be added to the subquery;
The order is also available after;
Dolez query adapts to: pair comparison;
SELECT employee_id, manager_id, department_id
From Employees
WHERE (manager_id, department_id) in
(SELECT manager_id, department_id
From Employees
WHERE employee_id in (178,174))
and employee_id not in (178,174);
Output: 176 149 80
Your query will succeed only if you want to query for something that corresponds to one by one of what you have returned from your subquery.
If one does not correspond then your query will not succeed.
Non-aligned subquery:
SELECT employee_id, manager_id, department_id
From Employees
WHERE manager_id in (SELECT manager_id
From Employees
WHERE employee_id in (174,141))
and department_id in (SELECT department_id
From Employees
WHERE employee_id in (174,141))
and employee_id not in (174,141);
Output: 144 124 50
143 124 50
142 124 50
176 149 80
The above two programs are the differences between subqueries and non-subqueries.
If I want to show employee information, ask: The employee's salary is higher than the average wage in the department.
SELECT A.last_name,
A.salary,
a.department_id
From Employees A
WHERE a.salary >
(SELECT AVG (Salary)
From Employees b
WHERE b.department_id = a.department_id);
In line view (inline view)
SELECT A.last_name, A.salary,
A.DEPARTMENT_ID, B.salavg
From employees A, (SELECT department_id,
AVG (Salary) Salavg
From Employees
GROUP by department_id) b
WHERE a.department_id = b.department_id
and A.salary > B.salavg;
Data scattered across multiple tables needs to be flattened
SELECT DISTINCT C.lastname, C.firstname
From Person.Contact c JOIN humanresources.employee E
On e.contactid = C.contactid WHERE EmployeeID in
(SELECT SalesPersonID
From Sales.SalesOrderHeader
WHERE SalesOrderID in
(SELECT SalesOrderID
From Sales.SalesOrderDetail
WHERE ProductID in
(SELECT ProductID
From Production.Product P
WHERE productnumber like ' fw123% '));