CODEWITHSUNDEEP
sqlsql-serverjoininner-join
Ravi
Ravi

Reputation: 31397

INNER JOIN works as CROSS JOIN

I have very simple scenario

ID |NAME | DEPT_ID
1  |R K  | 1
2  |V K  | 1

DEPT_ID | LOC
1       | KA 
1       | VA

Expected output

 ID |NAME | DEPT_ID |LOC
 1  |R K  | 1       |KA
 2  |V K  | 1       |VA

But,getting

ID |NAME | DEPT_ID |LOC
1  |R K  | 1       |KA
2  |V K  | 1       |VA
1  |R K  | 1       |VA
2  |V K  | 1       |KA

I have used simple INNER JOIN. I can get my expected output ?

select ID,NAME,LOC 
from table1 
    inner join table2 on table1.dept_id=table2.dept_id

Upvotes: 2

Views: 84

Answers (1)

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26699

First record has dept_id of 1. There are 2 matching records for this dept_id

Second record has dept_id of 1. There are 2 matching records for this dept_id

So total 4 records in the result. It works as expected.

There is no way the server could know that KA matches R K and not V K

Conclusion: don't use non-unique field for the join.

Upvotes: 9

Related Questions