Oracle Day04 Subqueries

1. Subqueries Solve the problem: When a simple query query does not reach the results, you can use a subquery to enrich the conditions of the query to achieve the purpose of displaying the results.

The format of a subquery: include it with a parenthesis, and then write the SQL statement inside
2. Considerations for sub-queries:

1). Note that you must have parentheses .
2). Be sure to pay attention to the writing format , not chaos.
3). Subqueries can be placed in the Select,from, where having the back .
4). Note that subqueries must not be placed behind group by .
5). The subquery and the main query can be not the same table, as long as the results of the subquery can be used in the main query.
6). The subquery is not generally sorted, but it needs to be sorted in Top-n .
7). in general, the subquery operation is performed before the main query operation, but the main query is executed in the related subquery before the subquery is executed.
8). For the result of a subquery, you can only use a single line operator if it is a single row, or multiple rows If it is multiple rows .
9). NULL handling in self-examination.

3. Example

1)--sub-query is placed behind select

Select Ename,job, (select Sal from EMP where empno=7566) subquery from EMP;

Note: The result can only be single-line when placed behind a select.

2)--from the back of the placement sub-query key grasp

--Query employee's name Payroll job

SELECT * FROM (
Select Ename,job,sal from emp
);

3)--where back and subquery

SELECT * FROM emp

where Sal > (
Select Sal from emp where ename = (
Select ename from emp where empno = 7566
)
);

Note: subqueries cannot be nested too much, generally nesting three layers, adding more will affect performance.

4) The order by cannot be followed by a subquery, which is syntactically possible, but it does not work.

5)--subquery and main query can be not the same table, as long as the results of the subquery can be used in the main query can be

--Query all employee information of the department named Sal

SELECT *
From EMP
Where deptno= (
Select Deptno from dept where Dname= ' SALES '
);

6)--For the result of a subquery, if it is a single line operator only, if it is more than one line, only a multiline operator is used.

Single-line operator: ><= <=! =
--Query Employee information for department number not 30
SELECT * FROM (SELECT * from emp where deptno! = 30);

SELECT * FROM (SELECT * from emp where deptno <> 30);

--<> to indicate not equal to

Multiline operator in, not in,any,all.
--Query and all employees in the common Department of Scott users and Clark users

SELECT * FROM emp

where Deptno in (
Select Deptno from emp where ename= ' SCOTT ' or ename= ' CLARK '
);

　　　　

Oracle Day04 Subqueries