Oracle Multi-Table query

Multi-Table Query

1.1 Internal Connection
The INNER JOIN keyword is typically used to specify an inner join, inner can be omitted, and the default represents an inner join. The query results contain only rows with equal values for the public fields of the two tables, which can be any of the columns in the two tables

1.2 External connection Query
Includes left outer connection, right outer join, full outer connection

(1) LEFT OUTER JOIN
The result set includes rows that meet the join criteria specified after on after two tables are connected, and all rows in the left table of the JOIN keyword that satisfy the criteria for the search, and if a row in the left table does not have a matching row in the right table, then all the selection columns of the right table are null in the result.

(2) Right outer join
Is the reverse connection of the left outer connection.

(3) Fully external connection full join
The result set for a full outer join query consists of two table-joined result sets and rows that do not meet the criteria in the left table and the right table.

The following is a multi-table query using the EMP and Dept tables:

1  Select ename,dname 2  from EMP 3 where ename='SMITH'

Execution Result:

Change to:

1 Select ename, (selectfromwhere deptno=  Division    2  from EMP 3 where ename='SMITH

If you do not know that Smith is in department number 20th, you will change to

1  Select ename, (selectfromwhere deptno=(select from where ename='SMITH' as Department   

Execution Result:

Multi-Table Query

1. Internal connection
The INNER JOIN keyword is typically used to specify an inner join, inner can be omitted, and the default represents an inner join. The query results contain only rows with equal values for the public fields of the two tables, which can be any of the columns in the two tables

1  Select enme,dname 2  from emp,dept 3 where Emp.deptmo= and Emp.ename='SMITH'

2. External connection

Oracle Multi-Table query