Can anyone tell me, how inner join works in SQL?

Question

I have a table A and B, both has one column and two rows as follows,

A  B
-------
C1 C2
1  1
1  1
1  0
0  0

If I apply a inner join on this and the it is returning 8 rows as a results,

Select C1,C2 from A inner join B on A.C1=B.C2;

Result
---------
C1 C2
1 1
1 1
1 1
1 1
1 1
1 1
0 0
0 0

And I am guessing that, first row of the C1 column is checking with the data of all C2 columns. If it's matches, its returning result otherwise, it won't. The same method was following for the rest of the columns. Correct me, if my understanding was wrong and please assist with the answer for the below question;

I have two tables A,B both are having two columns. What will be the result if we apply inner join, please explain me with functionality.

  A        B
----–-----------
C1 C2     C3 C4
1   1      1  1
1   1      1  0

Select C1,C2,C3,C4 from A inner join B on A.C1=B.C3;

It's returning 4 rows, please explain how?

You've replaced `rows` with `columns`, but basically you're correct. For every row in table a, join the rows in table b that match the join condition. — Zohar Peled, Sep 06 '20 at 11:46
The inner join will match rows from both tables according to any **predicate** you specify. In your first example you used the predicate `A.C1 = B.C2`. Now, what predicate do you want to use in the second case? — The Impaler, Sep 06 '20 at 12:06
No can do: in your second example `B` does not have a column named `C2`. — The Impaler, Sep 06 '20 at 12:12
See this great answer from Martin Smith: https://stackoverflow.com/a/27458534/2527905 — dnoeth, Sep 06 '20 at 16:36

Ankit Bajpai · Answer 1 · 2020-09-06T12:27:47.677

This is common misconception about inner joins. Concept of inner join says a value in a column of a table will match with each and every occurrence of same value of joining column in another table.

In your example, in table A First row of 1 of column C1 will match with all 2 rows of value 1 of column C2 of table B, Second 1 will match with all 2 1's then 3rd 1 will match with all 2 of able B. Then a 0 will match 2 times in table B.

Thus they becomes - 2(1's) + 2(1's) + 2(1's) + 2(0's) = 8 rows.

Same concept applies to your second example as well. Since you have 2 columns in your 2nd example, So you have to decide the join predicate here.

If you decided to join like `A.C1 = B.C3` then 4 rows will occur in result.
If you decided to join like `A.C1 = B.C4` then 2 rows will occur in result.
If you decided to join like `A.C2 = B.C3` then 4 rows will occur in result.
If you decided to join like `A.C2 = B.C4` then 2 rows will occur in result.

Okay, what will be result for the second predicate? – Velmurugan Saravanan Sep 06 '20 at 12:16 — Velmurugan Saravanan, Sep 06 '20 at 12:16

The Impaler · Answer 2 · 2020-09-06T12:28:59.837

In your example, if you use the predicate A.C1 = B.C3 the result is:

c1  c2  c3  c4 
--- --- --- -- 
1   1   1   1  
1   1   1   1  
1   1   1   0  
1   1   1   0

See running example at DB Fiddle.

Now, as a general rule, the inner join will match rows from both tables according to any predicate you specify, not necessarily simple column values.

For example:

A           B
--------    --------
C1 C2  #    C3 C4  #
1   1  A1   1  1   B1
1   1  A2   1  0   B2
            0  1   B3

If the predicate is a.c1 * a.c2 = b.c3 + b.c4, as in the query:

select
  a.*,
  b.*
from a
join b on a.c1 * a.c2 = b.c3 + b.c4

The result is:

c1  c2  c3  c4   matching predicate
--- --- --- --   --------------------------
1   1   1   0    1 * 1 = 1 + 0  (A1 and B2)
1   1   1   0    1 * 1 = 1 + 0  (A2 and B2)
1   1   0   1    1 * 1 = 0 + 1  (A1 and B3)
1   1   0   1    1 * 1 = 0 + 1  (A2 and B3)

Do you see how the rows are matched?

Can anyone tell me, how inner join works in SQL?

2 Answers2