MySQL Joins - Real Examples Tutorial

Write an INNER JOIN Query

Write a query to list all employees with their department names using INNER JOIN.

Expected output: A list of employee names with their corresponding department names, excluding employees without departments.

Hint: Join employees.department_id with departments.id.

Use LEFT JOIN to Include All Employees

Write a query to list all employees and their departments, including employees without a department.

Expected output: A list of all employees with department names or NULL if no department.

Hint: Use LEFT JOIN from employees to departments.

Simulate FULL JOIN

Write a query to list all employees and all departments, matching where possible, simulating FULL JOIN.

Expected output: A combined list showing all employees and departments, including unmatched rows.

Hint: Combine LEFT JOIN and RIGHT JOIN with UNION.

INNER JOIN returns only rows with matching values in both tables, while LEFT JOIN returns all rows from the left table and matched rows from the right table, filling with NULLs when no match exists.

Since MySQL does not support FULL JOIN natively, you can simulate it by combining LEFT JOIN and RIGHT JOIN results using UNION.

Explicit join conditions prevent Cartesian products, which can cause large, unintended result sets and degrade query performance.