Oracle multi-table join and subquery

1. Connection:

1. equijoin
Dikar set connection
Select ename, A. deptno as a_deptno, B. deptno as B _deptno, B. dname as department
From emp a, DEPT B
Equijoin
Select ename, A. deptno as a_deptno, B. deptno as B _deptno, B. dname as department
From emp a, DEPT B
Where a. deptno = B. deptno;
2. For non-equivalent connections, find the employee name, salary, grade, salary online, and salary offline in the EMP and salgrade tables.
Select ename as name, sal as salary, grade as salary level, losal as salary online, hisal as salary offline
From emp, salgrade
Where sal between losal and hisal;
3. External Connection

An external connection is an extension of an internal connection. It not only records that meet the connection conditions, but also records that do not meet the connection conditions are returned. The syntax is as follows:
Right Outer Join
Select e. ename, d. dname, e. deptno
From emp e, dept d
Where e. deptno = d. deptno (+ );
The left outer connection is opposite to the right outer connection.

Use hr to log on to the employees and departments tables.
Select first_name as name, department_name as department name, d. department_id as department ID
From employees e, departments d
Where e. department_id = d. department_id (+ );
4. Self-connection: query the employee ID and name of each employee and supervisor in the same table.
Select a. empno as employee number, a. ename as employee name, a. mgr as superior employee number, B. ename as superior name
From emp a, emp B
Where a. mgr = B. empno;
5. sql99
Cross join ------ equivalent to the dikar set
Select e. ename, d. dname
From emp e cross join dept d;

Natural join ------ equivalent join
Select e. ename, d. dname
From emp e natural join dept d;

Using clause ----- query using the same name column
Select E. ename, D. dname
From EMP e join dept d
Using (deptno );

On clause ------ When the column name is different from the on Clause
Use on to query two tables
Select E. ename, D. dname
From EMP e join dept d
On e. deptno = d. deptno;

Use on to query multiple tables
Select e. ename, d. dname
From emp e join dept d
On e. deptno = d. deptno
Join the third table
On column 1 = Column 2;

Inner join ------- an Inner join returns only data that meets the connection conditions.
Select employee_id, last_name, salary, department_id, department_name
From employees inner join orders ments using (department_id );

Left Outer Join
Select employee_id, last_name, salary, department_id, department_name
From employees left join orders using (department_id );
Right Outer Join
Select employee_id, last_name, salary, department_id, department_name
From employees right join orders using (department_id );

Full outer connection
Select employee_id, last_name, salary, department_id, department_name
From employees full outer join orders ments using (department_id );

 

Ii. subquery:

1. subquery Syntax:
Select select_list
From table
Where expr Operator
(Select select_list
From table );

2. subqueries are divided:
"Standard subquery": The subquery is executed only once.
"Join subquery": the primary query is executed once, And the subquery is executed once. The subquery depends on the parameters of the primary query.
Select * from jobs
Where job_id> 1 and Exists (select * from jobs where job_id = a. job_id-1)

3. Sub-query policy:
(1) subqueries should be included in the arc;
(2) The subquery should be placed on the right of the comparison operator.
(3) The Order by clause is unnecessary in subqueries unless Top-N analysis is required.
(4) Single Row subquery with single value operator, multi-row subquery with multiple value operator.

(5) subqueries should be considered when the query is based on unknown values.

Note:

If no results are returned for the subquery, no results are returned for the primary query.

If a subquery returns a single row result, it is a single row subquery. You can use the corresponding single row record comparison operator in the primary query.

If a subquery returns multiple rows of results, it is a multi-row subquery. The single row record comparison operator cannot be used for this subquery. Only IN, ANY, ALL

　　　　

Comparison operators for multi-row subqueries:
IN, ANY, ALL

Here, ANY and ALL can be replaced by "comparing the statistical results of Aggregate functions (Max, Min.

Select stor_id, min (qty) from sales
Group by stor_id
Having min (qty) in (select min (qty) from sales group by stor_id );
This is an example of a superfluous query.

4. subquery example:

Find people with higher salaries than scott
Select ename, sal from emp
Where sal>
(Select sal from emp where ename = 'Scott ');
Search for people with the same positions as scott
Select ename, job from emp
Where job =
(Select job from emp where ename = 'Scott ')
And ename <> 'Scott ';

Any usage <any means less than the maximum,> any is greater than the minimum
Select empno, ename, sal, job
From emp
Where sal <any (select sal from emp where job = 'salesman ');

<All: less than all, that is, less than least,> all: greater than all, that is, greater than the maximum
Select empno, ename, Sal, job
From EMP
Where Sal <all (select Sal from EMP where job = 'salesman ');

Iii. Oracle topn query:
In Oracle, subqueries are usually used to implement Top N queries.
Syntax format:
Select Field List
From (select Field List fromtable order by sorting field)
Where rownum <= N