0

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

enter image description here

C.Champagne
  • 5,381
  • 2
  • 23
  • 35
Shrikant U.
  • 247
  • 2
  • 5
  • 2
    Can you provide the SQL statement that you are asking about? – trincot Mar 25 '22 at 06:48
  • 1
    What is the specific reason behind you thinking this should be different? If you join on column and there is no value that exist in only one of the tables, then why would you think that an outer join gives you another result as an inner join? – Thorsten Kettner Mar 25 '22 at 06:50
  • Add E to table1, and F to table2, and you'll see the difference. – jarlh Mar 25 '22 at 07:08
  • [What is the difference between “INNER JOIN” and “OUTER JOIN”?](https://stackoverflow.com/a/46091641/3404097) – philipxy Mar 25 '22 at 07:10
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. Please in code questions give a [mre]. [ask] [Help] – philipxy Mar 25 '22 at 07:12
  • Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) – philipxy Mar 25 '22 at 07:14
  • "specific reason" Because that is how the language is defined. If you want to know where you went wrong (& right), at the 1st point you get an unexpected value say what you expected & why, justified by authoritative documentation. Right now you're asking for yet another presentation of the language giving no detail of your misconceptions/mistakes. [ask] [Help] PS A [mre] includes cut & paste & runnable code & example input; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. – philipxy Mar 25 '22 at 23:41
  • Start by reading how inner join & outer join work in a published introductory textbook & my linked answer. Then look at select * from x cross join y, then select * from x inner join y on c, then select * from x outer join y on c. (Use "minimal" representative input.) "say what you expected & why, justified by authoritative documentation". PS The accepted answer at the Q&A of my linked answer is poor (like almost every other answer there). Read published textbooks. – philipxy Mar 25 '22 at 23:49

2 Answers2

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
trincot
  • 317,000
  • 35
  • 244
  • 286
1

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

DB08
  • 151
  • 6
  • This is unclear. (Typical of descriptions of joins.) "returns all records from the left table" No. A join result has no rows that are in either input. You might be thinking that parts of output rows look like input rows. You might be thinking of the result after selecting only some columns of a join result. But that's not what you are saying. – philipxy Mar 25 '22 at 07:19