question

RohanRai-7950 avatar image
0 Votes"
RohanRai-7950 asked Yufeishao-msft commented

What Is the Difference Between “Inner Join” and “Outer Join”?

I have been reading this article on Joins in sql, I am still confused about the difference between Inner Join and Outer Join and how do Left join, right join and full join fit in?


sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Please do not forget to mark as accepted answer wherever information provided to you were helpful.

Regards

0 Votes 0 ·

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

how do Left join, right join and full join fit in?

LEFT/RIGHT are boht OUTER joins.
The article is well written, so what in detail confuse you?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered Yufeishao-msft commented

Hi @RohanRai-7950,

When the inner join is used, it considers only those attributes that we want to match both the table and, if anything that doesn’t, wouldn’t be included in our result table.
In the outer join, we consider any of the tables completely or both such that the remaining fields that were unmatched in both the tables were kept NULL.

Key Differences Between Inner Join and Outer Join:
The basic difference between the Inner Join and Outer Join is that inner join compares and combine only the matching tuples from both the tables. On the other hands, the Outer Join compare and combines all the tuples from both the tables being compared.
The database size of the resultant obtained from the Inner Join is smaller that Outer Join.
There are three types of the Outer Join Left Outer Join, Righ Outer Join, and Full Outer Join. But inner Join has no such types.

In left join, we consider the left table completely and the matched attributes (based on condition) in the right table along with, the unmatched attributes of the left table with the right table are placed NULL with respect to the column in the left table.
In right join is the opposite
Full Join is the union of both left join and right join where all the columns of the left table and the right table are considered where the unmatched or unfound attributes of the left table or right table will be placed with NULL in the resultant table.

Key Differences Between Left, Right and Full Outer Join
The result of Left Outer Join has all the tuples of left table. Similarly, the result of Right Outer Join has all the tuples of the right table. And the result of Full Outer Join has all the tuples from both the left and the right table.
In Left Outer Join, tuples of left table that does not have a matching tuple in right table are extended with Null values for the attributes of the right table. Opposite is the case for Right Outer Join. And in Full Outer Join, the tuples from left and right tables that do not have matching tuples in the right and left tables respectively are extended with NULL for the attributes of right and left table respectively.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.