Home » MySQL

Difference Between Inner Join and Outer Join in MySQL

Learn about the inner join and outer join, differences between them.
Submitted by Apurva Mathur, on November 08, 2022

Inner Joins

Inner Joins are the joins that help us to find the intersection between the two tables, which that means with the help of the inner join we can get the common values among the tables. When we are using inner join on any tables then it is important that they must have at least one common column between them, this join will combine the data according to the common column and as a result, it will return the new table.

Syntax to apply inner join

SELECT * 
FROM table1 INNER JOIN table2
ON table1.column_name = table2.column_name;

Example:

Suppose I have two tables,

TABLE 1: Students table

TABLE 2: Marks table

As you can see in both the tables, I have one common column named as the "name" now, if I want a new table that should contain the "name", "marks", and the "subject" the student opted for, then in such case we will use Inner Join in the following way:

SELECT students.name, students.subjects, marks.total_marks 
FROM students INNER JOIN marks 
ON students.name=marks.name;

After hitting this query our result would be:

Outer Join

In simple words we can say that it is just the opposite of inner join, inner joins provide us the result based on a common column, but outer join does not have such limitation. Outer join is a type of join which gives the result of two tables in the union form, and we know that the union of any two sets contains all the results from both tables.

We have three types of Outer Joins;

1. Left Outer Join
2. Right Outer Join
3. Full Outer Join

Syntax of left outer join

SELECT * 
FROM table1 LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;

Syntax of right outer join

SELECT * 
FROM table1 RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;

Syntax of full outer join

SELECT * 
FROM table1 FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

Overview Differences

Inner Join	Outer Join
It provides the intersection/ common attribute value among the two or more tables.	It provides us the union value among the two or more tables.
It is an important condition of inner join to have at least one common column between the two tables to get the result.	For outer join no such limitation is there, if no common columns are there it will give you the result which will contain all the attributes from both the tables.
Inner join has no further joins	Outer joins are of three types; Left Outer Join Right Outer Join Full Outer Join
If in case, you have a large dataset then it is suggested to apply inner joins on the table as it works faster than outer joins.	As outer joins provide us all the attributes from both the tables, therefore query takes times to execute which makes it slower than inner join.

---