MySQL is a popular and strong system that is extensively employed for handling data in the field of relational databases. A crucial aspect of working with databases is the ability to retrieve and handle data from different tables. SQL joins combine rows from different tables by connecting them based on related columns.

The Outer Join is an important join type as it includes rows with no match in the results, unlike other join types. This article will thoroughly explore MySQL Outer Join, discussing its various types and offering real-life examples.

What is an Outer Join?

An Outer Join combines all records from one table and includes any corresponding records from another table. If there is no matching item, the result will be NULL on the incompatible side. This differs from an Inner Join as it only displays rows with a match in both tables.

Types of Outer Joins in MySQL

MySQL supports two types of Outer Joins that MySQL.

1. LEFT JOIN (or LEFT OUTER JOIN) shows all entries from the left table along with any matching entries from the right table. If no matching data is found, the columns in the right table will be filled with NULL values.
2. RIGHT JOIN also known as RIGHT OUTER JOIN, gets all entries from the table on the right and only the corresponding entries from the left table. If there is no matching entry, the columns from the left table will retrieve NULL values.

LEFT JOIN

The LEFT JOIN, also called LEFT OUTER JOIN, is used to show all rows from the left table along with the matching rows from the right table. If no matches are detected, the right table will return a result of NULL.

Syntax:

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

Example:

Consider two tables, employees and departments:

employees:

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;