Instance analysis SQL nested subquery

Instance analysis SQL nested subqueries: some junior programmers often use nested subqueries (subqueries include a subquery) in SQL syntax, this article provides a basic explanation, and I believe that new users will gain some benefits. Use the subquery Principle 1. A subquery must be placed in parentheses. 2. Place the subquery in the comparison Condition

Instance analysis SQL nested subqueries: some junior programmers often use nested subqueries (subqueries include a subquery) in SQL syntax, this article provides a basic explanation, and I believe that new users will gain some benefits. Use the subquery Principle 1. A subquery must be placed in parentheses. 2. Place the subquery in the comparison Condition

Instance analysis SQL nested subquery:

Some junior programmers often use subqueries in SQL syntax rather than nested subqueries (subqueries contain a subquery). In this article, we will give a basic explanation ,, I believe that new users will gain some benefits.

Principles for using subqueries

1. A subquery must be placed in parentheses.

2. Place the subquery on the right of the comparison condition to improve readability.

The subquery does not contain the order by clause. Only one order by clause can be used for a SELECT statement,

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. Two Comparison conditions can be used in a subquery: single-line and multi-line operators.

Subquery type

Single Row subquery: Only one row of query is returned from the SELECT statement.

Multi-row subquery: returns a multi-row query from an in-SELECT statement.

Single Row subquery

A single row subquery is a query that returns a row from an inner query. Use a single row operator in this subquery type. The single line operators are listed on the slides.

Example

Displays employees with the same job ID as 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 );

Displays the employees whose job ID is the same as employee 141 and whose salary is higher than employee 143.

Note: external and internal queries can retrieve data from different tables.

SELECT last_name, job_id, salary

FROM employees

WHERE salary =

(Select min (salary)

FROM employees );

Ask everyone who has the minimum salary.

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 the minimum salary for each department, but it is higher than the salary of Department 50.

SELECT employee_id, last_name

FROM employees

WHERE salary =

(Select min (salary)

FROM employees

Group by department_id );

The problem occurs: a single row subquery returns multiple query values;

Should be changed:

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 zero value, it will not affect the main program;

If the subquery returns a null value, the return value of the main program is affected;

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 in the select statement must be a specific value,

Subqueries can also be added after from;

You can also add subqueries after having;

Order by can also be followed;

Multi-column subqueries are applicable to: Paired comparison; non-paired 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 can be successful only when the items to be queried correspond to the items returned by your subquery.

If one cannot match, your query will not succeed.

Unpaired subqueries:

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 the child query and the Child query.

If I want to display employee information, the employee's salary is higher than the average salary of the Department.

SELECT a. last_name,

A. salary,

A. department_id

FROM employees

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 is scattered in multiple tables and needs to be combined.

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