Oracle Getting Started sixth day (bottom)--Advanced sub-query

I. Overview

　　Main content:

　　

Second, sub-query introduction

　　1. Simple subquery (where subquery)

SELECT last_name  from    Employees WHERE  >                  (SELECT  salary                 from   employees                    WHERE  =149);

　　2. Multi-column subqueries

　　

　　Paired comparisons:

SELECTemployee_id, manager_id, department_id fromEmployeesWHERE(manager_id, department_id)inch                      (SELECTmanager_id, department_id fromEmployeesWHEREemployee_idinch(141,174)) andemployee_id not inch(141,174);

　　unpaired comparison:

SELECTemployee_id, manager_id, department_id fromEmployeesWHEREmanager_idinch(SELECTmanager_id fromEmployeesWHEREemployee_idinch(174,141)) anddepartment_idinch(SELECTdepartment_id fromEmployeesWHEREemployee_idinch(174,141)) andemployee_id not inch(174,141);

3. Using subqueries in the FROM clause

  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 Span style= "color: #808080;" >=   b.department_id  and  a.salary Span style= "color: #808080;" >>  b.salavg;  

　　4. Single-row sub-query

　　　　Using a single-column subquery in a case

--PROBLEM: Explicit employee's employee_id,last_name and location. --among them, if the employee department_id is the same as the department_id of location_id 1800,--the location is ' Canada ' and the rest is ' USA '. SELECTEmployee_id,last_name, ( Casedepartment_id when               (SELECTdepartment_id fromDepartmentsWHERElocation_id= 1800)          Then 'Canada'        ELSE 'USA'        END) Location--( case        --When department_id =        --(SELECT department_id from departments                 --WHERE location_id = 1800)        --Then ' Canada ' ELSE ' USA ' END fromEmployees

　　5. Related sub-query

　　　　The related subquery is that each subquery is related to the Outer main query.

A non-correlated subquery returns a consistent result each time, regardless of the primary query, such as just providing a fixed filter condition

　　

　　Grammar:

　　

　　Example:

 --  question: query employees for wages greater than the department level Last_name,salary and its department_id  select   last_name,salary,department_id  from  employees where  salary select  avg   (Salary)  from   employees  where  department_id outer  .department_id)   

6.exists operator

 --  question: Query the Emplo of company managers yee_id,last_name,job_id,department_id information  select   employee_id,last_name,job_id,department_id  from  employees where  exists   ( Span style= "color: #0000ff;" >select  1  from< /span> employees  where  manager_id Span style= "color: #808080;" >=  outer  .employee_id)   /pre>

　　Not exists in the same vein

　　7. Related Updates　　

　　　　Update data for another table using related subqueries based on data from one table

Grammar:

UPDATE table1 ALIAS1 SET    column = (SELECT  expression                 from   table2 alias2                 WHERE  ALIAS1. column =                             alias2. column);

　　Example:

UPDATE Employees E SET    =                (SELECT  department_name            from   departments D            WHERE  = d.department_id);

Related deletions similarly:

DELETE  from Employees E WHERE =              (SELECT  employee_id            from   emp_history                WHERE  = e.employee_id);

　　8.WITH clause

　　　　It's kind of like a ODPs CTE .

use the WITH clause to avoid repeating the same block of statements in a SELECT statement

The WITH clause executes the statement block in the clause once and stores it in the user's temporary table space

Use the WITH clause to improve query efficiency

　　　　Generally, it can be used for operations such as union to improve readability!

Grammar:

– for an alias withTmp as(Select *  fromtb_name) – for multiple aliases withtmp as(Select *  fromtb_name), TMP2 as(Select *  fromtb_name2), Tmp3 as(Select *  fromtb_name3), ...

　　Example:

 with TT  as      (Select from employees)     SELECT*from TT

　　For more with examples, refer to:https://www.cnblogs.com/linjiqin/archive/2013/06/24/3152667.html

Oracle Getting Started sixth day (bottom)--Advanced sub-query