What is The Difference Between Inner and Outer Join?


Sql is the language where a lot of data can be get with different logic. In an enterprise environment we may need to use complex SQL queries to get meaningful data. Inner is one of the logic where we join two tables with different magic.

Example Tables

In order to understand Inner and Outer Joins we will use following example tables with their data. We will use two tables those named Personels and Persons

MariaDB [test]> SELECT LastName FROM Personels; 
+----------+ 
| LastName | 
+----------+ 
| C        | 
| D        | 
| E        | 
+----------+ 
3 rows in set (0.00 sec)
MariaDB [test]> SELECT LastName FROM Persons; 
+----------+ 
| LastName | 
+----------+ 
| A        | 
| B        | 
| C        | 
| NULL     | 
+----------+ 
4 rows in set (0.00 sec)

Inner Join

If we inner join Persons with Personels we will get only intersection of the given fields. In this example we want to get the LastName column intersection in two tables Persons and Personels .

> SELECT Persons.LastName FROM Persons INNER JOIN Personels on Persons.LastName = Personels.LastName;           
+----------+ 
| LastName | 
+----------+ 
| C        | 
+----------+ 
1 row in set (0.00 sec)

Right Outer Join

The right outer join will return all entries in left table and

> SELECT Persons.LastName FROM Persons RIGHT OUTER JOIN Personels on Persons.LastName = Personels.LastName;       
+----------+ 
| LastName | 
+----------+ 
| C        | 
| NULL     | 
| NULL     | 
+----------+ 
3 rows in set (0.00 sec)

Left Outer Join

> SELECT Persons.LastName FROM Persons LEFT OUTER JOIN Personels on Persons.LastName = Personels.LastName;     
+----------+ 
| LastName | 
+----------+ 
| C        | 
| A        | 
| B        | 
| NULL     | 
+----------+ 
4 rows in set (0.00 sec)

 

What is The Difference Between Inner and Outer Join? Infografic

What is The Difference Between Inner and Outer Join? Infografic
What is The Difference Between Inner and Outer Join? Infografic

LEARN MORE  SQL Comments Tutorial with Example

Leave a Comment