Science behind Inner and Outer Join in SQL

Science behind Inner and Outer Join in SQL

May 17, 2019 DATAcated Challenge 0

Image result for sql

SQL – Structured query language is one among the most easily picked up RDBMS languages and widely used across industries to store and retrieve data for transactional and reporting purposes. When moderately large amount of data needs to be handled SQL comes into picture and is considered most efficient, manageable. DML is the way of operating the data for user purposes and select statement with ‘Joins’ come in handy when data needs to be viewed from multiple tables.

Two most commonly used joins are Inner join and Outer join.

  • Inner Join – When trying to get rows between two tables that are joined on a common column (Foreign key relationship) rows that match the join condition are returned from both the tables and non matching rows are dropped.

  • Outer Join– In a join between two tables with an outer join data from both/just left or right are returned depending on the type of outer join.  There are three different types of outer join namely, Left outer join, Right outer join and Full outer join.
    1. Left outer join – This join returns all rows from left table and only rows that are true to the join condition and ‘Null’ when no matching rows exist on the right.

2. Right outer join – returns all rows from right table and rows that satisfy the join condition and ‘Null’ when no matching rows exist on the left.

enter image description here

3. Full outer join– returns rows from from both the rows that match and ‘Null’ when no matching rows exist on both the tables.

enter image description here

To wrap up inner join could be considered a subset of outer join and the only difference being outer join allows a user to get non matching rows in addition to the matched rows and substitute ‘NULL’ as and when applicable.

By: Avanthika Sankararaman


Leave a Reply

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