? MySQL LEFT JOIN & RIGHT JOIN

? Quick Overview

The LEFT JOIN and RIGHT JOIN are Outer Joins used to combine data from two related tables while preserving all records from one side, even if there are no matches on the other.

? Key Concepts

• LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table.
• RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left table.
• NULL: If no match is found, the result set will contain NULL values for the missing side.

? Syntax / Theory

-- LEFT JOIN: Table1 is the priority
SELECT column1, column2
FROM table1
LEFT JOIN table2
ON table1.id = table2.table1_id;

-- RIGHT JOIN: Table2 is the priority
SELECT column1, column2
FROM table1
RIGHT JOIN table2
ON table1.id = table2.table1_id;

? Code Example: LEFT JOIN

// Fetch all employees even if no department is assigned
SELECT employees.name, departments.dept_name
FROM employees
LEFT JOIN departments
ON employees.dept_id = departments.id;

? Code Example: RIGHT JOIN

// Fetch all departments even if they have no employees
SELECT employees.name, departments.dept_name
FROM employees
RIGHT JOIN departments
ON employees.dept_id = departments.id;

? Live Interactive Logic

Switch between the joins to see how Charlie (No Dept) or Marketing (No Staff) behave.

? Visual Join Representation

? Use Cases

• LEFT JOIN: Finding users who haven't placed any orders yet.
• RIGHT JOIN: Finding categories that don't have any products assigned.
• Data Auditing: Identifying orphan records in a database.