MySQL Outer Join (original) (raw)
Last Updated : 23 Jul, 2024
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.
- 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.
- 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:
| employee_id | name | department_id |
|---|---|---|
| **1 | John Doe | 101 |
| **2 | Jane Roe | 102 |
| **3 | Jim Smith | NULL |
departments:
| department_id | **department_name |
|---|---|
| 101 | HR |
| 102 | Finance |
| 103 | IT |
To retrieve all employees along with their department names, using a **LEFT JOIN:
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;
Result:
| name | **department_name |
|---|---|
| John Doe | HR |
| Jane Roe | Finance |
| Jim Smith | NULL |
The query retrieves all **employees, including those without a **corresponding department, and fills in the department name where available, or **NULL if not.
RIGHT JOIN
The **RIGHT JOIN, also referred to as **RIGHT OUTER JOIN, is used to display all entries from the right table along with any corresponding rows from the left table. If there are no matches, the left table will output **NULL.
Syntax:
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
Example
Using the same **employees and **departments tables, to retrieve all departments along with their employees:
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;
Result:
| **name | **department_name |
|---|---|
| John Doe | HR |
| Jane Roe | Finance |
| NULL | IT |
The query retrieves all **departments, including those without a **corresponding employee, and fills in the employee name where available, or **NULL if not.
Practical Applications of Outer Joins
Outer Joins come in handy when you need a complete dataset that includes rows without matches in a different table. There are numerous applications in the real world. Some practical applications include:
- **Reporting and Analysis: Ensuring reports are comprehensive by including all categories, even those with no transactions.
- **Data Cleaning requires identifying and correcting data discrepancies in records for validation purposes.
- **Business Logic: Displaying all items, like products or employees, with either linked or missing information.
Conclusion
**MySQL Outer Joins, such as **LEFT JOIN and **RIGHT JOIN, are powerful tools for combining and retrieving data from multiple tables, ensuring that all crucial data is included in your queries. Learning and effectively using these joins can significantly enhance your ability to manage and analyze relational data, leading to better-informed and more thorough data-driven decisions.
By mastering **Outer Joins, you will be better equipped to handle complex queries and data sets, improving the durability and efficiency of your database interactions. If you are a database administrator, developer, or data analyst, having knowledge of **MySQL Outer Joins will be very beneficial for your daily tasks.