An equi join deals with common columns having same data in both the tables and the join is compared with an equality operator. But there are cases where tables may not have a common column, but a join is required.

Let us see an example.

SELECT e.Ename, e.Job, e.Sal, e.Deptno, s.Grade
  FROM Emp e, SalGrade s
 WHERE e.Sal BETWEEN s.LoSal AND s.HiSal 
   AND e.Sal > 2500;

ENAME      JOB              SAL     DEPTNO      GRADE
---------- --------- ---------- ---------- ----------
BLAKE      MANAGER         2850         30          4
JONES      MANAGER         2975         20          4
SCOTT      ANALYST         3000         20          4
FORD       ANALYST         3000         20          4
KING       PRESIDENT       5000         10          5

Hence, a non-equi join is join where the condition use any operator other than equal to sign.

Non-equi joins are also very beneficial with Self joins where there will be equi join condition along with some other level of condition checking with non-qual to conditions.

Q : Find the employees name, salary, designation and their manager’s name, salary and designation for all employees who are earning more than their managers?

Ans:

SELECT
    e.Ename  "Employees",
    e.Sal  "Emp Salary",
    e.Job  "Emp Job",
    m.Ename   "Managers",
    m.Sal  "Mgr Salary",
    m.Job  "Mgr Job"
  FROM Emp e, Emp m
 WHERE m.Empno = e.Mgr
   AND e.Sal > m.Sal;

Employees  Emp Salary Emp Job   Managers   Mgr Salary Mgr Job
---------- ---------- --------- ---------- ---------- ---------
FORD             3000 ANALYST   JONES            2975 MANAGER
SCOTT            3000 ANALYST   JONES            2975 MANAGER

Got through our next chapter to understand all about Self joins.