SQL Inner Join, Left Join, Right Join returns same results. Why this is showing result table like below..?

Question

Here, I have tried to perform inner join, left join, right join on table 1 & table 2..but all joins return the same output. What is the specific reason behind this...?

sample SQL query for ref:

select column1 
    from table1
    left join table1
    on table1.column1 == table2.column1

Answer 1

When both tables have the same set of joined values -- (A, B, C, D) in this case -- then there is no difference between an inner and outer join.

Such joins can only give different results when these sets are not the same. For instance, if you would delete the last two records from Table 2, then the inner join will not produce a record with D, while an outer join could still produce it:

SELECT    table1.column
FROM      table1 
LEFT JOIN table2 ON table1.column = table2.column

Answer 2

The INNER JOIN selects records that have matching values in both tables. (So in this case whatever data is present in Table1 is also present in table 2 for ex. "A" is present twice in tbl2 so in end result table its 2 times i.e for 1 "A" there is 2 values is been returned from tbl2)

The LEFT JOIN returns all records from the left table (table1), and the matching records from the right table (table2).The result is 0 records from the right side, if there is no match. (So again "A" is having 2 values in tbl2 so for 1 "A" there is 2 values is been returned from tbl2)

The RIGHT JOIN returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match. (So again in table 2 there is 2 "A" and for each 1 "A" 1 values from left table(tbl1) is returned).

Data set is having similar values, that's why you are getting similar result. Try modifying the data set you will observe the difference