• Networking
  • Programming
  • DBMS
  • Operating System
  • Internet
  • Hardware
  • Software

Tech Differences

Know the Technical Differences

Difference Between Inner Join and Outer Join in SQL

inner-join-vs-outer-joinInner Join and Outer Join both are the types of Join. Join compares and combines tuples from two relations or tables. Inner Join specifies the natural join i.e. if you write a Join clause without Inner keyword then it performs the natural join operation. The potential difference between Inner Join and Outer Join is that Inner Join returns only the matching tuples from both the table and the Outer Join returns all the tuples from both the compared tables.

Let us discuss some other differences between Inner Join and Outer Join with the help of the comparison chart shown below.

Content: Inner Join Vs Outer Join

  1. Comparison Chart
  2. Definition
  3. Key Differences
  4. Conclusion

Comparison Chart

Basis for ComparisonInner JoinOuter Join
BasicInner Join outputs only the matching tuples from both the table.Outer Join displays all the tuples from both the tables.
DatabaseThe Potential size of the database returned by Inner Join is comparatively smaller than Outer Join.Outer join return comparatively larger database.
TypesNo types.Left Outer Join,
Right Outer Join,
and Full Outer Join.

Definition of Inner Join

Inner Join is also referred to as Natural Join. Inner Join compares two tables and combines the matching tuple in both the tables. It is also called as the default type of join, as Join clause is written without the inner keyword it perform the natural join. If the Join clause is written without Outer keyword then also inner join is performed.

Inner Join can be explained with an example. There are two tables student table and department table. Now lets us understand what does inner Join performs.

dataSELECT Name, Sem, Deparment_name FROM Student INNER JOIN Department ON Student.Department_ID= Department.ID.result-of-inner-joinYou can see that only those tuples are obtained in the resultant where Student.Department_ID= Department.ID. Hence, we can say that Inner Join combines only the matching tuple of two table.

Definition of Outer Join

Unlike in Inner Join, only those tuples are output that has same attribute values in both the compared table; Outer Join outputs all the tuples of both the table. Outer Join is of three types Left Outer Join, Right Outer Join, and Full Outer Join.

Let us understand them one by one. First, let us take Left Outer Join.

Select Name, Department_name From Student Left Outer Join Department ON Student.Department_ID=Depoartment.ID.result-of-left-outer-joinYou can see that all the tuples from Student Table are displayed in the result.

Select Name, Department_name From Department Right Outer Join Student ON Student.Department_ID=Depoartment.ID.result-of-right-outer-joinYou can see that all the tuples from Department table are displayed.

Select Name, Department_name From Student Full Outer Join Department ON Student.Department_ID=Depoartment.ID.result-of-right-outer-joinYou can observe that all the tuples from both the tables are displayed in the result.

Key Differences Between Inner Join and Outer Join

  1. 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.
  2. The database size of the resultant obtained from the Inner Join is smaller that Outer Join.
  3. 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.

Conclusion

Both the Joins are very useful. Ther use depends on the requirement of the user.

Related Differences:

  1. Difference Between Left, Right and Full Outer Join
  2. Difference Between JOIN and UNION in SQL
  3. Difference Between Group By and Order By in SQL
  4. Difference Between DELETE and TRUNCATE in SQL
  5. Difference Between Fact Table and Dimension Table

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Top 10 Differences

  • Difference Between OLTP and OLAP
  • Difference Between while and do-while Loop
  • Difference Between Guided and Unguided Media
  • Difference Between Preemptive and Non-Preemptive Scheduling in OS
  • Difference Between LAN, MAN and WAN
  • Difference Between if-else and switch
  • Difference Between dispose() and finalize() in C#
  • Difference Between for and while loop
  • Difference Between View and Materialized View
  • Difference Between Server-side Scripting and Client-side Scripting

Recent Addition

  • Difference Between Unit Testing and Integration Testing
  • 4G Vs 5G
  • Raster Vs Vector Images
  • JPEG Vs TIFF
  • RJ11 Vs RJ12

Categories

  • DBMS
  • Hardware
  • Internet
  • Networking
  • Operating System
  • Programming
  • Software

Copyright © 2023 · Tech Differences · Contact Us · About Us · Privacy