1. subquery knowledge system, single-row subquery, multi-row subquery

1. query the employee information with higher salaries than scott. Step 1: query the employee's salary selectsalfromempwhereenameSCOTT; Step 2: query the employee information with higher salaries than scott. select * fromempwheresal3000; conclusion: subquery essence: nesting of multiple select statements 2: Knowledge body of subquery

1. query the employee information with higher salaries than scott. Step 1: query the employee's salary of scott. select sal from emp where ename = 'Scott '; Step 2: select * fromemp where sal 3000 is used to query the employee information with higher salaries than scott. Conclusion: The nature of subqueries: nesting of multiple select statements 2: Knowledge body of subqueries

Zookeeper

1. query employee information with higher salaries than scott

Step A: Check scott's employee salary.

Select sal from emp

Where ename = 'Scott ';

Step 2 B: query the employee information with higher salaries than scott

Select * fromemp

Where sal> 3000;

Summary:

Subquery essence: nesting of multiple select statements

2: Build a Knowledge System for subqueries

A reasonable Writing Style

B subquery () do not forget

The C subquery and primary query can query the same table or different tables.

The primary query can be used as long as the results returned by the subquery.

D. Where can I prevent subqueries?

Select a, B, c --- OK, can only store single row subqueries, cannot make multi-row subqueries From tab1 --- OK can have subqueries Where col in (em1, em2) --- subquery Col between a1 and a2 Col> 222 Col> () Group... --- Subqueries are not allowed. Having .... --- Subqueries are allowed. Order... --- Subqueries are not allowed.

E subquery category

1. Single Row operators correspond to single row subqueries, and multi-row operators correspond to multiple row subqueries.

2. The number of entries returned by the subquery is divided into single-row subqueries and multi-row subqueries.

3. Single Row subqueries can only use single row comparison operators (>>=<=<>)

4. multi-row subqueries can only use the multi-row comparison operator (int any all)

3 single row subquery

Eg: the query of employee information is the same as that of the 141 job type. The salary is higher than that of the 143 job.

SQL> conn hr/123456 Connected. SQL> select last_name, job_id, salary 2 FROM employees 3 WHERE job_id = 4 (SELECT job_id 5 FROM employees 6 WHERE employee_id = 141 );

Eg: query the employee information with the lowest salary

Select last_name, job_id, salary FROM employees WHERE salary = (Select min (salary) FROM employees );

Eg: Ask the minimum wage of each department number and Department (the minimum wage is higher than the minimum wage of Department 50)

Train of Thought Analysis: Look at the subquery to see the group by and having condition to retrieve and view the retrieval object // Subquery: minimum wage of Department 50 À retrieve the minimum wage of each department and the minimum wage of each department that is higher than the minimum wage of Department 50

SELECT department_id, MIN (salary) FROM employees GROUP By department_id Having min (salary)> (Select min (salary) FROM employees WHERE department_id = 50 );

4. query the employee information whose department name is SALES (method 2)

Method 1: subquery

SELECT * FROM emp Where deptno = (SELECT deptno FROM dept WHERE dname = 'sales ');

Method 2: Multi-Table query

SELECT e .* FROM emp e, dept d WHERE e. deptno = d. deptno and d. dname = 'sales ';

Note: The second method (Multi-table queries run faster than subqueries, because multi-table queries read data in the memory at a time, consuming memory and accelerating the speed) First (the Sub-query requires two operations to connect to the database, and the operation to connect to the database is a time-consuming operation, reducing the speed ).

5 The columns to be queried After select can be single-row subqueries, but not multi-row subqueries

Case study:

Select ename, empno, (select deptno from emp) AA from emp;

Correct case:

Select ename, empno, (select deptno from emp where EMPNO = 7369) AA from emp;

VcuuPC9wPgoKPHRhYmxlIGJvcmRlcj0 = "1" cellspacing = "0" cellpadding = "0">

Select * From (select ename, sal From emp );

7 multi-row subquery

Multi-row subquery can only use the multi-row comparison operator (in any all)

-- For example, you can use either of the following methods to query the employee information of a department whose name is * (not) SALES or ACCOUNTING.

-- Eg query information about employees with higher salaries than any employee in department 30

-Eg: Query Information about employees with higher salaries than all employees in department 30.

You can use either of the following methods to query the employee information of a department whose name is * (not) SALES or ACCOUNTING.

Select * From emp Where deptno in (Select deptno From dept Where dname = 'sales' or dname = 'accounting ');

Operator	Description
IN	Equal to any
ANY	Compare with any value returned by the subquery
ALL	Compare with all values returned by the subquery

ANY

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 _ proc ';

ALL

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 _ proc'

Eg: The employee information higher than the salary of any employee in department 30 is greater than the minimum value of any in the set.

Select * From emp Where sal> all (select sal From emp Where deptno = 30 );

It is equivalent:

Select * From emp Where sal> (select max (sal) From emp Where deptno = 30 );

Query is the manager's employee information

Analysis: SELECT * FROM emp WHERE empno in (manager set );

Actual: Select * From emp Where empno in ( Select mgr from emp );

Select * From emp Where empno not in ( Select mgr From emp Where mgr is not null );