0

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?

Mohit Saxena
  • 89
  • 1
  • 12
  • "definitions i've read on internet" Time to follow a published academic textbook on information modelling, the relational model & DB design (including "normalization to higher NFs"). (Manuals for languages & tools to record & use designs are not such textbooks.) (Nor are wiki articles or web posts.) – philipxy Mar 02 '20 at 20:27
  • Equijoin is a RA (relational algebra) operator & details depend on a particular definition in a particular version of RA. So you need to tell us what you mean by it. "non-equi join" is not standard. Where did you see these terms? Tell us why you think things are or are not the same or where you are stuck not knowing. Don't just ask for yet more presentations. SQL INNER JOIN ON is on any condition, so it's unclear what you mean by "can have other operators" or even "have an operator". See [ask], other [help] links, hits googling 'stackexchange homework' & the voting arrow mouseover texts. – philipxy Mar 02 '20 at 20:33

1 Answers1

3

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.

David Ferenczy Rogožan
  • 23,966
  • 9
  • 79
  • 68
  • i know equi join and non-equi join are not same. but what about inner join and non-equi join? – Mohit Saxena Mar 02 '20 at 19:57
  • As I wrote in the first sentence, they're not related. You can have both equi- and non-equi `INNER JOIN`. Check the diagram and read those articles I have linked. – David Ferenczy Rogožan Mar 02 '20 at 20:01
  • @MohitSaxena Did my answer help you to understand those terms? If yes, please consider accepting my answer, it'll help others to find the relevant information and it also rewards my effort with some reputation points. – David Ferenczy Rogožan Mar 04 '20 at 23:45