Basic Differences Between SQL Inner Join and Outer Join

1. Introduction

In this article, let’s discuss the difference between SQL Inner Join and Outer Join.

2. Joins

A Join clause helps the user to combine values from two or more tables based on a correlation between them. The relation between them is typically lying on the columns of Table A and Table B that have matching values.

2.1. Uses of Joins

In a relational database, data is distributed in many related tables. Join Clause helps the programmer to obtain the data from more than 1 table by using the values common to each. It pieces the data together and makes it useful and understandable. In most cases, it matches the column value of one table with another table.

3. Inner Join

Inner Join is also called as EQUIJOIN. This matches the left table to the right table. It retrieves the data by combining the values of two tables. This Join returns the rows from table 1 that matches with the rows from table 2. The column values of matched rows of the two tables are combined into a result row.

SELECT     table1.column1, 
           table2.column2... 
FROM       table1 
INNER JOIN table2 
ON         table1.common_field = table2.common_field;

4. Outer Join

An Outer Join returns a set of records that includes what an Inner Join would but also includes other rows for which no match is found.

4.1. Full Join

Full Outer Join or Full Join. This Join not only return the rows that are matched, but also the rows which are mismatched. These mismatched sides will have NULLS.

SELECT    table1.column1, 
          table2.column2... 
FROM      table1 
FULL JOIN table2 
ON        table1.common_field = table2.common_field;

4.2. Left Outer Join

A Left Outer Join will return all the data in Table 1 and also the shared data that the Table 2 had in common.

SELECT table1.column1, 
       table2.column2, 
FROM   table1 
LEFT OUTER JOIN table2 
ON table1.common_field = table1.common_field2; 

4.3. Right Outer Join

A Right Outer Join is a reverse form of Left Outer Join where it returns all the data in Table 2 and also the data Table 1 had in common.

SELECT table1.column1, 
       table2.column2, 
FROM   table1 
RIGHT OUTER JOIN table2 
ON table1.common_field = table1.common_field2; 

5. Differences Between Sql Inner Join and Outer Join

6. Conclusion

To summarize, we have the differences between SQL Inner Join and Outer Join.