Inner join

From Oracle FAQ
Jump to: navigation, search

An inner join (aka simple join) is a join that returns rows of the tables that satisfy the join condition.

[edit] Examples

Using usual SCOTT's tables and Oracle join syntax:

SCOTT> SELECT ename, job, dept.deptno, dname
  2    FROM   emp, dept
  3    WHERE emp.deptno = dept.deptno;

ENAME      JOB           DEPTNO DNAME
---------- --------- ---------- --------------
CLARK      MANAGER           10 ACCOUNTING
KING       PRESIDENT         10 ACCOUNTING
MILLER     CLERK             10 ACCOUNTING
JONES      MANAGER           20 RESEARCH
FORD       ANALYST           20 RESEARCH
ADAMS      CLERK             20 RESEARCH
SMITH      CLERK             20 RESEARCH
SCOTT      ANALYST           20 RESEARCH
WARD       SALESMAN          30 SALES
TURNER     SALESMAN          30 SALES
ALLEN      SALESMAN          30 SALES
JAMES      CLERK             30 SALES
BLAKE      MANAGER           30 SALES
MARTIN     SALESMAN          30 SALES

Using ANSI join syntax:

SCOTT> SELECT ename, sal, grade
  2    FROM emp JOIN salgrade 
  3         ON emp.sal BETWEEN salgrade.losal AND salgrade.hisal;

ENAME             SAL      GRADE
---------- ---------- ----------
SMITH             800          1
JAMES             950          1
ADAMS            1100          1
WARD             1250          2
MARTIN           1250          2
MILLER           1300          2
TURNER           1500          3
ALLEN            1600          3
CLARK            2450          4
BLAKE            2850          4
JONES            2975          4
SCOTT            3000          4
FORD             3000          4
KING             5000          5

[edit] Also see

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #