-1

Till now I was under the impression that when we "join" two tables mainly by "inner join" the condition we test is for equality, for example:

select sales.sales_date, sales.order_id, sales.product_id, product.product_name  from sales INNER JOIN product ON sales.PRODUCT_ID = product.PRODUCT_ID;

And have seen some venn diagrams which explains it, like here

In "inner join" do we always test for equality for condition?

I tried this query and I am able to see some output as well.

select sales.sales_date, sales.order_id, sales.product_id, product.product_name  from sales INNER JOIN product ON sales.PRODUCT_ID != product.PRODUCT_ID;

Based on this I am bit confused what does "inner join" really mean.

Can anyone help me understand this?

Community
  • 1
  • 1
CuriousMind
  • 8,301
  • 22
  • 65
  • 134
  • Possible duplicate of [INNER JOIN ON vs WHERE clause](https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) – philipxy Oct 13 '19 at 00:02

3 Answers3

2

This is a SQL question, not specific to Oracle. A join that uses an equality comparison in the ON condition is called an equi join. A join that uses inequality (<=, >, BETWEEN, != etc.) is called a non equi join. Obviously, since they even have a technical name, non equi joins do exist. Google for "non equi join" and you will likely find many examples.

Join conditions may also be non-arithmetic; for example you may join tables A and B on A.string LIKE B.init_fragment || '%' - you are looking for all strings in the string column in A that begin with a value in the init_fragment column in B.

  • Thanks for the explanation, however does inner join usually mean to test for equality? Most of the materials which I have referred (even in various SO question - answers) they are mentioning that Inner joins means those set of rows where two tables match for a condition based on some common field (like product_id in above case). I am bit confused on this now :( – CuriousMind May 01 '17 at 09:47
  • 1
    @CuriousMind - Usually, yes. But there are many, many cases when a join is **not** based on equality. It is best not to think that "join means equality" even though it "often" or "usually" means that. If you think that "join means equality" from the outset, you will be confused when you see non equi joins. –  May 01 '17 at 12:10
  • Thanks for the info, but in most of the materials which I have referred they are saying in INNER JOIN we test for equality and only matching records come in two tables; now if INNER JOIN can have any other clause (other than equality), what meaning of that would be? Can you suggest some material which is definitive? Online can find so much and not sure which is genuine, text books say INNER JOIN means matching records, so wondering where to refer to real information. – CuriousMind May 01 '17 at 14:33
1

In practice, you would most often express foreign/primary key relations as conditions in a join ... on clause, but there is no rule that requires this.

The syntax allows you to put any condition in the join ... on clause that you could have put in the where clause, as long as the referenced tables are in scope (i.e. tables already joined at that point).

A condition in the join product on clause would normally say something about how the product records need to be joined to the previously mentioned table(s) (prices in your case). In a broader sense, it tells the engine which record combinations can be eliminated from the result, even before the remaining tables are joined (if any). It is of course up to the SQL database engine to perform optimisations and decide when exactly to apply the condition.

trincot
  • 317,000
  • 35
  • 244
  • 286
0

No, inner joins can have non equi join conditions. There are basically 2 components in any join, one is the join type (can be inner, left outer, right outer, full outer etc..) and the other is how join is performed (can be equi join and non equi join). You can mix and match between these 2 components to have different types of join like inner equi join, inner non equi join etc..

hope this helps

ravi malhotra
  • 703
  • 5
  • 14