Oracle Base Statement Practice record

1. Insert a record into Scott's EMP table

Insert into Scott.emp (empno,ename,job) VALUES (9527, ' EAST ', ' salesman ');

2. Scott's EMP Table left Outer Connection Dept table

Select E.empno,e.ename,e.job,d.deptno,d.dname

From Scott.emp E left join Scott.dept D
on e.deptno = D.deptno;

Results:

3. Scott's EMP Table right outer connection Dept table

Select E.empno,e.ename,e.job,d.deptno,d.dname

From Scott.emp e right join Scott.dept D
on e.deptno = D.deptno;

Results:

4, Scott's EMP table fully external Connection Dept table

Select E.empno,e.ename,e.job,d.deptno,d.dname
From Scott.emp e full join scott.dept D
on e.deptno = D.deptno;

Results:

5. Scott's EMP in-Table Connection Dept Table

Select E.empno,e.ename,e.job,d.deptno,d.dname
From Scott.emp e inner join scott.dept D
on e.deptno = D.deptno;

Results:

Summarize:

Inner connection (INNER join/join): The query result record line satisfies the connection condition;

Left OUTER Join/left join: The query result record row contains not only data rows that satisfy the condition of the join condition, but also data rows with the left table not satisfying the join condition;

Right outer join (OUTER join/right join): The query result record row contains not only data rows that satisfy the condition of the join condition, but also data rows with the right table not satisfying the join condition;

Complete outer join (full OUTER join/full join): The query result record row contains not only the data rows that satisfy the condition of the join condition, but also the data rows with the left table and the right table that do not satisfy the join condition;

Oracle Base Statement Practice record