Home » MySQL

Difference Between Left, Right and Full Outer Join in MySQL

Learn about the Left, Right, and Full Outer Joins and differences between them.
Submitted by Apurva Mathur, on November 08, 2022

In MYSQL joins are used to combine to or more tables together. Sometimes when we perform a large query task then we feel the need of combining the two tables together in such cases JOINS comes into the picture.

Outer Join

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

Suppose I have two tables,

TABLE 1: Students table

TABLE 2: Marks table

Now we will apply different types of joins to these two tables.

1) Left Outer Join

This type of join displays all the columns from the LEFT table with similar columns on the RIGHT table. In simple words, the result of the LEFT outer join will contain all the attributes from the LEFT table and only the matched attributes from the RIGHT table.

Syntax:

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

Example:

SELECT students.*, marks.total_marks
FROM students LEFT OUTER JOIN marks ON 
students.name=marks.name;

The query stated above will show you the following result,

2) Right Outer Join

This type of join displays all the columns from the RIGHT table with similar columns on the LEFT table. In simple words, the result of the RIGHT outer join will contain all the attributes from the RIGHT table and only the matched attributes from the LEFT table. So it is just the opposite of the left outer join.

Syntax:

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

Example:

SELECT name,subjects,total_marks,status 
FROM students RIGHT OUTER JOIN marks ON students.Name=marks.sName;

This query will display the following result;

3) Full Outer Join

This type of join combines both the result and then displays it. It returns all the possible combinations from both tables.

Syntax:

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

---