multi-table query

This article mainly talks about four points:

(1) oracle multi-table query (2) join query of SQL99 standard (3) sub-query (4) hierarchical query

There are two ways of Oracle multi-table query, one is oracle's unique query mode, the other is SQL99 standard join query, and it is a general multi-table query.

1. Oracle Connection

Equivalent connection: where to add a connection condition. The same column in the table can be prefixed before the column name.

--Searching for Employees id Name of Employee, Department Name of Employee, City of Department where Employee is located
select e.employee_id,e.first_name,d.department_name,l.city  --These three attributes are not in one table, so establish relationships
from employees e, departments d,locations l 
where e.department_id=d.department_id  and d.location_id =l.location_id

External connection: Using external connection can query data that do not meet the conditions. Symbols of External Connections (+)

--Searching for Employees id Name Departments Name Requires All Employees'Information to be Displayed without Departments' Employees
select  e.employee_id,e.first_name, d.department_name
from employees  e,departments d
where  e.department_id=d.department_id(+)
--Because there may be no department for employees, the default is not to show at this time, to show all employees, just add to the staff.(+)

--Searching for Employees id Name Department Name Requires All Departmental Information No Employee Departments to be Displayed
select  e.employee_id,e.first_name, d.department_name
from employees  e,departments d
where  e.department_id(+)=d.department_id
--Similarly, there will be departments without employees. Ibid.

Self-join: It's all in a unified table

--Display all employee name numbers and the names of superior leaders
select e.employee_id,e.first_name, ee.first_name
from employees e, employees ee
where  e.manager_id=ee.employee_id

 

2. Connection Query of SQL99 Standard

A generic multi-table query with oreal-specific features

Join  on :

---Searching for Employees id Name Department
select e.employee_id,e.first_name, d.department_name
from  employees  e join departments d on e.department_id= d.department_id;

Natural join: The natural join clause uses use uses columns with the same name in two tables as the equivalent join condition, and queries the qualified data in the table.

-- Departmental id   The name of the department. The name of the city where the Department is located.
select d.department_id,d.department_name,l.city
from departments d  natural join  locations l  --It will look up in order to see if they are the same.

Join using: The using clause can be used to filter if more than one column satisfies the criteria. Do not add a table name or prefix or alias to the selected column.

select e.employee_id,e.last_name,d.location_id
from employees e join departments d using(department_id)--I feel better than Natural join More practical.

Outer join:

Lift outer join: Left outer join

Right outer join on right outer connection

Full External Join: Choose the one that is unsatisfactory on both sides

--Searching for Employees id  Employee's Name and Employee's Departments Require to Display All Employee Information Without Departmental Employee Information
select  e.employee_id, e.first_name,d.department_name
from employees e left outer join departments d 
on  e.department_id=d.department_id; --Left Full Represents Left Full Display

3. sub queries

--Find out the wage ratio OConnell Everyone with a high salary
select  first_name,salary 
from employees e
where e.salary>(select salary from employees where  last_name='OConnelll')
--The last thing to return in parentheses is OConnell A person's salary 

There are two hidden columns in oracle

rowid is the only non-repetitive rownum that is hidden to identify when data rows

Rownum:

-----Find out the top three salaries
select a.first_name,a.salary from
(select rowid,rownum, e.first_name,e.salary
from employees e 
order by e.salary desc) a
where rownum<=3
--Wages have been reduced in parentheses, but rownum It's already from 1 before it's sorted.,2...Start with it. You sort it out at last. rownum It's a mess. It's a new watch outside. It's also hidden. rownum，It's also 1 at this time.,2....Ordering, but also for descending order, so rownum<=3 You can have the top three salaries.

Paging query: using oracle grammar to write sql for a paging query

--There are 107 conditional data showing 10 items per page.     Query Page 3 Data 31-40  Requirements are ranked according to wages
--The first step completes the sorting and the second step solidifies rownum   The third step is screening according to conditions.

select rn r,t2.first_name,t2.salary from 
(select rownum rn,t1.* from
(select  *
from employees e 
order by e.salary desc) t1) t2
where rn>=31 and rn<=40
--The first parentheses are just descending, and the second parentheses make rownum and salary There are ordered arrangements, and the third is instantiation. rownum This column is what he became. r Column.
--meanwhile where It can't be aliased, so use it. rn，Instead of r，Because order where Give priority to select

Using group functions in subqueries

--Find out which employees have the lowest salary
select first_name,salary  from employees  
where  salary= (select min(salary) from employees )--In parentheses is a minimum wage.

---Screen out the minimum wages for departments that are higher than those for departments that are grouped according to departments and find out which departments have higher minimum wages than those for departments 50.
--In subqueries HAVING clause
select department_id,min(salary) --Yes min That means you can't have it. where
from employees e
group by department_id 
having min(salary)>( select min(salary) from  employees  where department_id=50)
--The minimum wage in parentheses is in sector 50.

Multi-line sub-query:

In (equal to any one in the list)

select  first_name,salary  from employees  e 
where e.salary in( select salary from employees ee where ee.salary<10000) 
--It's equivalent to just satisfying one of them and adding one to the feeling. in It doesn't make any sense at all.

ANY (compared with any value returned by a subquery)

select  first_name,salary  from employees  e 
where e.salary > any( select salary from employees ee where ee.salary<10000) 
--It's equivalent to comparing with the smallest value in parentheses.

ALL (compared with all values returned by subqueries)

That's equivalent to comparing with the largest value in parentheses.

 

4. Hierarchical query:

You can see clearly the relationship between superiors and subordinates.

Hierarchical queries can be queried from top to bottom or from bottom to top.

-- Search from the bottom
select employee_id,last_name,job_id,manager_id 
from employees
start with employee_id=104
connect by prior manager_id=employee_id 

Operation results:

--Query from top to bottom
select last_name,employee_id, manager_id
from employees e 
start with last_name='King'
connect by prior  employee_id =manager_id

--Use level and lpad  Formatted Hierarchical Query
select lpad(last_name,length(last_name)+(level*3)-2,'_')
from employees e 
start with last_name='King'
connect by prior  employee_id =manager_id

Operation results:

 

So far as this document is concerned, there are some shortcomings. Welcome to leave a message and give some advice.