Difference between an INNER JOIN and an OUTER JOIN in SQL
JOINS are the most commonly used commands that we come across in any SQL query or script. There are different types of JOINS defined in SQL to get varied outputs. The purpose of joining tables is to understand the relationship between the data present in the tables and how they change based on the conditional parameters used to join them.
In this post we would be talking about two specific JOIN, INNER and OUTER joins and some scenarios where they are used.
Inner Join – This join command is used when we want records which are common in both the tables. This is a default JOIN used on SQL. For example – If we have two tables, one containing car details and other containing the customer (both prospective and present) details, and we want to get the number of cars that are sold, we can inner join command (assuming we have a common key to join the tables).
Outer Join can be categorized into multiple sub-types –
Left Outer Join – This command is used when we want to list out all the records from one table along with the matched records from second table. Taking the above example, if we want to find the list of cars that are yet to be sold we can Left Outer Join the cars table with customer table. The owner column will be populated as ‘NULL’ for cars that are yet to be sold.
Right Outer Join – This command is the exact opposite of Left Outer Join. If we change our question saying that we want the list of prospective customers we can right join the cars and customer table to obtain that list.
Full Outer Join – This is a union of Left and Right Outer Joins. This command will list out all the records starting from present to prospective customers and cars that are yet to be sold and that are sold.
More often i use the Inner Join and Left Outer Join commands in my day to day work. But depending upon the scenario any these commands can be leveraged to generate the desired result.
By: Priyesh Upadhyay