Equi join

An equi join is an inner join statement that uses an equivalence operation (i.e: colA = colB) to match rows from different tables. The converse of an equi join is a nonequi join operation.

Examples

Using SCOTT's table 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

SELECT *
FROM  emp, dept
WHERE emp.deptno = dept.deptno
  AND job = 'CLERK';

Using ANSI join syntax:

SCOTT> SELECT ename, dname
  2    FROM emp INNER JOIN dept
  3         ON emp.deptno = dept.deptno;

ENAME      DNAME
---------- --------------
CLARK      ACCOUNTING
KING       ACCOUNTING
MILLER     ACCOUNTING
JONES      RESEARCH
FORD       RESEARCH
ADAMS      RESEARCH
SMITH      RESEARCH
SCOTT      RESEARCH
WARD       SALES
TURNER     SALES
ALLEN      SALES
JAMES      SALES
BLAKE      SALES
MARTIN     SALES

Also see

• Cartesian join
• Inner join
• Natural join
• Nonequi join
• Outer join
• Self join

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	#