0

I am new to Joins and cannot understand the difference between and mathematical working behind inner join, natural join and equi join. Is it always a Cartesian product which is made into a smaller result using predicates?

Could someone please elaborate the underlying working of these three joins with examples?

floralmural
  • 49
  • 7
  • Possible duplicate of [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) Have a look at all the answers in this question, you will find what you are looking for – Sudipta Mondal Jun 21 '18 at 05:11
  • @SudiptaMondal That's a completely different question. – Amadan Jun 21 '18 at 05:12
  • @Amadan - A lot of answers in that question have explanations on Venn diagrams – Sudipta Mondal Jun 21 '18 at 05:13
  • They do. Explanations of differences between inner and outer. Not of inner, natural and equi. I searched the entire question, equi joins were mentioned but only defined once, incorrectly. – Amadan Jun 21 '18 at 05:13
  • @Amadan oh yes, it doesn't have for Natural join, it has only for inner and equi – Sudipta Mondal Jun 21 '18 at 05:20
  • @floralmural - the answer in the first comment and this one https://stackoverflow.com/questions/8696383/difference-between-natural-join-and-inner-join I wonder if there is even any mathematical explanation behind natural join – Sudipta Mondal Jun 21 '18 at 05:22
  • When they say "In the inner join condition, you can also use other operators like <,>,<>." what does that mean? And how is this going to work? Why and when is a join used with a <,> or <> condition? – floralmural Jun 21 '18 at 05:22
  • @SudiptaMondal How does natural join work exactly then? It just checks common columns and joins accordingly? – floralmural Jun 21 '18 at 05:23
  • @floralmural yes, I don't see much utility of natural joins TBH, most production tables would have something like columns like `creation_time` which would fail, if we used natural joins. but yes it just checks the common columns and joins accordingly – Sudipta Mondal Jun 21 '18 at 05:25

1 Answers1

3

Inner join of A and B combines columns of a row from A and a row from B based on a join predicate. For example, a "sempai" join: SELECT ... FROM people A INNER JOIN people B ON A.age > B.age will pair each person with each person that is their junior; the juniormost people will not be selected from A, and seniormost people will not be selected from B, because there are no matching rows.

Equi join is a particular join where the join relation is equality. A "sempai" join from the last paragraph is not an equi join; but "same age" join would be. Though typically it would be used for foreign relationships (equi joins on primary keys), such as SELECT ... FROM person A INNER JOIN bicycle B ON A.bicycle_id = B.id. (Pay no attention to the fact that this is not a proper model, people sometimes have multiple bicycles... a bit of a silly example, I'm sure I could have found a better one.)

A natural join is a special kind of equi join that assumes equality of all shared columns (without explicitly stating the predicate). So for example SELECT ... FROM people A INNER JOIN bicycles B ON A.bicycle_id = B.bicycle_id is equivalent to SELECT ... FROM people A NATURAL JOIN bicycles B, assuming bicycle_id is the only column present in both tables. Most people I know will not use this, because of several reasons - it is a more common practice to have the primary key not repeat the table name, i.e. bicycles.id than bicycles.bicycles_id; it is possible the foreign key does not reflect the table name (e.g. person.overseer_id rather than person.person_id, for obvious reasons), and (forgotten my me but thankfully remembered by Sudipta Mondal) there might be unrelated columns that are named the same but make zero sense to join on, like creation_time. For these reasons, I have never used NATURAL JOIN in my life.

Equi/natural joins do not necessarily have to be inner.

Amadan
  • 191,408
  • 23
  • 240
  • 301