MySQL Joins
MySQL Joins
In MySQL, joins are used to combine rows from two or more tables based on a related column between them. Joins are essential for retrieving data that is distributed across multiple tables.
Types of Joins in MySQL
| Join Type | Description |
|---|---|
| INNER JOIN | Returns only rows where matching values exist in both tables |
| LEFT JOIN (or LEFT OUTER JOIN) | Returns all rows from the left table, and matched rows from the right table; unmatched rows from right table are NULL |
| RIGHT JOIN (or RIGHT OUTER JOIN) | Returns all rows from the right table, and matched rows from the left table; unmatched rows from left table are NULL |
| FULL OUTER JOIN | Returns rows when there is a match in either table (Not directly supported in MySQL; use UNION) |
| CROSS JOIN | Returns Cartesian product of two tables (all combinations) |
Example Tables
Students Table
| student_id | name | dept_id |
|---|---|---|
| 1 | John | 1 |
| 2 | Emma | 2 |
| 3 | Raj | 1 |
| 4 | Sara | 3 |
Departments Table
| dept_id | dept_name |
|---|---|
| 1 | IT |
| 2 | HR |
| 3 | Finance |
| 4 | Sales |
INNER JOIN
Returns only matching rows from both tables.
Output:
| name | dept_name |
|---|---|
| John | IT |
| Emma | HR |
| Raj | IT |
| Sara | Finance |
LEFT JOIN
Returns all rows from the left table, and matched rows from the right table.
Output: (Same as INNER JOIN here, because all students have a department, but if a student had dept_id = 5, dept_name would be NULL)
| name | dept_name |
|---|---|
| John | IT |
| Emma | HR |
| Raj | IT |
| Sara | Finance |
RIGHT JOIN
Returns all rows from the right table, and matched rows from the left table.
Output:
| name | dept_name |
|---|---|
| John | IT |
| Raj | IT |
| Emma | HR |
| Sara | Finance |
| NULL | Sales |
Notice: “Sales” department has no student → name = NULL
CROSS JOIN
Returns all possible combinations between two tables.
Output: (4 students × 4 departments = 16 rows)
| name | dept_name |
|---|---|
| John | IT |
| John | HR |
| John | Finance |
| John | Sales |
| Emma | IT |
| Emma | HR |
| … | … |
FULL OUTER JOIN (MySQL Workaround)
MySQL doesn’t support FULL OUTER JOIN directly. Use UNION of LEFT JOIN and RIGHT JOIN:
Summary Table
| Join Type | Returns |
|---|---|
| INNER JOIN | Only matching rows |
| LEFT JOIN | All left table rows + matched right table rows |
| RIGHT JOIN | All right table rows + matched left table rows |
| FULL OUTER | Rows when match in either table |
| CROSS JOIN | All combinations (Cartesian product) |
