SQL basics -- & gt; subquery, SQL basics -- Query

SQL basics --> subquery, SQL basics -- Query
-- ======================================
-- SQL basics --> subquery
-- ======================================
 
I. subquery
A subquery is a query in a SELECT, UPDATE, or DELETE statement.

Ii. subquery category
Single Row subquery
Returns zero or one row.
Multi-row subquery
Returns one or more rows.
Multi-column subquery
Return multiple columns
Related subqueries
Reference one or more columns in an External SQL statement
Nested subquery
Query in other subqueries
 
Iii. subquery syntax

SQL>
SELECT select_list
FROM table
WHERE expr operator (SELECT select_list FROM table );

The subquery (internal query) is executed once before the primary query is executed, and the primary query (external query) uses the results of this subquery.

4. subquery rules
Enclose the subquery in parentheses.
Place the subquery on the right of the comparison Condition
The order by clause is only required for subqueries when Top-N analysis is performed.
Single-row operators are used for single-row subqueries, and multi-row operators are used for multi-row subqueries.

5. Single Row subquery
Returns only one row.
Comparison operators of tables with single rows: =, >,>=, <,<=, <>

-- Use subquery in the where clause
SQL>
Select ename, job
From emp
Where e-mapreduce = (select e-mapreduce from e-mapreduce where mgr = 7902 );

-- Subquery using grouping Functions
SQL>
Select ename, job, sal from emp where sal> (select avg (sal) from emp );

-- Use subquery in HAVING clause
SQL>
Select deptno, min (sal)
From emp
Group by deptno
Having min (sal)> (select min (sal) from emp where deptno = 20 );

-- Use subquery in the from clause
SQL>
Select empno, ename from (select empno, ename from emp where deptno = 20 );

-- Common Errors in single row subqueries
-- 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 rows
ORA-01427: single-row subquery returns more than one row

-- The subquery cannot contain the order by clause.
SQL>
Select empno, ename
From emp
Where sal> (select avg (sal) from emp order by empno );
Order by empno)
 
ORA-00907: missing right parenthesis

-- No rows are returned in the subquery. The following statement can be correctly executed, but no data is returned.
SQL>
Select ename, job
From emp
Where e-mapreduce = (select e-mapreduce from e-mapreduce where mgr = 8000 );
 
Return Value: no rows selected

6. multi-row subquery
Returns multiple rows.
Use multi-line comparison operators IN, ANY, ALL

-- Use the IN operator IN multi-row subqueries
SQL>
Select empno, ename, job
From emp
Where sal in (select max (sal) from emp group by deptno );

-- Use the ANY operator in multi-row subqueries
Note:
{
For example, set (1, 2, 3, 4)
> Any indicates that only one of them can be larger.
> All indicates that it is larger than any of them.
}
SQL>
Select empno, ename, job
From emp
Where sal <any (select avg (sal) from emp group by deptno );

-- Use the ALL operator in multi-row subqueries
SQL>
Select empno, ename, job
From emp
Where sal> all (select avg (sal) from emp group by deptno );

VII. Related subqueries
Some fields in the primary query are used in the subquery. A subquery is executed for each row scanned in the primary query.

-- Query the Department number, name, and salary of employees whose salaries are higher than the average salaries of employees in 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 records of employees responsible for managing other employees (using exists)
Note: EXISTS is used to determine whether EXISTS. It is similar to in, but the 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 employees 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)

Note: Comparison between EXISTS and not exists and IN and NOT IN
EXISTS and IN are different:
EXISTS only checks the existence of rows. IN checks the existence of actual values (IN general, the performance of EXISTS is higher than IN)
Not exists and not in:
If the value list contains null values, true is returned for not exists, and false is returned for not in.

-- Check the following query to query the department names and locations where the Department number does not appear in the emp table.
SQL>
Select deptno, dname, loc
From dept d
Where not exists (select 1 from emp e where e. deptno = d. deptno );

-- IN and null
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 ('20140901', NULL );

Note: subqueries must be included in brackets.
Subqueries are generally placed on the right of the comparison condition.
Do not use order by in subqueries unless you perform a TOP-N analysis.

8. Multi-column subquery
1. paired comparison

-- Query records with the highest salary in a department
SQL>
Select *
From scott. emp
Where (sal, job) in (select max (sal), job from scott. emp group by job );

-- Non-paired comparison, implementing similar functions
SQL>
Select *
From scott. emp
Where sal in (select max (sal) from scott. emp group by job)
And job in (select distinct job from scott. emp );

9. nested subqueries
That is, the subquery is located inside the subquery. The number of nested layers can reach a maximum of layers. However, avoid using nested subqueries as much as possible, and the query performance with table join 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: In addition to count (*), null values are ignored in subqueries.