CODEWITHSUNDEEP
databaseinner-joinjoinnon-equi-join
Mohit Saxena
Mohit Saxena

Reputation: 189

What is the difference between inner join and non-equi join?

From definitions i've read on internet, in equi join the join condition is equality (=) while inner join can have other operators such as less than (<) or greater than (>) as well.

a non-equi join is a type of join whose join condition uses conditional operators other than equals.

Does that mean non-equi joins and inner joins are same?

Upvotes: 0

Views: 2681

Answers (1)

David Ferenczy Rogožan
David Ferenczy Rogožan

Reputation: 25411

Those are two different things, "equi-" and "non-equi" joins are independent of the logical join type.

  • "Equi-join" is when all columns in ON clause are matched on equality, for example ON t1.c1 = t2.c1 AND t1.c2 = t2.c2.

  • "Non-equi-join" is when one or more columns are using an inequality comparison (e.g. < less than, > more than, <> not equal etc.), for example ON t1.c1 = t2.c1 AND t1.c2 > t2.c2.

Both "equi-join" and "non-equi-join" may be used together with any logical join, e.g. INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOIN, etc.

I found the following diagram from Complex SQL.com site to be the easiest way to understand it:

equi- non-equi-join diagram

You can read further details for example in An Illustrated Guide to the SQL Non Equi Join or SQL Joins on Beginner SQL Tutorial.

Upvotes: 3

Related Questions