Oracle connection Query

Connection query is a frequently used query method in database query statements. The following describes how to query connections based on the official documents provided by Oracle:

1. Connection Type:

1) Unique oracle connection types:

A.Equijoin equivalent join

SQL> select e. employee_id, e. last_name,
2 e. department_id, d. department_id,
3 d. location_id
4 from employees e, departments d
5 whereE. department_id = d. department_id;

EMPLOYEE_ID LAST_NAME DEPARTMENT_ID LOCATION_ID
-------------------------------------------------------------------------
198 OConnell 50 1500
199 Grant 50 50 1500
200 Whalen 10 10 1700
201 Hartstein 20 1800

......

B. Non-Equijoin Non-equivalent join

 

SQL> select e. last_name, e. salary, j. grade
2 from employees e, job_grades j
3 whereE. salary between j. lowest_sal and j. highest_sal;

 

LAST_NAME SALARY G
------------------------------------
Olson 2100
Philtan Ker 2200
Markle 2200

...

Feene3000 B
Cabrio 3000 B
Fleau R 3100 B
China East 2

...

Hartstein 13000 C
Partners 13500 C

Partners 13500 D
Russell 14000 D
De Haan 17000 E
Kochhar 17000 E

C. Outer join

SQL> select e. last_name, e. department_id, d. department_name
2 from employees e, departments d
3 whereE. department_id (+) = d. department_id;

 

Equivalent to: select e. last_name, e. department_id, d. department_name
From employees eRIGHT JOINAdministrative ments d
ONE. department_id = d. department_id;

LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
--------------------------------------------------------------------
Greenberg 100 Finance
Sciarra 100 Finance
Urman 100 Finance
Popp 100 Finance
Faviet 100 Finance
Gietz 110 Accounting
Higgins 110 Accounting
Treasury
 Corporate Tax
Control And Credit
Registrant holder Services

...............

Note: Here (+) is the connector. placing it on the LEFT of the equal sign represents the right join (equivalent to the right join), and placing it on the RIGHT of the equal sign represents the left join (equivalent to the left join ).

Right join: searches for all Department table IDs, department names, and employee names of the corresponding department IDs in the employee table. If not, NULL is returned.

D. Self join

SQL> select worker. last_name | 'Works for '| manager. last_name
2 from employees worker, employees manager
3 whereWorker. manager_id = manager. employee_id;

WORKER. LAST_NAME | 'worksfor' | MANAGER. LAST_NAME
------------------------------------------------------------
OConnellworks for Mourgos
Grantworks for Mourgos
Whalenworks for Kochhar
Hartsteinworks for King
Fayworks for Hartstein
Mavrisworks for Kochhar

............

2)Connections meeting the 1999 specifications:

Cross joins
Natural joins Natural connection
Using clause usage conditions
Full or two sided outer joins Full connection
Arbitrary join conditions for outer joins is an Arbitrary addition condition to the outer join.