Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. 4 Joins in Oracle
  5. 4.4 Equi Join

4.4 Equi Join

An equi join is a join where the join condition contains an equality operator. An equi join combines rows that have equivalent values on both tables for the specified columns.

SELECT Ename, Sal, Dname, Loc, Dept.Deptno
  FROM Emp, Dept
 WHERE Emp.Deptno = Dept.Deptno;

ENAME             SAL DNAME          LOC               DEPTNO                   
---------- ---------- -------------- ------------- ----------                   
SMITH             800 RESEARCH       DALLAS                20                   
ALLEN            1600 SALES          CHICAGO               30                   
WARD             1250 SALES          CHICAGO               30                   
JONES            2975 RESEARCH       DALLAS                20                   
MARTIN           1250 SALES          CHICAGO               30                   
BLAKE            2850 SALES          CHICAGO               30                   
CLARK            2450 ACCOUNTING     NEW YORK              10                   
SCOTT            3000 RESEARCH       DALLAS                20                   
KING             5000 ACCOUNTING     NEW YORK              10                   
TURNER           1500 SALES          CHICAGO               30                   
ADAMS            1100 RESEARCH       DALLAS                20                   
JAMES             950 SALES          CHICAGO               30                   
FORD             3000 RESEARCH       DALLAS                20                   
MILLER           1300 ACCOUNTING     NEW YORK              10

14 rows selected.

The common column names in the WHERE clause should be qualified with the table name to avoid ambiguity. If there are no common column names between two tables, the qualifier is not necessary, but it is a good coding style to qualify column names with table name or table alias.

You can always implement other conditions with JOIN condition. There is no restriction in the number of conditions that can be implemented with a Join condition.

SELECT e.Ename, e.Sal, e.Job, d.Dname, d.Loc, d.Deptno
  FROM Emp e, Dept d
 WHERE e.Deptno = d.Deptno
   AND e.Job IN('CLERK', 'ANALYST', 'MANAGER') 
   AND e.Sal BETWEEN 1500 AND 3000;

ENAME             SAL DNAME          LOC               DEPTNO            
---------- ---------- -------------- ------------- ----------             
JONES            2975 RESEARCH       DALLAS                20
BLAKE            2850 SALES          CHICAGO               30
CLARK            2450 ACCOUNTING     NEW YORK              10
SCOTT            3000 RESEARCH       DALLAS                20
FORD             3000 RESEARCH       DALLAS                20
SELECT E.Ename, E.Job, E.Sal, E.Sal + NVL(E.Comm, 0) TotalSal, D.Deptno, D.Dname
  FROM Emp E, Dept D
 WHERE E.Deptno = D.Deptno AND E.Job = UPPER('manager');

ENAME      JOB              SAL   TOTALSAL     DEPTNO DNAME
---------- --------- ---------- ---------- ---------- --------------
JONES      MANAGER         2975       2975         20 RESEARCH
CLARK      MANAGER         2450       2450         10 ACCOUNTING
BLAKE      MANAGER         2850       2850         30 SALES
Was this article helpful to you? Yes No

How can we help?