SQL Base subquery (11)

Subquery: Type, syntax, and considerations

What problems can be solved by using subqueries?

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/8E/A4/wKioL1jHqvaAi5l5AABtYPakz2M348.jpg "title=" Qq20170314135459.jpg "alt=" Wkiol1jhqvaai5l5aabtypakz2m348.jpg "/>

Sub-query syntax:

Select select_list from table where expr operator (select select_list from table);

• subqueries (internal queries) are executed before the main query (out-of-query).

• The primary query uses the subquery results.

• Location: select,where,from,having

1, inquire who's salary is higher than Abel

Select Last_Name, salary from employees

Where Salary >

(Select Salary

From Employees

where last_name = ' Abel ');

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/8E/A6/wKiom1jHsNqjz0PkAABsK2VriWA397.jpg "title=" Qq20170314135459.jpg "width=" "height=" 375 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:750px;height:375px; "alt = "Wkiom1jhsnqjz0pkaabsk2vriwa397.jpg"/>

Considerations for using sub-queries

• The subquery is to be enclosed within parentheses.

• Enhance readability by placing subqueries on the right side of the comparison condition (subqueries can appear on both sides of the comparison operator)

• Single row operator for single row subquery, multiline operator for multiline subquery

Single-line subquery:

– Group functions in a subquery

Subqueries in the –having clause

• Return only one row

• Using single-line comparison operators

  
  

Operator	Meaning
=	Equals
>	Greater than
>=	Greater than or equal
<	Less than
<=	Less than or equal
<>	Not equal to

Select Last_Name, job_id, salary from employees

where job_id in (select job_id from Employees

where last_name like ' Taylor ')

and salary in

(Select Salary

From Employees

where last_name like ' Taylor ');

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/8E/A6/wKiom1jHtBOQ7GOyAABwuTaqM_M121.jpg "title=" Qq20170314135459.jpg "width=" "height=" 159 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:750PX;HEIGHT:159PX; "alt = "Wkiom1jhtboq7goyaabwutaqm_m121.jpg"/>

Using group functions in subqueries

Select Last_Name, job_id, salary from employees where

Salary = (select min (Salary) from employees);

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/8E/A6/wKiom1jHtGeRiG-7AAA9uqj_8nk378.jpg "title=" Qq20170314135459.jpg "width=" 770 "height=" 197 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:770PX;HEIGHT:197PX; "alt = "Wkiom1jhtgerig-7aaa9uqj_8nk378.jpg"/>

HAVING clause in subquery

• Perform a subquery first

• Returns the result to the HAVING clause in the main query

Select department_id, min (Salary)

From Employees

GROUP BY department_id

Having min (salary) >

(select min (Salary)

From Employees

where department_id = 50);

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/8E/A6/wKiom1jHtS7h4McRAABdKCs5ddg593.jpg "title=" Qq20170314135459.jpg "width=" "height=" 449 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:700PX;HEIGHT:449PX; "alt = "Wkiom1jhts7h4mcraabdkcs5ddg593.jpg"/>

Multiline subqueries use a single-line comparer, and the following is an incorrect notation

Select employee_id, last_name

From Employees

where salary =

(select min (Salary)

From Employees

Group by department_id);

Null value problem in sub-query

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/8E/A4/wKioL1jHteXjowVRAAAq6WRkNzU513.jpg "title=" Qq20170314135459.jpg "width=" "height=" "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:700px;height:210px; "alt = "Wkiol1jhtexjowvraaaq6wrknzu513.jpg"/>

Select Last_Name, job_id from Employees

where job_id =

(select job_id from Employees

where last_name = ' Haas ');

Multi-row subqueries

– Use all or any

• Returns multiple rows.

• Use the multiline comparison operator.

Operator	Meaning
Inch	equals any one of the values in the list
Any	Must be used before =,! =, <, <=, >= operator, compared to each value in the list, if no rows are returned, the result evaluates to False
All	Must be used before =,! =, <, <=, >= operator, compared to each value in the list, if no rows are returned, the result evaluates to True

Examples of Use:

Using any in multiline subqueries

Select employee_id, last_name, job_id, salary

From employees where salary < any

(Select Salary

From Employees

where job_id = ' It_prog ')

and job_id < > ' It_prog ';

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/8E/A6/wKiom1jHt8LTA-IiAADQ_EwqKZw542.jpg "title=" Qq20170314135459.jpg "alt=" Wkiom1jht8lta-iiaadq_ewqkzw542.jpg "/>

Using the all operator in multiline subqueries

Select employee_id, last_name, job_id, salary

From Employees

Where salary < All

(Select Salary

From Employees

where job_id = ' It_prog ')

and job_id <> ' It_prog ';

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/8E/A4/wKioL1jHt6DD9JYjAADE2eDGWKo847.jpg "title=" Qq20170314135459.jpg "alt=" Wkiol1jht6dd9jyjaade2edgwko847.jpg "/>

Null values in a subquery

Select Emp.last_name

From Employees EMP

where emp.employee_id not in

(Select mgr.manager_id

From employees MGR);

This article is from the "record a bit of learning life" blog, please make sure to keep this source http://ureysky.blog.51cto.com/2893832/1906425

SQL Base subquery (11)