Oracle connection query and oracle connection

Oracle connection query and oracle connection

I have learned inner join and outer join in SQL Server. outer join is divided into left outer join and
Right outer join ). In Oracle, the external connection between two or more tables is represented by (+.

Code Demo: Check the name, department, work, and salary of the employee whose salary is greater than 2000 yuan.
Because the Department name is in dept and other information is in the emp table, internal join is required.

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 the SQL/92 standard internal join:

Code Demonstration: inner join

SELECT e.ENAME,e.JOB,e.SAL,d.DNAME FROM EMP e INNER JOIN DEPT d ON e.DEPTNO=d.DEPTNOWHERE e.SAL>2000;

In inner join, the keyword INNER can be omitted.

Code Demo: query the employee name and salary in each department. The Emp table uses the foreign key deptno to reference deptno In the Dept table,
If some departments do not have employees in the Dept table, internal join is used. departments without employees cannot be displayed. Therefore, external join Based on the Dept table must be used.

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:
(+): An Oracle-specific join operator that appears on the left to the right to the Outer Join and on the right to the left to the outer join.

You can also use the standard SQL/92 statement:
Code Demonstration: External join

SELECT e.ENAME,e.JOB,e.SAL,d.DNAME FROM EMP e RIGHT OUTER JOIN DEPT d ON e.DEPTNO=d.DEPTNO;

In right outer join, the keyword OUTER can be omitted.