--=========================
--sql Base--subquery
--=========================
One, sub-query
A subquery is a query that is inside a select, UPDATE, or DELETE statement
Ii. classification of sub-queries
Single-line subquery
Returns 0 rows or one row
Multi-row subqueries
Return one or more rows
Dolez Query
Return multiple columns
Related sub-query
Referencing one or more columns in an external SQL statement
Nested subqueries
Queries that are located in other sub-queries
Third, sub-query syntax
Sql>
SELECT select_list
From table
WHERE expr operator (SELECT select_list from table);
a subquery (internal query) executes once before the main query is executed, and then the main query (external query) uses the results of that subquery
Iv. rules for sub-queries
Enclosing a subquery in parentheses
Place a subquery to the right of the comparison condition
The ORDER BY clause is only required in a subquery when performing a sort top-n analysis
Single-row operators for single-row subqueries, multi-line operators for multiline subqueries
Five, single-row sub-query
Return only one row
Table-less operator using single-line: =, >=,,<, <=,<>
- -using subqueries in the WHERE clause
Sql>
Select ename, Job
From EMP
where empno = (select empno from emp where mgr = 7902);
--sub-query using the GROUPING function
Sql>
Select ename, Job, Sal from EMP where sal > (select AVG (SAL) from EMP);
--using subqueries in the HAVING clause
Sql>
Select Deptno, min (sal)
From EMP
GROUP BY Deptno
Have min (sal) > (select min (sal) from emp where deptno = 20);
--using subqueries in the FROM clause
Sql>
Select Empno, ename from (select Empno, ename from emp where deptno = 20);
--common errors in single-row subqueries
- -the result of the subquery returns more than one row
Sql>
Select Empno, ename
From EMP
Where Sal = (select Sal from emp where deptno = 20);
(select Sal returns multiple lines
Ora-01427:single-row subquery returns more than one row
--cannot include ORDER BY clause in subquery
Sql>
Select Empno, ename
From EMP
Where Sal > (select AVG (SAL) from emp Order by empno);
ORDER by Empno)
Ora-00907:missing Right Parenthesis
--There is no return row inside the subquery, the following statement can be executed correctly, but no data is returned
Sql>
Select ename, Job
From EMP
where empno = (select empno from emp where mgr = 8000);
Return: No rows selected
six, multi-row sub-query
return multiple rows
Use the multiline comparison operator in, any, all
- -using the in operator in a multiline subquery
Sql>
Select Empno, ename, Job
From EMP
Where Sal in (the Select Max (SAL) from the EMP Group by DEPTNO);
--using the any operator in multiline subqueries
Note:
{
such as set (1,2,3,4)
>any means it's as long as it's bigger than either one.
>all says it's bigger than any of them.
}
Sql>
Select Empno, ename, Job
From EMP
Where Sal < any (select AVG (SAL) from EMP Group by DEPTNO);
--Using the all operator in multiline subqueries
Sql>
Select Empno, ename, Job
From EMP
where Sal > All (select AVG (SAL) from EMP Group by DEPTNO);
Vii. related sub-queries
Some of the fields in the main query are used in the subquery, and the primary query executes the subquery one time per scan row
-The department number, name, and salary for the average wage of employees who are paid higher than the same department
Sql>
Select Deptno, ename, Sal
From EMP
outer where Sal > (select AVG (SAL)
From EMP
Inner where inner.deptno = Outer.deptno);
--Query the employee records responsible for managing other employees (using exists)
Note : EXISTS is a judgment whether it exists, and in similar, but efficiency is higher than in
Sql>
Select Empno, ename
From EMP
Outer where exists
(select empno from emp inner where inner.mgr = Outer.empno);
--query staff who do not manage other employees (not exists)
Sql>
Select Empno, ename
From EMP
Outer where NOT EXISTS
(select empno from emp inner where inner.mgr = Outer.empno)
Remarks: Comparison of EXISTS and not EXISTS with in and not
The difference between exists and in:
exists only checks the existence of a row, in order to check the existence of the actual value (generally exists performance is higher than in)
Not EXISTS and not in:
In the case where the value list contains null values, not EXISTS returns True, and not in returns false.
- -look at the following query, the department number is not in the EMP table appears in the department name and location
Sql>
Select Deptno, Dname, loc
From Dept D
Where NOT EXISTS (select 1 from emp e where e.deptno = D.deptno);
--in and Null values
Sql>
SELECT *
From EMP E
WHERE E.empno not in (SELECT 7369
From dual
UNION All
SELECT NULL from dual);
Sql>
SELECT * from emp e WHERE e.empno in (' 7369 ', NULL);
Note: Subqueries are included in parentheses
Subqueries are generally placed on the right side of the comparison criteria
do not use ORDER by in a subquery unless you perform a top-n analysis.
Eight, Dolez query
1. Comparison of paired pairs
- -check the highest record of salary for department
Sql>
SELECT *
From Scott.emp
Where (Sal, Job) in (the Select Max (SAL), job from Scott.emp Group by job);
-- non-paired comparisons to achieve similar functions as described above
Sql>
SELECT *
From Scott.emp
Where Sal in (the Select Max (SAL) from the Scott.emp Group by Job)
and job in (select distinct job from scott.emp);
Nine, nested subqueries
that is, a subquery inside a subquery, with a nesting layer up to the level. However, you should avoid using nested subqueries as much as possible, and query performance with table joins will be higher
Sql>
Select Deptno, Num_emp
From (select Deptno, Count (empno) as num_emp from EMP Group by Deptno) d
where Num_emp > 3;
Note: The subquery handles null values in addition to count (*), ignoring null values
SQL Base--subquery
