Joins in MySql
Joins in MySQL
Joins in MySQL are used to combine data from two or more tables based on a related column.
1. Types of Joins in MySQL
| Join Type | Description |
|---|---|
INNER JOIN | Returns only matching records from both tables. |
LEFT JOIN (or LEFT OUTER JOIN) | Returns all records from the left table and matching records from the right table. |
RIGHT JOIN (or RIGHT OUTER JOIN) | Returns all records from the right table and matching records from the left table. |
FULL JOIN (or FULL OUTER JOIN) | Returns all records from both tables (not supported in MySQL directly). |
CROSS JOIN | Returns all possible combinations of records from both tables. |
2. Sample Tables
employees Table
| id | name | department_id |
|---|---|---|
| 1 | Alice | 1 |
| 2 | Bob | 2 |
| 3 | Charlie | 3 |
| 4 | David | NULL |
departments Table
| id | department_name |
|---|---|
| 1 | IT |
| 2 | HR |
| 3 | Sales |
| 4 | Marketing |
3. INNER JOIN
✅ Returns only matching records from both tables
SELECT employees.name, departments.department_nameFROM employeesINNER JOIN departments ON employees.department_id = departments.id;
✅ Output:
+---------+---------------+| name | department_name |+---------+---------------+| Alice | IT || Bob | HR || Charlie | Sales |+---------+---------------+
🚀 Note: David is excluded because department_id is NULL.
4. LEFT JOIN (LEFT OUTER JOIN)
✅ Returns all records from the left table (employees) and matching records from the right table (departments). If no match is found, NULL is returned.
SELECT employees.name, departments.department_nameFROM employeesLEFT JOIN departments ON employees.department_id = departments.id;
✅ Output:
+---------+---------------+| name | department_name |+---------+---------------+| Alice | IT || Bob | HR || Charlie | Sales || David | NULL |+---------+---------------+
🚀 Note: David has NULL because he has no department_id.
5. RIGHT JOIN (RIGHT OUTER JOIN)
✅ Returns all records from the right table (departments) and matching records from the left table (employees). If no match is found, NULL is returned.
SELECT employees.name, departments.department_nameFROM employeesRIGHT JOIN departments ON employees.department_id = departments.id;
✅ Output:
+---------+---------------+| name | department_name |+---------+---------------+| Alice | IT || Bob | HR || Charlie | Sales || NULL | Marketing |+---------+---------------+
🚀 Note: The "Marketing" department has no employees, so NULL appears.
6. FULL JOIN (FULL OUTER JOIN)
⚠️ MySQL does not support FULL JOIN directly. You can use UNION of LEFT JOIN and RIGHT JOIN:
SELECT employees.name, departments.department_nameFROM employeesLEFT JOIN departments ON employees.department_id = departments.idUNIONSELECT employees.name, departments.department_nameFROM employeesRIGHT JOIN departments ON employees.department_id = departments.id;
✅ Combines both LEFT JOIN and RIGHT JOIN results.
7. CROSS JOIN
✅ Returns the Cartesian product (all possible combinations) of both tables.
SELECT employees.name, departments.department_nameFROM employeesCROSS JOIN departments;
✅ Output: If employees has 4 rows and departments has 4 rows, this returns 4 × 4 = 16 rows.
8. Performance Tips
⚡ Use Indexing – Index columns used in JOIN (department_id, id) to speed up queries.
⚡ Filter Data Early – Use WHERE conditions to limit unnecessary data before joining.
⚡ Use EXPLAIN – Analyze performance with EXPLAIN SELECT ....
9. Key Takeaways
✅ INNER JOIN – Returns only matching rows.
✅ LEFT JOIN – Returns all left table rows, even if no match.
✅ RIGHT JOIN – Returns all right table rows, even if no match.
✅ FULL JOIN – Not supported directly, use UNION.
✅ CROSS JOIN – Returns all possible combinations.
