Oracle Connection Query

An inner join (inner join), an outer join (outer join) has been learned in SQL Server, and an outer join is divided into a left outer join (outer join) and
Right outer join (outer join). An outer join between two tables or several tables in Oracle is represented by a (+).

Code Demo: Please check out the employee's name, department, work, salary, salary is more than 2000 yuan.
Because the department name is in dept, the other information in the EMP table requires an inner join to complete.

Sql> SELECT e.ename,e.job,e.sal,d.dname  2 from  emp e,dept d  3  WHERE e.deptno=d.deptno  4  and e.sal>2000;

You can also use an inner join in the SQL/92 standard:

Code Demo: INNER JOIN

SELECT e.ename,e.job,e.sal,d.dname from EMP e INNER joins DEPT D on E.deptno=d.deptnowhere e.sal>2000;

In this inner join, the keyword inner can be omitted.

Code Demo: Find out the employee's name and salary under each department. The EMP table uses foreign key deptno to refer to the Deptno in the Dept table,
In the Dept table, if there are no employees in some departments, an inner join will not be able to display the department with no employees, so the outer join must be based on the Dept table.

Sql> SELECT e.ename,e.job,e.sal,d.dname  2 from  EMP E, DEPT d  3  WHERE  e.deptno (+) =d.deptno  4  

Code parsing:
(+): Oracle-specific connector, which appears on the left side of the right outer join, appears on the right to refer to the left outer join.

You can also use the SQL/92 standard notation:
Code Demo: Outer JOIN

SELECT E.ename,e.job,e.sal,d.dname from EMP e right OUTER joins DEPT D on E.deptno=d.deptno;

Here right OUTER join, the keyword OUTER can be omitted.

Oracle Connection Query