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

Rohan Rai 1 Reputation point
2021-10-08T08:45:09.537+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,477 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Olaf Helper 40,156 Reputation points
    2021-10-08T08:58:28.707+00:00

    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?

    0 comments No comments

  2. YufeiShao-msft 7,046 Reputation points
    2021-10-08T09:20:59.99+00:00

    Hi @Rohan Rai ,

    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.


  3. Tom Phillips 17,716 Reputation points
    2021-10-08T18:29:48.69+00:00
    0 comments No comments