Example analysis of SQL nested subqueries

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% '));